<% 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
%>
|