Generate accurate ADO RecordCount values in ASP

[neta]

As you know, the ADO RecordCount property returns the number of records in an ADO recordset. Of course, in several instances, this property also returns a -1 instead. The value RecordCount returns depends on the recordset's cursor type: -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

You may be surprised to learn that RecordCount will be -1 for recordsets created with the Execute method from a Connection or Command object. That's because this method generates a forward-only recordset, which, as we mentioned earlier, returns -1.

As an example, enter and run the following procedure in a standard ASP page. When you open it, the page displays -1 for the recordset based on myConRst, and 6246 for myKeyRst.

<%
Dim myConn, myComm, myConRst, myKeyRst
Dim sConnection
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Microsoft Visual Studio\VB98\Biblio.mdb" 
Set myConn = Server.CreateObject("ADODB.Connection")
Set myKeyRst = Server.CreateObject("ADODB.Recordset") 
myConn.Open sConnection
myComm = "Select * From Authors"
Set myConRst = myConn.Execute(myComm, , 1)
myKeyRst.Open myComm, myConn, 1
%>
RecCount <BR/>
>From Connection: <%=myConRst.RecordCount%><BR/>
>From Recordset: <%=myKeyRst.RecordCount%>
<%
Set myKeyRst = Nothing
Set myConRst = Nothing
Set myConn = Nothing
%>