PRB: ADO: Recordcount May Return -1
ID: Q194973
|
The information in this article applies to:
-
ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2
SYMPTOMS
When you request the RecordCount for a serverside recordset, a -1 may
return. This occurs with ActiveX Data Objects (ADO) version 2.0 when the
CursorType is adOpenForwardonly or adOpenDynamic. This occurs with ADO 1.5
only when the cursortype is adOpenForwardonly. Testing with the OLEDB
provider for JET and SQL Server produces varying results, depending on the
provider.
Providers may not support some CursorTypes. When you select a CursorType
that is not supported, the provider should select the CursorType closest to
what you request. Please refer to the documentation that comes with your
provider. Also, please note that not all combinations of LockType and
CursorType work together. Changing a LockType may force a change in the
CursorType. Be sure to use debug to check the value of CursorType when
conducting tests with your OLEDB provider.
CAUSE
The number of records in a dynamic cursor may change. Forward only cursors
do not return a RecordCount.
RESOLUTION
Use either adOpenKeyset or adOpenStatic as the CursorType for server side
cursors or use a client side cursor. Client side cursors use only
adOpenStatic for CursorTypes regardless of which CursorType you select.
STATUS
This behavior is by design.
MORE INFORMATION
Steps to Reproduce Behavior
- Open a standard .exe project in Visual Basic.
- From the Project menu,
choose References.
- Select the Microsoft Active Data Object Library.
- Paste the following code in the form code window:
Option Explicit
Dim rs As ADODB.Recordset
Private Sub Form_Load()
'set up rs
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open "Select ProductID from products", & _
"Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Data Source=d:\vb5_win95\nwind.mdb", _
adOpenDynamic, adLockUnspecified
Debug.Print rs.RecordCount
End Sub
- Replace the preceding Data Source with a Data Source on your computer.
Run the preceding form and note the record count. Change the CursorType
to adOpenForwardonly and note the record count.
- Change the CursorLocation to adUseClient and experiment with the
different CursorTypes. In all cases the correct record count returns.
REFERENCES
ActiveX Data Objects (ADO) Help; search on: "CursorType Property."
Additional query words:
Keywords : kbADO150 kbADO200 kbDatabase kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:1.5,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbprb