How to count records in ASP classic


I'm not quite familiar with programming ASP classic. I just need a small code to run on my webpage. How do i count the record of the returned query?

Set rsscroll = Server.CreateObject("ADODB.Recordset")
Dim strSQLscroll, rsscroll
strSQLscroll = "SELECT * FROM tblItems where expiration_date > getdate() order by expiration_date desc;" strSQLscroll,oConn


Best Solution

It is possible (but not recommended) to use the RecordCount property on the Recordset object as follows:

iTotalRecords = rsscroll.RecordCount

If your table is really large, this can take a long time to run. I would instead run a separate SQL query to get the total records

SQL = "SELECT COUNT(*) AS TotalRecords FROM tblItems WHERE expiration_date > getdate() "
set rsRecordCount = conn.Execute(SQL)
if not rsRecordCount.Eof then
  iTotalRecords = rsRecordCount.Fields("TotalRecords")
  iTotalRecords = 0
end if
set rsRecordCount = nothing
Related Question