Solved

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

Posted on 2014-10-30
2
203 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:murbro
2 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 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:murbro
ID: 40413264
Great answer! Thanks Jim
Murray
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 the fundamental information of how to create a table.

861 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now