Using the Recordsets
To gain the most control and flexibility, you can programmatically use the hierarchical recordset by accessing it directly in your application's code. Also, use the Hierarchical Flexgrid with the hierarchical recordset. The Hierarchical Flexgrid works with either an ADO recordset or a Data Environment hierarchical data command.
For example, suppose you want to bind the Hierarchical Flexgrid to your Data Environment hierarchical data command. First, right-click the Toolbox and select Components to add a form to your project. Next, add the Hierarchical Flexgrid to the Toolbox. Then, select the Hierarchical Flexgrid, as Screen 4 shows, and click OK.
Draw the grid on the form, and set the grid's properties to:
Property Value
DataSource envNorthwind
DataMember Customer
These two settings link the grid to the Data Environment command. The first property selects the Data Environment instance. The second property selects the data command. Note that you are using the parent command and not the child command. Next, right-click the grid, and select Retrieve Structure to display the headers for the data columns in design mode.
Now you can execute the application. The results should resemble the form in Screen 5, page 69. To hide and show the detail data, click the + or in the first column. You can expand the use of your hierarchical recordsets to include more than one child command.
The SHAPE command supports other features, such as aggregate functions, to perform operations on the underlying recordset. The aggregate functions let you compute totals, sums, and other functions on data from a child command.
To use aggregate functions, add a new data command and name it ProductOrder. Set its connection to Northwind and its SQL statement to:
SELECT ProductID, ProductName FROM Products
Then add a child command and name it OrderProductDetails:
SELECT OrderID, ProductID, UnitPrice, Quantity
FROM "Order Details"
Click the Relation tab, and relate OrderProductDetails to the ProductOrder command. Relate the two commands on the ProductID field. Close the properties for this command, and open the properties for ProductOrder.
Click the Aggregates tab, then click the Add key to add a new aggregate. Table 1 lists the available aggregate functions. Name it OrderQuantity. Select Sum as the aggregate function to use. Next, select OrderProductDetails in the Aggregate On list to link the aggregate operation to that child data command. The aggregate function can operate now on the child recordset. Select Quantity in the Field list to define the field the sum operation will act on. Close the Properties menu.
Right-click ProductOrder, and select Hierarchy Information. Listing 2 contains the resulting SHAPE statement. This SHAPE statement looks similar to the statement in Listing 1, except that Listing 2's SHAPE statement uses the SUM function to summarize the Quantity field. The aggregate functions operate on fields that are in the SHAPE statement. In this example, the SUM is passing OrderProductDetails .'Quantity'. OrderProductDetails is the alias name assigned to the child recordset in the SHAPE statement, and Quantity is a field in the OrderProductDetails recordset.
Occasionally, a hierarchical recordset might suddenly seem to stop working. This problem usually occurs when you have been in and out of a data command's property pages. I was puzzled when I ran into this problem, because I hadn't made changes to the recordsets. Out of curiosity, I displayed the Hierarchy Information dialog box for the parent recordset, and to my surprise, it contained nested SHAPE statements. Although you can use nested SHAPE statements, I was not trying to. The problem stemmed from my adding aggregate information to the recordset. To fix it, I deleted the child command and recreated it.
In building applications with VB 6.0, the developers at my company have found occasions when the Data Environment doesn't seem to work correctly. For example, it's easy to programmatically walk through hierarchical recordsets using ADO but not with the Data Environment. So, they now test the Data Environment with complex SQL and SHAPE statements that match the application before building a complex application that is tied to the Data Environment.
The Data Environment designer is quite helpful for building queries, even queries to use with ADO. So use the Data Environment to build powerful SHAPE statements, then place the SHAPE statement in your ADO code and execute it.
In future columns, I will explore more uses of the Data Environment designer. Specifically, I will explore how to use the designer with VB, ADO, MTS, and other services.
End of Article
Prev. page
1
[2]
next page -->
