How to get number of records in the database using recordset

Question

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?

Munir Shaikh · Answer

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 Montano · Answer

AFAIK the recordCount returns the number of rows affected and is not applicable to a select statement but in insertupdate 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 field1field2 from myTable you could modify it instead to select count(field1)field1field2 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!