You are currently offline, waiting for your internet to reconnect

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

This article has been archived. It is offered "as is" and will no longer be updated.
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
Additional information: COMPUTE BY statements not supported.
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.
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section of this article.

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.
For more information, visit the following Microsoft Developer Network (MSDN) Web sites:
Summarizing data using COMPUTE and COMPUTE BY

Article ID: 823401 - Last Review: 12/08/2015 03:22:41 - Revision: 1.3

Microsoft .NET Framework 1.1, Microsoft .NET Framework 1.0

  • kbnosurvey kbarchive kberrmsg kbbug kbsqlprog kbdatabase kbprogramming KB823401