Tutorial :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


Solution:1

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..


Solution: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)


Solution:3

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »