One of the handy features of working with the ActiveX Data Object (ADO) is the ability to load the results of SQL select statements into a Recordset. Once you have loaded the Recordset, you can display, search, and manipulate the data pulled from a data store. Unfortunately, finding out how many records are in a Recordset is not as easy as it should be.
The Recordset object, created by the Open or Execute functions of the ADODB object, has a RecordCount property, but this property reports ‘-1’ if the Recordset has been opened with a forward-only cursor. Since this is the default and the only way that you can open a Recordset if you have created it using an Execute command, you will need to have another way to count the records.
One simple way is to create a counter that you increment as you step through your Recordset, as shown in the following code segment:
<%RecordCount = 0Set objRecordset=MyConn.Execute(Query)WHILE NOT objRecordset.EOFRecordCount = RecordCount + 1Response.Write(objRecordset("title")&"
")objRecordset.MoveNextWEND%><%= RecordCount %> records in the database.
UPDATE 6/16/00: Francisco Mejia, from Moorestown, New Jersey, contributed this alternative way to count recordsets:
You can also count recordsets using ‘recordset.GetRows’
dim objRecordset as ado.recordsetdim RecordCount = 0 set objRecordset=MyConn.Execute(Query)RecordCount = ubound(rs.getrows)- 1
