PRB: Data Bindings Are Lost When ADO Recordset Is Closed

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 then select the most recent version of the Microsoft DataGrid Control (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.