[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2013-01-19
3
Medium Priority
?
467 Views
Last Modified: 2013-01-24
Hi

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

(1)
                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)
                cn.Open()

(2)
      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)
                cn.Open()

                oCount_Rows_in_Query = cmd.ExecuteScalar.ToString
0
Comment
Question by:Murray Brown
3 Comments
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 1000 total points
ID: 38796850
Try with the following:

"SELECT Count(*) FROM [Accounting]"
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 38796918
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

0
 

Author Closing Comment

by:Murray Brown
ID: 38815846
Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 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