Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-10-30
2
Medium Priority
?
229 Views
Last Modified: 2014-10-30
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
Comment
Question by:Murray Brown
2 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40413243
>"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
 

Author Closing Comment

by:Murray Brown
ID: 40413264
Great answer! Thanks Jim
Murray
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question