Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-10-30
2
Medium Priority
?
226 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

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.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

721 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