How to set a count(*) SQL statement = to a VB variable?

[Kooda]

Basically the subject line said it. I need to actually find out the number that the count query returns.

thanks

 

[AdaHacker]

Something like the following would do it. Assume that conn is an ADODB.Connection that you've already set up and opened.

Dim RS As ADODB.Recordset
Dim myvar As Long
Set RS = New ADODB.Recordset
RS.Open "SELECT count(*) AS myval FROM mytable", DB
myvar = RS.Fields("myval")

Basically, aggregate operations like count and sum just return a single column in the recordset. I usually alias them to make referencing easier.

 

[Kooda]

all I'm trying to do is find out if a customer ID is in the field somewhere. If it isn't, I will tell the user.
If the count is 0, then the ID does not exist.

Can't I do this without a recordset? I'm having problems with some ADO functions and our Sybase SQL server.

thanks

 

[Kooda]

Thanks a ton adaHacker..you rule.
A question on the code: myvar = RS.Fields("myval&quot

it seems that this was the important part the counted the results. What does the "Fields" part do?

thanks again