Select Count causes system error

[indydavid32]

This is my code.

Dim cn2 As New SqlClient.SqlConnection()
cn2.ConnectionString = "Integrated Security=True;" & _
        "Data Source=Brutus;Initial Catalog=Problem Tracking;" & _
        "user id=user;password=pass;"
cn2.Open()
Dim commSQL2 As New SqlClient.SqlCommand()
commSQL2.Connection = cn2
Dim datRead2 As SqlClient.SqlDataReader
commSQL2.CommandText = "Select Count(Store Number) As Exp1 From Stores Order By [Store Number]"
datRead2 = commSQL2.ExecuteReader

This causes the following error message:
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

I tried it with a regular Select command like this.

commSQL2.CommandText = "Select [Store Number] From Stores Order By [Store Number]"

This worked fine.

Anyone have any ideas why the select count statement is not working?

Thanks

[indydavid32]

Looks like the Order By clause was causing the error. Apparently, you cannot have an order by clause in a select statement with a Select count in the line.

Even this doesn't work.

commSQL2.CommandText = "Select count(*) as Row Count, [Store Number] From Stores Order By [Store Number]"

This doesn't work either...

commSQL2.CommandText = "Select count(*) as Row Count, [Store Number] From Stores"

Anyone have one that does work where you bring back the record count and all the records as well?

[Mike Hildner]

Just a suggestion - you may want to catch SqlExceptions - if you do, you get better messages.

About your SQL - do you really want a result set that contains the count in every row? Or do you just want the count? You might be better off determining the number of rows after you've fetched them, or issue another statement that just gets that one value.

[Cander]

Of course order by wouldnt work. There is no reason for it to work at all.

If you want a listing of records and the count. Just fill a dataset, then use

datset.Tables("tablename").Rows.Count

[techgnome]

If the field is going to contain spaces, the name MUST be enclosed in brackets....

commSQL2.CommandText = "Select count(*) as [Row Count], [Store Number] From Stores Order By [Store Number]"

AND you have to tell it what to group on....

commSQL2.CommandText = "Select count(*) as [Row Count], [Store Number] From Stores GROUP BY [Store Number]"

TG