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

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
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:

    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!

  • By Scott Mitchell


    Attachments:

  • 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] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article