| Answer: |
Many times developers new to ADO or ASP poke through the documentation and quickly come upon the .RecordCount property of the ADO Recordset object. "Great!" they may think, "I can use this to get the total number of records in a query." So they hammer out a quick ASP page, something like:
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM Table1", "DSN=MyDSN"
Response.Write "There are " & objRS.RecordCount & " records in Table1"
|
When they run, this however, the output they get is:
There are -1 records in Table1
Egad! How can a database table have -1 records? It can't; it doesn't. The .RecordCount property requires a non-forward-only cursor to return sensible results. This is because all of the records in the Recordset have to be stepped through and, then, the cursor has to be returned to the beginning of the Recordset. This requires a dynamic cursor. When opening a Recordset a forward-only cursor is used by default; a forward-only cursor, as its name implies, does not allow the cursor to step back through the records of a Recordset. (To learn more about Recordset cursors be sure to read: Recordset Cursors: Choose the Right Cursor for the Right Job.)
So how do we get the .RecordCount property to work? In this ASPMessageboard post Bill Wilkinson shares:
<%
... I assume you have already opened your connection object...
Set RS = Server.CreateObject("ADODB.RecordSet")
RS.Open tableNameOrSqlQuery, yourConnection, 3, 3
count = RS.RecordCount
Response.Write "count of records in tableName is " & count
%>
|
Why does that work? Because of the way we opened the RecordSet. If you don't specify the open modes, you end up getting a forward-only, read-only "cursor." And such cursors can NOT give you a valid RecordCount, hence you always get -1. Using 3,3 (or using the constants adOpenStatic, adLockOptimistic if you have included a valid adovbs.inc file) gives you a cursor that *can* count records for you. (For more information on using adovbs.inc to avoid "magic numbers" (the 3, 3, for example), see: FAQ: What is adovbs.inc? Why should I use it? What benefits does it have?)
Oh...and one more comment: If you learn to use the RecordSet.GetRows method, to convert your recordset into an array, then you gain efficiency and you don't need to use RecordCount (because the size of the array tells you how many records there are).
Oh, what the heck:
<%
... I assume you have already opened your connection object...
Set RS = Server.CreateObject("ADODB.RecordSet")
RS.Open "tableName", yourConnection ' I use defaults this time!
allRows = RS.GetRows ' convert RS to array!
numberOfRecords = UBound(allRows,2) + 1
numberOfFields = UBound(allRows,1) + 1
...
%>
|
(For more information on GetRows be sure to check out: Use GetRows to Speed Up Displaying "Skinny" Tables)
Happy Programming! |
