Q190605: PRB: Binding Hierarchical Recordset in Data Environment

Article: Q190605
Product(s): Microsoft Visual Basic for Windows
Version(s): 2.5,2.6,2.7,6.0,6.0 SP4
Operating System(s): 
Keyword(s): kbATM kbDatabase kbDataBinding kbVBp600bug kbGrpDSVBDB kbGrpDSMDAC kbDSupport kbADO250
Last Modified: 10-JUN-2002

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

- Microsoft Visual Basic Enterprise Edition for Windows, versions 6.0, 6.0 SP4 
- Microsoft Data Access Components versions 2.5, 2.6, 2.7 
-------------------------------------------------------------------------------

SYMPTOMS
========

In Data Environment, when a hierarchical recordset is used, and the
parent/child/grandchild recordsets are bound, the expected behavior is for all
the child/grandchild recordsets to "stay in sync" with the parent. However, when
record in the parent table moves, the grandchild recordset does not receive
notification that it needs to retrieve the current chapter. This creates a
situation in which the grandchild recordset becomes out of sync.

STATUS
======

This behavior is by design.

MORE INFORMATION
================

The following Visual Basic code reproduces the problem described above. Access
database NWIND.MDB is used here: Customers table is the parent, Orders table,
the child, and OrderDetails table, the grandchild. Two workarounds are provided
by binding the parent/child/grandchild recordsets in code to the DataGrid
controls. Workaround 1 takes the recordset from the Data Environment, and sets
the DataSource property with the recordsets; Workaround 2, bypassing the Data
Environment, uses the ADO SHAPE command to generate the hierarchical recordset.

Steps to Reproduce Behavior
---------------------------

Task One: Data Environment

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

2. On the Project menu, select Components.

3. On the Designers tab, select Data Environment.

4. On the Project menu, select Add Data Environment. If Add Data Environment
  item is not found directly under the Project menu, select More ActiveX
  Designers, and then click Data Environment. This brings up a Data Environment
  Window, named DataEnvironment1 by default.

5. Right-click Connection1, then select Properties. Enter appropriate
  information to establish a connection to Access sample database NWIND.MDB.
  Click OK to save the information.

6. Right-click Connection1, and then select Add Command. Command1 is created by
  default. Rename it as Customers, and set the following property values for
  Customers:

Property     Value
----------------------
CommandText  Customers
CommandType  adCmdTable

7. Right-click Customers, and then select Add Child Command. Command2 is created
  by default. Rename it as Orders.

8. Right-click Orders, and then select Properties.

9. On the General tab, under the Source of Data section, click Database Object,
  and then select Table from the dropdown combobox. Select Orders table as the
  Object Name from the dropdown combobox.

10. On the Relation tab, place a check next to Relate to a Parent Command
  Object. Select Customers as the Parent Command. Then relate the Parent-child
  with CustomerID field, and then click Add button.

11. Click OK to save the information, and return to the Data Environment Window.

12. Add a child command to the Orders command and rename it OrderDetails.

13. Right-click OrderDetails, and then select Properties to bring up the
  OrderDetails Properties dialog. On the General tab, under Source of
  Datasection, click Database Object, and then select Table from the dropdown
  combobox. Select OrderDetails table as the Object Name from the drop-down
  combobox. On the Relations tab, select Orders as the Parent Command and
  relate the two commands on the OrderID field.

Task Two: Other Controls and Visual Basic Code

1. From the Project menu, select Components, and then place a check next to
  Microsoft DataGrid Control 6.0 (OLE DB).

2. Add three DataGrid controls, DataGrid1, DataGrid2, and DataGrid3 to Form1.

3. Add three CommandButton controls, Command1, Command2, and Command3 to Form1.

4. Paste the following code in the General Declaration section of Form1:

        Option Explicit

        Dim cn as ADODB.Connection
        Dim rsCustomers As ADODB.Recordset
        Dim rsOrders As ADODB.Recordset
        Dim rsOrderDetails As ADODB.Recordset

        Private Sub Form_Load()

           Command1.Caption = "Re-pro"
           Command2.Caption = "Workaround I"
           Command3.Caption = "Workaround II"

        End Sub

        Private Sub Command1_Click()

           Set DataGrid1.DataSource = DataEnvironment1
           DataGrid1.DataMember = "Customers"
           Set DataGrid2.DataSource = DataEnvironment1
           DataGrid2.DataMember = "Orders"
           Set DataGrid3.DataSource = DataEnvironment1
           DataGrid3.DataMember = "OrderDetails"

        End Sub

        Private Sub Command2_Click()

           Set DataGrid1.DataSource = Nothing
           DataGrid1.DataMember = ""
           Set DataGrid2.DataSource = Nothing
           DataGrid2.DataMember = ""
           Set DataGrid3.DataSource = Nothing
           DataGrid3.DataMember = ""

           Set rsCustomers = DataEnvironment1.rsCustomers
           Set rsOrders = rsCustomers.Fields("Orders").Value
           Set rsOrderDetails = rsOrders.Fields("OrderDetails").Value

           Set DataGrid1.DataSource = rsCustomers
           Set DataGrid2.DataSource = rsOrders
           Set DataGrid3.DataSource = rsOrderDetails

        End Sub

        Private Sub Command3_Click()
           Dim cn As New ADODB.Connection
           With cn
              .Provider = "MSDataShape"
              .CursorLocation = adUseClient
              .ConnectionString = "dsn=nwind;"
              .Open
           End With

           Dim rsCustomers As New ADODB.Recordset
           Dim rsOrders As ADODB.Recordset
           Dim rsOrderDetails As ADODB.Recordset
           rsCustomers.Source = "SHAPE {SELECT * FROM Customers} " & _
                               "APPEND ((SHAPE {SELECT * FROM Orders} " & _
                               "AS Orders " & _
                               "APPEND ({SELECT * FROM [Order Details]} " & _
                               "AS OrderDetails " & _
                               "RELATE OrderID TO OrderID)) " & _
                               "AS Orders RELATE CustomerID TO CustomerID)"

          rsCustomers.Open , cn, adOpenStatic, adLockOptimistic

          Set rsOrders = rsCustomers.Fields("Orders").Value
          Set rsOrderDetails = rsOrders.Fields("OrderDetails").Value

          Set DataGrid1.DataSource = Nothing
          DataGrid1.DataMember = ""
          Set DataGrid2.DataSource = Nothing
          DataGrid2.DataMember = ""
          Set DataGrid3.DataSource = Nothing
          DataGrid3.DataMember = ""

          Set DataGrid1.DataSource = rsCustomers
          Set DataGrid2.DataSource = rsOrders
          Set DataGrid3.DataSource = rsOrderDetails
        End Sub

  NOTE: Without the workaround, when you move a record from parent table in
  DataGrid1, the child recordset in DataGrid2 is updated accordingly. However,
  the grandchild recordset in DataGrid3 disappears.

REFERENCES
==========

For additional information about SHAPE APPEND syntax and how to traverse
hierarchical recordsets, click the article numbers below to view the articles in
the Microsoft Knowledge Base:

  Q189657 HOWTO: Use the ADO SHAPE Command

  Q185425 ADO Hierarchical Recordsets via SHAPE APPEND via C++/VBA/Java

Additional query words:

======================================================================
Keywords          : kbATM kbDatabase kbDataBinding kbVBp600bug kbGrpDSVBDB kbGrpDSMDAC kbDSupport kbADO250 kbMDAC260 kbADO260 kbmdac270bug kbado270bug 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB600Search kbVB600 kbMDACSearch kbMDAC250 kbMDAC260 kbVB600SP4 kbMDAC270
Version           : :2.5,2.6,2.7,6.0,6.0 SP4
Issue type        : kbprb

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