Using Count(*) on an existing recordset in VB

[TomSnider]

I have created a recordset, rsItems, from an SQL statement in ADO. Is there a way to use that recordset as the table in another SQL statement....? Like "Select Count(*) from rsItems;".....?

Any direction will be appreciated.

Tom Snider

 

[cmmrfrds]

No, I don't believe you can do an aggregate function on the recordset object. If the recordset is using a client side static cursor the recordcount will be filled in, otherwise, on a read only recordset it will not.

If this is an Access database, it will probably be necessary to do a second sql call to get aggregations, such as the count. There are some manipulations possible on the recordset, such as, filtering and sorting, but aggregation is not one of them.

 

[TomSnider]

Thanks for the response. I did not think it was possible, but I was just hoping for some magic..... I am connected to a MySQL database and using a static cursor, but the .recordcount property gives me a -1 always....I have just started using MySQL, so it very well could be some foul up on my part. Anyway, thanks again for taking the time.
Tom Snider

 

[cmmrfrds]

I am not sure how the MySQL provider works, but for SQL Server if you use a client-side static cursor the record count will be filled in. Make sure you don't have a forward-only cursor or a read-only recordset as these will not return a record count.

Does MySQL have stored procedures. ADO will allow you to read multiple recordsets returned from a stored procedure. There is a readnextrecordset method. One could create the detailed recordset and follow it with an aggregation recordset in the stored procedure.

 

[TomSnider]

I don't know if store procedures are allowed in MySQL, but that is a great idea to check out. Much thanks again.

Tom Snider