Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

VB.net Error trying to count the records in a query before running it

Hi
I use the VB.net code function below to count the number of records on a query before I run it. It has worked fin up until the following SQL string that I passed into it. I get the error "No column name was specified for column 1 of 'X' "

      oSQL      "SELECT Sum([Order Details].[Quantity]), [Order Details].[Product ID] FROM [Order Details] GROUP BY [Order Details].[Product ID] HAVING Sum([Quantity]) < 100000"      String


    Function oCount_Rows_in_Query(ByVal oSQL As String) As String

        Try
            Dim DBType As String = Globals.ThisAddIn.oRIGHT.lblDataBaseType.Text
            Dim oConnectionString As String = Globals.ThisAddIn.oRIGHT.lblConnectionString.Text


            'If there are sort criteria then chop this off the end as doesn't work
            'in a count, nor is it relevant
            'If InStr(oSQL, "ORDER BY") > 0 Then
            'Dim arrSplit As Object = Split(oSQL, "ORDER BY")
            'oSQL = arrSplit(0) 'Use SQL before the ORDER BY
            'End If


            Dim sSQL As String


                sSQL = "SELECT Count(*) As MyCount FROM (" & oSQL & ") As X" 'Have to alias the sub query

                Dim cn As New SqlConnection(oConnectionString)
                Dim cmd As New SqlCommand(sSQL, cn)
                cn.Open()

                oCount_Rows_in_Query = cmd.ExecuteScalar.ToString

                If cn.State <> ConnectionState.Closed Then
                    cn.Close()
                End If

        Catch ex As Exception
            oCount_Rows_in_Query = "Error"
            MsgBox(Err.Description & " 17azn")
        End Try
    End Function
0
Murray Brown
Asked:
Murray Brown
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>"No column name was specified for column 1 of 'X' "
The knee-jerk SQL answer to the above error message would be to include an alias (QuantitySum below) in the first column
oSQL      "SELECT Sum([Order Details].[Quantity]) as QuantitySum, [Order Details].[Product ID] FROM [Order Details] GROUP BY [Order Details].[Product ID] HAVING Sum([Quantity]) < 100000"      String

Open in new window

Can't help you on the VB.NET part..
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Great answer! Thanks Jim
Murray
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now