Datacheck Solutions :: ASP

Tutorials
Back

Returning a Recordcount of -1

If you have done a recordcount on the rows in your database I bet you are wondering how there could be -1 records in your database. The simple answer is that there can't be - 1. The problem is that you have used the wrong cursor. As all the records of the database have to be stepped through and then the cursor has to be returned to the beginning of the recordset, the default which is the forward only cursor won't work. The simple solution is to use a more dynamic cursor rather than a forward only cursor.
Note the code below and the numbers 3,3. These numbers represent the constants adOpenStatic and adLockOptimistic which are the perfect cursor and locktype for a recordcount.

<%@ Language="VBScript" %>
<% Option Explicit %> 
<html>
<head>
<title>Returning a Recordset Recordcount</title>
</head>
<body>
<% 
'declare your variables
Dim recordset, connection
Dim count, sSQL, sConnString

'declare SQL statement that will query your database
sSQL="SELECT * FROM your_table_name"

'create ADO connection and recordset object
Set Connection=Server.CreateObject("ADODB.Connection")
Set Recordset=Server.CreateObject("ADODB.Recordset")

'define the connection string, specify database 
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=" & Server.MapPath("yourdatabasename.mdb")

'Open the connection to the database
Connection.Open(sConnString)

'Open the recordset object executing the SQL 
Recordset.Open sSQL,connection,3,3
'set our variable count equal to the number of records
count=Recordset.recordcount

response.write "the number of records is " & count

'close the connection and recordset objects
Recordset.Close
Set Recordset=Nothing
Connection.Close
Set Connection=Nothing
%>
</body>
</html>