[thelist] Number of records returned (Solution)

on 8/15/02 8:14 AM, Chris Marsh at chris@webbtech.co.uk wrote:

> [..]
>
>> Set objConn = Server.CreateObject("ADODB.Connection")
>> objConn.Open Application("strConn")
>> strSQL = "SELECT *** FROM *** WHERE *** = 1"
>> Set objRS = objConn.Execute(strSQL)
>
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open Application("strConn")
> strSQL = "SELECT *** FROM *** WHERE *** = 1"
> Set objRS = Server.CreateObject("ADODB.RecordSet")
> objRS.ActiveConnection = objConn
> objRS.CursorType = 3
> objRS.Open strSQL
>

<tip type="asp, getting recordcount">
It's easy to get the count of records in a recordset returned by a query.
But don't set the CursorType to adOpenStatic (3) to get the correct
.RecordCount. That's very expensive! Instead use the good old
adOpenForwardOnly cursor (the default) and use GetRows to load your
recordset into an array, as in:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open Application("strConn")
strSQL = "SELECT field FROM table WHERE field = condition"
Set objRS = objConn.Execute(strSQL)
arrRS = objRS.GetRows
Set objRS = Nothing 'you can now clean up because you've used GetRows
objConn.Close
Set objConn = Nothing

Now, the count of total records is the upper bound of arrRS:
lngCount = Ubound(arrRS, 2)
</tip>