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

thanks,

回答1:

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

回答2:

rsscroll.RecordCount

回答3:

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 
%>

回答4:

You could just change your SQL to count the records:

strSQLscroll = "SELECT count(*) as Total FROM tblItems where expiration_date > getdate();"

Then you just need to response.write rsscroll("Total")

回答5:

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.

回答6:

Get in the habbit of storing returned data in arrays. This is amazingly faster to iterate than using an open record set. Also, specify the fields to select when doing this as you have to explicitly reference the array index.

<%
Set rsscroll = Server.CreateObject("ADODB.Recordset")
Dim strSQLscroll, rsscroll
Dim arrCommon

'Open recordset, copy data to array
strSQLscroll = "SELECT field1, field2, field3 FROM tblItems where expiration_date > getdate() order by expiration_date desc;"
rsscroll.open strSQLscroll,oConn
    arrCommon = rsscroll.getRows()
rsscroll.close

'Get the total records in this array
response.write ubound(arrCommon, 2);

'Loop...
for i = 0 to ubound(arrCommon, 2)

    ' This prints field 3
    response.write arrCommon(2, i)

next
%>

回答7:

<% ' TableID = your tables ID...

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

%>

回答8:

You can try this

    Dim count
    count = 0
    if strSQLscroll.eof <> true or strSQLscroll.bof <> true then
       while not strSQLscroll.eof
          count = count+1
          strSQLscroll.movenext
       wend
    end if
    response.write(count)

回答9:

If you are using MySQL try this:

Dim strSQLscroll, rsscroll, countrs

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

countrs = rsscroll.recordcount