Knowledge Base

BUG: A System.InvalidOperationException exception may occur when you run a SQL Server query by using Visual Studio .NET

Article ID: 823401

Article Last Modified on 5/21/2007

APPLIES TO

Microsoft .NET Framework 1.1
Microsoft .NET Framework 1.0

SYMPTOMS

When you run a Microsoft SQL Server query by using Microsoft Visual Studio .NET, you may receive the following error message:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: COMPUTE BY statements not supported.

CAUSE

The behavior occurs if your SQL Server query contains a COMPUTE BY clause. The behavior occurs because the Microsoft .NET Framework does not support queries that contain the COMPUTE BY clause.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section of this article.

MORE INFORMATION

Steps to reproduce the behavior

Start Visual Studio .NET.
On the File menu, point to New, and then click Project.
Under Project Types, click Visual C# Projects, and then click Class Library under Templates.
Name the project ClassLibrary1. By default, the Class1.cs file is created.

Replace the existing code in the Class1.cs file with the following code.

Note In the following code, replace the connection string with a connection string that is appropriate for your environment.

using System;
using System.Data;
using System.Data.SqlClient;

namespace ManagedProviderRepro
{
    public class ComputeBy
    {
        public static void Repro()
        {
            string connectionString = "server=ServerName;uid=UserName;pwd=Password";

            SqlConnection c = new SqlConnection(connectionString);
            c.Open();

            SqlCommand cmd = new SqlCommand("use NorthWind", c);
            IDataReader dr = cmd.ExecuteReader();
            ProcessReader(dr);
            dr.Close();
            cmd.Dispose();

            cmd = new SqlCommand("SELECT ProductID, Quantity, SUM(UnitPrice * Quantity) AS Total " +
                "FROM dbo.[Order Details] " +
                "GROUP BY ProductID, Quantity " +
                "ORDER BY ProductID, Quantity " +
                "COMPUTE SUM(SUM(UnitPrice * Quantity)) BY ProductID, Quantity " +
                "COMPUTE SUM(SUM(UnitPrice * Quantity)) BY ProductID", c);

            dr = cmd.ExecuteReader();
            
            try
            {
                ProcessReader(dr);
            } 
            finally
            {
                dr.Close();
                cmd.Dispose();
                System.Windows.Forms.MessageBox.Show(c.State.ToString());
            }
        }

        private static void ProcessReader(IDataReader dr)
        {
            bool bNextResult = false;
            do
            {
                if (dr.FieldCount <= 0)
                {
                    bNextResult = dr.NextResult();
                    continue;
                }

                Object oVal;
                int fieldCount = dr.FieldCount;
            
                while (dr.Read())
                {
                    for (int i = 0; i < fieldCount; i++)
                    {
                        oVal = dr.GetValue(i);
                        Console.WriteLine(oVal.ToString());
                    }
                }
                
                bNextResult = dr.NextResult();
            } while (bNextResult);
        }
    }
}

Add a reference to the System.Windows.Forms.dll assembly, and then build the project.
In Visual Studio .NET, point to New on the File menu, and then click Project.
Under Project Types, click Visual C# .Projects, and then click Windows Application under Templates.
Name the project WindowsApplication1. By default, the Form1.cs file is created.

Replace the existing code in the Form1.cs file with the following code:

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using ManagedProviderRepro;

namespace WindowsApplication1
{
    /// <summary>
    /// Summary description for Form1.
    /// </summary>
    public class Form1 : System.Windows.Forms.Form
    {
        private System.Windows.Forms.Button button1;
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.Container components = null;

        public Form1()
        {
            //
            // Required for Windows Form Designer support
            //
            InitializeComponent();

            //
            // TODO: Add any constructor code after InitializeComponent call
            //
        }

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        protected override void Dispose( bool disposing )
        {
            if( disposing )
            {
                if (components != null) 
                {
                    components.Dispose();
                }
            }
            base.Dispose( disposing );
        }

        #region Windows Form Designer generated code
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.button1 = new System.Windows.Forms.Button();
            this.SuspendLayout();
            // 
            // button1
            // 
            this.button1.Location = new System.Drawing.Point(120, 104);
            this.button1.Name = "button1";
            this.button1.TabIndex = 0;
            this.button1.Text = "button1";
            this.button1.Click += new System.EventHandler(this.button1_Click);
            // 
            // Form1
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(292, 266);
            this.Controls.Add(this.button1);
            this.Name = "Form1";
            this.Text = "Form1";
            this.ResumeLayout(false);

        }
        #endregion

        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main() 
        {
            Application.Run(new Form1());
        }

        private void button1_Click(object sender, System.EventArgs e)
        {
            
            ManagedProviderRepro.ComputeBy.Repro(); 
        }
    }
}

Add a reference to the ClassLibrary1.dll assembly that you built in step 4.
Build and then run the WindowsApplication1 project. You receive the error message that is mentioned in the "Symptoms" section.

REFERENCES

For more information, visit the following Microsoft Developer Network (MSDN) Web sites:

InvalidOperationException class
http://msdn2.microsoft.com/en-us/library/system.invalidoperationexception(vs.71).aspx

Summarizing data using COMPUTE and COMPUTE BY
http://msdn2.microsoft.com/en-us/library/aa213217(SQL.80).aspx

Keywords: kbdatabase kbsqlprog kberrmsg kbbug kbprogramming KB823401

Microsoft KB Archive/823401

Contents