ADO.RecordCount equals - 1 problem
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
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.