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

Article translations Article translations
Article ID: 823401 - View products that this article applies to.
Expand all | Collapse all

On This Page

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

  1. Start Visual Studio .NET.
  2. On the File menu, point to New, and then click Project.
  3. Under Project Types, click Visual C# Projects, and then click Class Library under Templates.
  4. Name the project ClassLibrary1. By default, the Class1.cs file is created.
  5. 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);
    		}
    	}
    }
  6. Add a reference to the System.Windows.Forms.dll assembly, and then build the project.
  7. In Visual Studio .NET, point to New on the File menu, and then click Project.
  8. Under Project Types, click Visual C# .Projects, and then click Windows Application under Templates.
  9. Name the project WindowsApplication1. By default, the Form1.cs file is created.
  10. 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(); 
    		}
    	}
    }
  11. Add a reference to the ClassLibrary1.dll assembly that you built in step 4.
  12. 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

Properties

Article ID: 823401 - Last Review: May 21, 2007 - Revision: 1.3
APPLIES TO
  • Microsoft .NET Framework 1.1
  • Microsoft .NET Framework 1.0
Keywords: 
kberrmsg kbbug kbsqlprog kbdatabase kbprogramming KB823401

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com