• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

VB.net Counting Rows in query doesn't work with SQL Client but does with Oledb


I am trying to count the number of records in any query using the following code.
I get the error "Syntax error near '('"
The second example where I use Oledb works

                sSQL = "SELECT Count(*) As MyCount FROM (SELECT [Accounting].[Account Type], [Accounting].[Co_ID] FROM [Accounting])"
                Dim cn As New SqlConnection(oConnectionString)
                Dim cmd As New SqlCommand(sSQL, cn)

      sSQL = "SELECT Count(*) As MyCount FROM (SELECT [Accounting].[Account Type], [Accounting].[Co_ID] FROM [Accounting])"

                Dim cn As New OleDbConnection(oConnectionString)
                Dim cmd As New OleDbCommand(sSQL, cn)

                oCount_Rows_in_Query = cmd.ExecuteScalar.ToString
Murray Brown
Murray Brown
2 Solutions
Jacques Bourgeois (James Burger)PresidentCommented:
Try with the following:

"SELECT Count(*) FROM [Accounting]"
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you may need to add an alias to the subquery:
  sSQL = "SELECT Count(*) As MyCount FROM (SELECT [Accounting].[Account Type], [Accounting].[Co_ID] FROM [Accounting]) sub_query " 

Open in new window

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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