>I'm having a hard time getting the no. of records returned by an SQL statement.
>
>The recordcount property just doesn't work and the recordset does not allow moving backwards.
>
>Could somebody please provide an example/source me on how to connect and retrieve the number of records in a recordset. I would appreciate if you will use BIBLIO.MDB or Northwind.MDB as your database.
>
>Thanx.
You need to use a static cursor to use the recordcount property of the ADO recordset:
Dim cSQL, oRs, oConn
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=Pubs;" & _
"Data Source=DMSTEST"
cSQL = "SELECT * FROM Employee"
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open cSQL, oConn, adOpenStatic, adLockReadOnly, adCmdText
Response.Write "The number of records is " & oRs.RecordCount & ".
"
' Returns 41
You'll need to include the ADOVBS.INC file in order to use the ADO constants, otherwise, you can just provide the numeric values.
Sorry this isn't and example with your Access database but I think you'll get the idea.
Hope this helps,
-JT
Jeff Trockman, MCP