Creating Custom Hierarchical Recordsets, Part 2

In Part 1 we briefly looked at data shaping and custom Recordsets, mentioning that ADO supported the creation of custom hierarchical Recordsets. In this part, we'll look at the specific syntax needed to start creating such hierarchical Recordsets!

The Syntax for Creating Custom Hierarchical Recordsets
When creating a custom hierarchical Recordset, use the following syntax:

SHAPE APPEND NEW DataTypeConstant AS ParentColumnName1, NEW DataTypeConstant AS ParentColumnName2, ... NEW DataTypeConstant AS ParentColumnNameN, ((SHAPE APPEND NEW DataTypeConstant AS ChildColumnName1, NEW DataTypeConstant AS ChildColumnName2, ... NEW DataTypeConstant AS ChildColumnNameN) RELATE ParentColumn TO ChildColumn) AS Alias

In the above example note that there are places to specify a column's DataTypeConstant. These constants are defined in adovbs.inc, so you will need to, at minimum, use a server-side include to dump the contents of adovbs.inc into this page. (To learn more about adovbs.inc be sure to read this article.) You should also take a quick look at the various data type names (they're under the heading DataTypeEnum Values; you can view a copy of adovbs.inc online here.)

Of course, to turn the above syntax into an actual hierachical Recordset, we must create a Recordset object. Since we are going to be adding rows to this custom Recordset, it is important to specify that the Recordset's LockType is something other than read-only (adLockReadOnly). In the following code block we specify the specific shape syntax, and create the hierarchical Recordset:

Dim objParentRS Set objParentRS = Server.CreateObject("ADODB.Recordset") 'Create our custom data shape (hierarchical Recordset) Dim strSQL strSQL = "SHAPE APPEND " & _ " NEW adInteger AS EmployeeID, " & _ " NEW adVarChar(50) AS Name, " & _ " ((SHAPE APPEND " & _ " NEW adInteger AS ProjectID, " & _ " NEW adVarChar(50) AS ProjectName, " & _ " NEW adInteger AS EmployeeID) " & _ " RELATE EmployeeID TO EmployeeID) AS rsProjects" objParentRS.LockType = adLockOptimistic objParentRS.Open strSQL, objConn

Finally, we need to add our data to the Recordset. Since we have a hierarchical Recordset we need to add both information about each Employee and information on the projects they are working on. To make things simple, let's begin by adding information on our employees. We use the .AddNew method to add a new column to the Recordset, and the Update method to commit the newly added information. Below you can see that our company has three employees:

objParentRS.AddNew objParentRS("EmployeeID") = 1 objParentRS("Name") = "Scott Mitchell" objParentRS.Update objParentRS.AddNew objParentRS("EmployeeID") = 2 objParentRS("Name") = "Bill Gates" objParentRS.Update objParentRS.AddNew objParentRS("EmployeeID") = 3 objParentRS("Name") = "Larry Ellison" objParentRS.Update

Now that we've added our three employees, we can go ahead and add the list of projects for each employee. Note that we begin by creating a child Recordset object that is set to the Value of the rsProjects column in our parent Recordset:

Dim objChildRS Set objChildRS = Server.CreateObject("ADODB.Recordset") 'Now we have to add projects for each employee Set objChildRS = objParentRS("rsProjects").Value objChildRS.AddNew objChildRS("ProjectID") = 1 objChildRS("ProjectName") = "Wireless Something-or-Other" objChildRS("EmployeeID") = 1 objChildRS.Update objChildRS.AddNew objChildRS("ProjectID") = 2 objChildRS("ProjectName") = "Floor Sweeping" objChildRS("EmployeeID") = 2 objChildRS.Update objChildRS.AddNew objChildRS("ProjectID") = 3 objChildRS("ProjectName") = "Janirotial Duties" objChildRS("EmployeeID") = 2 objChildRS.Update objChildRS.AddNew objChildRS("ProjectID") = 4 objChildRS("ProjectName") = "Mopping" objChildRS("EmployeeID") = 3 objChildRS.Update objChildRS.AddNew objChildRS("ProjectID") = 5 objChildRS("ProjectName") = "Web Site Redesign" objChildRS("EmployeeID") = 1 objChildRS.Update

Note that since each parent item (employee) is related to each project via the EmployeeID in the child and parent Recordsets, we simply alter the EmployeeID column in the child Recordsets to assign the various projects to various employees. Note that we could have also iterated through the rows of the objParentRS Recordset, setting objChildRS equal to the Value of the rsProjects column.

Now that we have inserted all this information into our hierarchical Recordset, how do we go about displaying it? Since we have a Recordset within a Recordset, we must have two Do While Not objRS.EOF loops: one to iterate through each row in the parent Recordset and the other to iterate through each child Recordset for each row in the parent Recordset:

'Iterate through the Recordset objParentRS.MoveFirst Do While Not objParentRS.EOF Response.Write(objParentRS("Name") & "<br><ul>") 'Now Set the Child recordset to rsTitles Set objChildRS = objParentRS("rsProjects").Value 'Loop through the Child recordset If objChildRS.EOF Then Response.Write("<b>No Projects</b>") Do While Not objChildRS.EOF Response.Write(objChildRS("ProjectName") & "<br>") objChildRS.MoveNext Loop Response.Write("</ul>") objParentRS.MoveNext Loop

[View a live demo!]

Conclusion
In this article we examined how to create custom hierarchical Recordsets in ADO. While this article only examined how to create single custom parent-child hierarchies, you can create much more complex custom hierarchies using the same syntax. For information on more complex data shaping relationships be sure to read Overcoming Data Shaping Limitations and Advanced Data Shaping Techniques.

Happy Programming!