DOCUMENT:Q192383  08-SEP-1998  [vbwin]
TITLE   :PRB: Data Bindings Lost When ADO Recordset Is Closed
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:6.0
OPER/SYS:WINDOWS
KEYWORDS:kbVBp600 kbDataBinding kbcode

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - 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.

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.

(c) Microsoft Corporation 1998, All Rights Reserved. Contributions by David
Sceppa, Microsoft Corporation.

Additional query words:
======================================================================
Keywords          : kbVBp600 kbDataBinding kbcode
Version           : WINDOWS:6.0
Platform          : WINDOWS
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1998.