Microsoft KB Archive/192383

= PRB: Data Bindings Are Lost When ADO Recordset Is Closed =

Article ID: 192383

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q192383



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.



Steps to Reproduce Behavior
 Start a new Standard EXE project in Visual Basic. Form1 is created by default. Select Components on the Project menu, and then select the most recent version of the Microsoft DataGrid Control (OLE DB). Select References on the Project menu and click "Microsoft ActiveX Data Objects Library."  Place the following objects on the form:   Control             Name --  Textbox             txtCustomerID Textbox            txtCity DataGrid           gridCustomers CommandButton      cmdModifyData CommandButton      cmdCloseOpen CommandButton      cmdRequery

  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

</li> Modify the strPathToNWind constant in the code so that it contains the path to the Northwind database (NWind.mdb) on your computer.</li> Run the project.</li> 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.</li> 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.</li> Exit the project by clicking on the "X" button in the upper right corner of the form.</li>  Modify the cmdCloseOpen_Click event and un-comment the following line: ReBindMyControls

</li> Re-run the project.</li> 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.</li> 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.</li></ol>

Keywords: kbdatabinding kbprb kbcode KB192383

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.