NOTE: click here to see superior alternatives to .RecordCount.

RecordCount returns -1
The use of the ADO Recordset's .RecordCount property requires either the use of:

• Static or Keyset server-side cursors or
• A client-side cursor (which returns a Static cursor)

(Note: some OLEDB Providers will return the correct recordcount with an adOpenDynamic cursor, others will not).

By default Recordsets are opened server-side, and with an adOpenForwardOnly cursor. Attempting to access the .RecordCount property with this type of cursor will return -1.

The easiest way to fix this is to change the cursor type to adOpenStatic. Doing this requires you to explicitly create a recordset object:

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText

Attempting to implicitly create a recordset, eg like this:

Set objRS = objConn.execute(strSQL)

will not work, as the implicitly created recordset will have a default adOpenForwardOnly cursor.

As mentioned above, the alternative method is to use a client-side cursor. The client referred in this case is the OLEDB Cursor Service.

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseClient
objRS.Open strSQL, objConn,,adLockReadOnly, adCmdText

In order to be able to use server-side cursors and .Recordcount, the Recordset object must support either Approximate Positioning or Bookmarking. There has been discussion on the ActiveServerPages list to the effect that the MS Oracle OLEDB Provider (or earlier versions of this provider) do not support either Approximate Positioning or Bookmarking, hence require client-side cursors in order for .RecordCount to work.

The use of ADO constants requires you to define them. You can get information on doing this here. For more information on the Recordset's .Open method click here.

Back to FAQ Listing