When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Sunday, June 03, 2001

Creating Custom Hierarchical Recordsets, Part 2

By Scott Mitchell

  • Read Part 1

  • 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!

    - continued -

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

    	NEW DataTypeConstant AS ParentColumnName1,
    	NEW DataTypeConstant AS ParentColumnName2,
    	NEW DataTypeConstant AS ParentColumnNameN,
    		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("EmployeeID") = 1
      objParentRS("Name") = "Scott Mitchell"
      objParentRS("EmployeeID") = 2
      objParentRS("Name") = "Bill Gates"
      objParentRS("EmployeeID") = 3
      objParentRS("Name") = "Larry Ellison"

    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("ProjectID") = 1
      objChildRS("ProjectName") = "Wireless Something-or-Other"
      objChildRS("EmployeeID") = 1
      objChildRS("ProjectID") = 2
      objChildRS("ProjectName") = "Floor Sweeping"
      objChildRS("EmployeeID") = 2
      objChildRS("ProjectID") = 3
      objChildRS("ProjectName") = "Janirotial Duties"
      objChildRS("EmployeeID") = 2
      objChildRS("ProjectID") = 4
      objChildRS("ProjectName") = "Mopping"
      objChildRS("EmployeeID") = 3
      objChildRS("ProjectID") = 5
      objChildRS("ProjectName") = "Web Site Redesign"
      objChildRS("EmployeeID") = 1

    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
      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>")
    [View a live demo!]

    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!

  • By Scott Mitchell


  • View a live demo!
  • Read Using Custom Recordsets
  • Read Data Shaping
  • Read Advanced Data Shaping Techniques
  • Read Overcoming Data Shaping Limitations

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article