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,

score 8 · Answer 1 · answered Jun 29 '09 at 08:38

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

Great approach, Works for me :) – yaqoob Mar 31 '15 at 03:57 — yaqoob, Mar 31 '15 at 03:57

score 6 · Answer 2 · answered Jun 29 '09 at 08:34

6

rsscroll.RecordCount

answered Jun 29 '09 at 08:34

Chris Klepeis

9,783
16
83
149

4

That might work, but it may also return -1. For example the default SQL server firehose rowset will not yield a row count until all the records have been consumed by the caller. – AnthonyWJones Jun 29 '09 at 08:53
Keep in mind you need to have nocount set to on. see http://stackoverflow.com/a/16617637/356544 – Slider345 Dec 17 '13 at 23:55

Matthew Glidden · Answer 3 · 2009-07-02T15:39:54.933

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

score 1 · Answer 4 · answered Jul 10 '09 at 09:04

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.

score 1 · Answer 5 · answered Feb 21 '11 at 13:13

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

score 0 · Answer 6 · answered Jun 29 '09 at 08:35

0

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")

answered Jun 29 '09 at 08:35

Dan

865
2
11
19

score 0 · Answer 7 · answered Jan 25 '14 at 06:34

0

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)

answered Jan 25 '14 at 06:34

Mandeep Singh

983
8
9

score 0 · Answer 8 · edited May 06 '16 at 03:13

0

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

edited May 06 '16 at 03:13

Benjamin W.

46,058
19
106
116

answered May 06 '16 at 02:54

marcollahc

46
1
6

1

Welcome to Stack Overflow! While this code may answer the question, providing additional context regarding *why* and/or *how* this code answers the question improves its long-term value. – Benjamin W. May 06 '16 at 03:13

score 0 · Answer 9 · answered Feb 21 '11 at 13:05

<% ' 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")

%>

how to count records in ASP classic?

9 Answers9

Linked