ADO.RecordCount equals - 1 problem

Question

When ever I try to access the RecordCount property, I always get a return value of -1. Below is my sample code.

Set oConn = Server.CreateObject ("ADODB.Connection")
oConn.Open Application("strConnectstring")
Set rs = Server.CreateObject ("ADODB.Recordset")
rs.ActiveConnection = oConn
SQL = "Publications_PicoSearchListing"
set rs = oConn.execute(SQL)

I'm not sure if I'm doing forwardCursor or dynamic cursors, or if the provider even supports the RecordCount property. How do I check if the provider supports RecordCount property or if I'm using either forwardCursor or dynamic cursors.

Any help would be appreciated.

Thank You

Answer 1

Please note: unless you move to the end of the recordset there is no guarantee that the RecordCount will have been populated. The standard pattern to to iterate over each row in the recordset using While Not rs.EOF. In all the VBA code I've ever written, I have never relied on checking rs.RecordCount

Rather than checking the cursor type, you can set it. For example:

Set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("northwind.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT * FROM Customers"

rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockBatchOptimistic

rs.Open sql, conn

If all you want is the count, why not emit a "SELECT Count(*) From Publications_PicoSearchListing"

Of Interest?: Understanding ADO's Default Cursor Type

Another alternative to get the RecordCount is to execute:

rs.MoveLast
rs.MoveFirst

and then check the RecordCount, and even then I seem to remember some cursor types aren't guaranteed (but memory hazy on this).

Also note: Don't use the MoveLast/MoveFirst unless you really need to: this will be slow with a large recordset or a recordset drawn across a network. Instead use the Count(*) technique.

Answer 2

Recordcount is not supported with the default forward-only cursor. you must add extra parameters to the open command

rs.open sql,conn,1,1

That should let you have access to rs.recordcount.

But paging is best done by using the Recordset.GetRows() + Recordset.Move() method.

http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html (scroll down to the bold "Recordset.GetRows() + Recordset.Move()" this is fastest way without using stored procedures)

Answer 3

For paging you can use the recordset.PageSize and recordset.AbsolutePage like this

Set rs = Server.CreateObject("ADODB.Recordset")
' make recordset use adUSEclient ( client side cursor)'
rs.CursorLocation = 3
' make recordset use the adOpenStatic cursor ( scrollable )'
rs.CursorType = 3
rs.PageSize = RecordsPerPage

rs.Open sql, conn
' go to selected page'
if not rs.EOF and not rs.BOF then
    rs.AbsolutePage = page_you_want_to_go
end if

you then have access to recordset.PageCount to know the number of pages returned..