Custom Hierarchical Recordset Demo

This demo illustrates how to create custom hierarchical Recordsets. The hierarchical Recordset is a list of employees and their associated projects.


List of Employees and their Projects

Scott Mitchell
Bill Gates
Larry Ellison


Source Code
<% Option Explicit %>
<!--#include virtual="/webtech/code/adovbs.txt"-->
<%
  'This include file creates a database connection
  'to the SQL pubs database
  Dim objConn
  Set objConn = Server.CreateObject("ADODB.Connection")

  Dim strConnString 
  'We want the MSDataShape provider and no Data Provider
  strConnString = "Data Provider=NONE; Provider=MSDataShape"
  objConn.Open strConnString		'Open the connection

  'Create a Recordset
  Dim objParentRS, objChildRS
  Set objParentRS = Server.CreateObject("ADODB.Recordset")
  Set objChildRS = 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


  'Now, we've created our custom hierarchical Recordset...
  'Time to populate it!
  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 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



  'Ok, now let's list out the employees and their associated
  'projects.  This will require two Do ... Loops

  Response.Write("<h1>List of Employees and their Projects</h1>")

  '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


  'Clean up...
  objParentRS.Close
  Set objParentRS = Nothing

  Set objChildRS = Nothing

  objConn.Close
  Set objConn = Nothing
%>


[Return to the article]