How to get number of records in the database using recordset

Kripa

This is a real simple problem but for some reason I haven't been able to figure it out. I am executing a query and I need to get the number of records returned from the query and need to use that number to loop through some operations. I have tried rs.RecordCount where rs is a recordset but receive a -1. What is the function to get the number of rows returned?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Munir Shaikh19y ago
I think you are person of Asp classical, since you have used rs.recordCount, but if you want to do this in ASP.net isuggest that you should use "SqlComd.ExecuteReader" ie. SqlDataReader rdr = SqlCommand.ExecuteReader(); and iterate through end of record ie. While(rdr.Read()) {} I think this will solve your problem >>Munnamax
Jan Montano19y ago
AFAIK, the recordCount returns the number of rows affected and is not applicable to a select statement, but in insert,update and delete only. And if you're working with a datareader, you have to traverse first the records before knowing the record count. In which case, you can have a call first for the row count using the count().
SELECT count(*) from myTable. then execute your real query.
or in your query for example, SELECT field1,field2 from myTable, you could modify it instead to select count(field1),field1,field2 from myTable so that it will be only one call. Implementation is up to you.

I normally would query it twice. First for the count. then for the actual data. Cheers!