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

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.