how to count records in ASP classic?

Question

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;"
rsscroll.open strSQLscroll,oConn
%>

thanks,

Answer 1

One simple solution's to use the SQL COUNT method. This assumes you want the number of rows and not the data itself.

<%
    Set rsscroll = Server.CreateObject("ADODB.Recordset")
    Dim strSQLscroll, rsscroll, intRow
    strSQLscroll = "SELECT COUNT(*) AS Total FROM tblItems WHERE expiration_date > getdate();"
    rsscroll.open strSQLscroll, oConn
    response.write rsscroll("Total")
    rsscroll.close: set rsscroll = nothing 
    oConn.close: set oConn = nothing 
%>

This returns one row with a single value called "Total." (Read on if you need both the row count and data.)

Your query code uses a default RecordSet, which returns data in "forward-only" mode for efficiency. It'll step through row-by-row, but doesn't know the actual count. (This mode also sets the RecordSet.RecordCount to -1, so the field isn't useful for you.)

RecordSet.Open's "Cursor Type" parameter lets you change to "Keyset" mode (parameter value 1), which does set the RecordCount field to the number of data rows. ("Lock Type" and "Command Type" parameters included for completeness, but they don't figure into this answer.)

RecordsetObject.Open "TableName|SQLStatement", ConnectionObject [,Cursor Type] [,Lock Type] [,Command Type] 

Add this parameter to your code's RecordSet.Open call and then check RecordCount.

<%
    Set rsscroll = Server.CreateObject("ADODB.Recordset")
    Dim strSQLscroll, rsscroll, intRow
    strSQLscroll = "SELECT * FROM tblItems where expiration_date > getdate() order by expiration_date desc;"
    rsscroll.open strSQLscroll, oConn, 1
    intRow = rsscroll.RecordCount
    ' ... do something with intRow
    rsscroll.close: set rsscroll = nothing 
    oConn.close: set oConn = nothing 
%>

If database performance means anything to your situation, the RecordSet.GetRows() method's much more efficient.

<% 
    Dim rsscroll, intRow, rsArray
    Set oConn = CreateObject("ADODB.Connection") 
    oConn.open "<connection string>" 
    strSQLscroll = "SELECT * FROM tblItems where expiration_date > getdate() order by expiration_date desc" 
    Set rsscroll = conn.execute(strSQLscroll) 
    if not rsscroll.eof then 
        rsArray = rsscroll.GetRows() 
        intRow = UBound(rsArray, 2) + 1 
        response.write "rows returned: " & intRow
        ' ... do any other operations here ... 
    end if 
    rsscroll.close: set rsscroll = nothing 
    oConn.close: set oConn = nothing 
%>

Answer 2

I usually use a separate query like "select count(*) from table " to get counts because I usually need not only the count but a summation of the number of units or the average price or whatever and it's easier to write a separate query than to make more variables and say "TotalUnits = TotalUnits + rs("Units").value" inside the loop to display the results. It also comes in handy for the times you need to show the totals above the results and you don't want to loop though the recordset twice.

Answer 3

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")
else
  iTotalRecords = 0
end if
rsRecordCount.Close
set rsRecordCount = nothing