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
RecordCount Property returns -1 because by default the Cursor is adOpenForwardOnly.
An ADODB Recordset in VBA is a storage item: you can store all kinds of different things in it: numbers, texts, dates. An ADODB Recordset is a database that you can design, fill and edit completely in the working memory. VBA has several other options for storing data: - a dictionary. - a collection.
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)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With