A "COMPUTE BY statements not supported" exception occurs when you use the COMPUTE BY clause in SQL Server

Steps to Reproduce the Problem

1. Create a stored procedure "myProc" in the Pubs database by running the following query in SQL Server Query Analyzer:

   CREATE PROCEDURE MyProc AS
   SELECT Type, Price
   FROM Titles
   ORDER BY Type
   COMPUTE SUM(Price) BY Type
   GO
   					

2. Start Visual Studio .NET.
3. Create a new Windows application in Visual Basic .NET.
4. Make sure that your project contains a reference to the System.Data namespace.
5. Place a command button on Form1. Change the Name property of the button to "btnTest" and the Text property to "Test."
6. Use the Imports statement on the System and System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add this to the General Declarations section of Form1, as follows:

   Imports System
   Imports System.Data.OleDb
   Imports System.Data.SqlClient
   					

7. Paste the following code in the code window after the region "Windows Form Designer generated code":
   
   Note You must replace User ID <user name> with an account that has the appropriate permissions to perform these operations on the database.

   Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click
           Dim myConnString As String = _
                   "User ID=<username>;password=<strong password>;Initial Catalog=pubs;Data Source=myServer"
       Dim myConnection As New SqlConnection(myConnString)
       Dim myCommand As New SqlCommand()
       Dim myReader As SqlDataReader
   
       myCommand.CommandType = CommandType.StoredProcedure
       myCommand.Connection = myConnection
       myCommand.CommandText = "MyProc"
   
       Dim RecordCount As Integer
       myConnection.Open()
       myReader = myCommand.ExecuteReader
       While myReader.Read()
           'Logic to process data
           RecordCount = RecordCount + 1
       End While
   End Sub
   					

8. Modify the Connection string (myConnString) as appropriate for your environment.
9. Save your project. On the Debug menu, click Start to run your project.
10. Click Test.
    
    Notice that an exception is generated when you attempt to run the command.

Back to the top