PRB: Data Bindings Lost When ADO Recordset Is Closed

• ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2

SYMPTOMS

If you have controls that are bound to a closed recordset and you then open (or re-open) that recordset, the controls do not display data from the recordset. Bound TextBox controls display what was in the TextBox before the recordset was closed. Bound Grid controls appear blank.

RESOLUTION

After you have opened (or re-opened) your recordset, you must re-bind your controls in order to display data from that recordset.

- or -

You can use the Requery method on the recordset instead of closing and re- opening the recordset without needing to re-bind your controls.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new Standard EXE project in Visual Basic. Form1 is created by default.



2. Select Components on the Project menu and click "Microsoft DataGrid Control 6.0 (SP3) (OLE DB.)"



3. Select References on the Project menu and click "Microsoft ActiveX Data Objects Library."



4. Place the following objects on the form:




   Control             Name
   ----------------------------------
   Textbox             txtCustomerID
   Textbox             txtCity
   DataGrid            gridCustomers
   CommandButton       cmdModifyData
   CommandButton       cmdCloseOpen
   CommandButton       cmdRequery
 

5. Add the following code to your form:




      Const strPathToNWind As String = "C:\Path\To\NWind.MDB"
      Dim cnNWind As ADODB.Connection
      Dim rsCustomers As ADODB.Recordset

      Private Sub Form_Load()
          Dim strConn As String
          Dim strSQL As String

          cmdModifyData.Caption = "Modify Data"
          cmdCloseOpen.Caption = "Close / Open"
          cmdRequery.Caption = "Requery"

          strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & strPathToNWind & ";"
          strSQL = "SELECT CustomerID, City FROM Customers"

          Set cnNWind = New ADODB.Connection
          cnNWind.CursorLocation = adUseClient
          cnNWind.Open strConn

          Set rsCustomers = New ADODB.Recordset
          rsCustomers.Open strSQL, cnNWind, adOpenStatic, _
                           adLockReadOnly, adCmdText

          txtCustomerID.DataField = "CustomerID"
          Set txtCustomerID.DataSource = rsCustomers
          txtCity.DataField = "City"
          Set txtCity.DataSource = rsCustomers
          Set gridCustomers.DataSource = rsCustomers
      End Sub

      Private Sub ReBindMyControls()
          Set txtCustomerID.DataSource = rsCustomers
          Set txtCity.DataSource = rsCustomers
          Set gridCustomers.DataSource = rsCustomers
      End Sub

      Private Sub cmdModifyData_Click()
          Dim strSQL As String
          Dim strNewCity As String
          Dim intRecordsAffected As Integer

          strNewCity = InputBox("Enter a new value for the city")
          If strNewCity <> "" Then
             strSQL = "UPDATE Customers " & _
                      "SET City = '" & strNewCity & "' " & _
                      "WHERE CustomerID = '" & rsCustomers!CustomerID & "'"
             cnNWind.Execute strSQL, intRecordsAffected, adExecuteNoRecords
             MsgBox intRecordsAffected & " record(s) affected"
          Else
             MsgBox "No update performed"
          End If
      End Sub

      Private Sub cmdCloseOpen_Click()
          rsCustomers.Close
          rsCustomers.Open
          'ReBindMyControls
          MsgBox "Recordset reopened" & vbCrLf & _
                 "Current customer's city: " & rsCustomers!City
      End Sub

      Private Sub cmdRequery_Click()
          rsCustomers.Requery
          MsgBox "Recordset reopened" & vbCrLf & _
                 "Current customer's city: " & rsCustomers!City
      End Sub

      Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As _
                                   Integer)
          rsCustomers.Close
          Set rsCustomers = Nothing

          cnNWind.Close
          Set cnNWind = Nothing
      End Sub
 

6. Modify the strPathToNWind constant in the code so that it contains the path to the Northwind database (NWind.mdb) on your computer.



7. Run the project.



8. Click Modify Data to enter a new value for the current customer's city. The code modifies that row in the database without changing the row in the recordset.



9. Click Close/Open to close and re-open the recordset. A message box appears that says that the recordset has been re-opened and displays the value for the current customer's city in the recordset object. You see the value that you entered in step 8 in this message box, but the text box still displays the old value and the grid is blank.



10. Exit the project by clicking on the "X" button in the upper right corner of the form.



11. Modify the cmdCloseOpen_Click event and un-comment the following line:




      ReBindMyControls
 

12. Re-run the project.



13. Click Modify Data to enter a new value for the current customer's city. The code modifies that row in the database without changing the row in the recordset.



14. Click Close/Open to close and re-open the recordset. A message box appears that says that the recordset has been re-opened and displays the value for the current customer's city in the recordset object. You see the value that you entered in step 13 in this message box. The grid and text box now display the new value.


NOTE: You can also click Requery to use the Requery method on the recordset instead of using the Close and Open methods. If you use this method, you do not need to re-bind your controls.

Setting a control's DataSource or DataMember properties re-binds the control.

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by David Sceppa, Microsoft Corporation

Additional query words:

Keywords : kbcode kbDataBinding kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbprb