DOCUMENT:Q190605 07-AUG-1998 [vbwin] TITLE :BUG: Binding Hierarchical Recordset in Data Environment PRODUCT :Microsoft Visual Basic for Windows PROD/VER: OPER/SYS:WINDOWS KEYWORDS: ====================================================================== --------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual Basic Enterprise Edition for Windows, version 6.0 --------------------------------------------------------------------- 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 ====== Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this bug and will post new information here in the Microsoft Knowledge Base as it becomes available. 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. From the Project menu, select Components, select the Designers tab, and then place a check next to Data Environment. 3. From 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. 4. 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. 5. Right-click Connection1, 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 6. Right-click Customers, then select Add Child Command. Command2 is created by default. Rename it as Orders. 7. Right-click Orders, then select Properties to bring up Orders Properties window. 8. On the General tab, Source of Data section, click Database Object, then select Table from the dropdown combobox. Select Orders table as the Object Name from the dropdown combobox. 9. 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. Then click Add button. 10. Click OK to save the information, and return to the Data Environment Window. 11. Add a child command to the Orders command and rename it OrderDetails. 12. Right-click OrderDetails, and select Properties to bring up the OrderDetails Properties dialog. On the General tab, Source of Data section, click Database Object, then select Table from the dropdown combobox. Select OrderDetails table as the Object Name from the rop-down combobox. In 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 on SHAPE APPEND syntax and how to traverse hierarchical recordsets, please see the following articles in the Microsoft Knowledge Base: ARTICLE-ID: Q189657 TITLE : HOWTO: Use the ADO SHAPE Command ARTICLE-ID: Q185425 TITLE : ADO Hierarchical Recordsets via SHAPE APPEND via C++/VBA/Java Additional query words: kbADO kbDatabase kbDataBinding kbdse kbDSupport kbVBp600bug ====================================================================== Platform : WINDOWS Issue type : kbbug ============================================================================= 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.