PRB: Run-time Bound Server-side ADO Recordset Skips Second Record

ID: Q244801

The information in this article applies to:

Microsoft Visual Basic Professional and Enterprise Editions, 32-bit only, for Windows, version 6.0
ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1, 2.1 SP1, 2.1 SP2

SYMPTOMS

When using a recordset with a server-side forward-only, or dynamic cursor and binding to a textbox at run-time it is possible for the recordset to skip the second record. The textbox will initially display the first record. A MoveNext causes it to display the third record. With the dynamic cursor, a MovePrevious will then display the second record correctly. This behavior is only exhibited immediately after binding or after a ReQuery. This behavior is also shown if the textbox is bound from the Data Environment.

RESOLUTION

The recordset can be displayed correctly by reading one of the fields prior to binding to the text box. For a run-time bound control, use the following code prior to binding the textbox.


Dim strTemp as String
strTemp = rs(0)         'rs is the Recordset

When using the Data Environment, the only way around this problem is to bind the textbox at run-time and use the workaround earlier. When you want to call the ReQuery method, you should re-bind the recordset. The following code shows the workaround with ReQuery.


rs.ReQuery
Dim strTemp as String
strTemp = rs(0)
Set Text1.DataSource = rs

MORE INFORMATION

Steps to Reproduce Behavior

Create a New .exe Project.

Add the Microsoft ActiveX Data Objects 2.x Library to your project.

Add a Textbox (Text1) and two Buttons (Command1 and Command2) to your form.

Add the following code to your project.


Option Explicit

Dim rs As Recordset

Private Sub Command1_Click()
   With rs
      .MoveNext
      If .EOF Then
         .MoveLast
      End If
   End With
End Sub

Private Sub Command2_Click()
   With rs
      .MovePrevious
      If .BOF Then
         .MoveFirst
      End If
   End With
End Sub

Private Sub Form_Load()
   Dim cn As Connection
   Set cn = New Connection
   cn.CursorLocation = adUseServer
   cn.Open "DSN=Northwind"
   
   Set rs = New Recordset
   Set rs.ActiveConnection = cn
   rs.CursorType = adOpenDynamic
   rs.Open "SELECT EmployeeID FROM Employees ORDER BY EmployeeID"
   
   'Uncomment the next two lines to fix problem
   'Dim strTemp As String
   'strTemp = rs("EmployeeID")
   
   Set Text1.DataSource = rs
   Text1.DataField = "EmployeeID"
   
   Command1.Caption = "Next"
   Command2.Caption = "Previous"
End Sub

Run the Project. The textbox displays the first record.

Click Next. The textbox will now show the third record.

Additional query words:

Keywords : kbADO kbADO200 kbADO201 kbADO210 kbDataBinding kbVBp600 kbDataEnv kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:2.0,2.01,2.1,2.1 SP1,2.1 SP2,6.0
Platform : WINDOWS
Issue type : kbprb