PRB: Data Bindings Lost When ADO Recordset Is Closed

• Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 6.0

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 (OLE DB)".



3. Select References on the Project menu and click "Microsoft ActiveX Data Objects 2.0 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.3.51;" & _
                    "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.

Keywords          : kbcode kbDataBinding kbVBp600 
Version           : WINDOWS:6.0
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: June 2, 1999