In Part 1, we showed what SHAPE command we needed to use to obtain a proper data shape for our professor "parent" with its two "children": the classes taught and the research projects our professor is working on. Now that we have this information in a nice, recordset-based hierarchy, we can display the two children via the same method discussed in our first article on Data Shaping.

Here is the source code to display the two children:

<%@ Language=VBScript %> <% Option Explicit %> <% Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.Provider = "MSDataShape" objConn.Open "DSN=University" Dim objClassRS, objProfessorRS, objProjectRS Dim strShapeSQL strShapeSQL = "SHAPE {SELECT ProfessorID, FirstName, LastName, PhoneNumber " & _ " FROM Professor}" & _ " APPEND({SELECT ClassName, RefNumber, ProfessorID FROM Class} AS Class " & _ " RELATE ProfessorID TO ProfessorID), " & _ " ({SELECT Title, ProfessorID FROM Project} AS Project " & _ " RELATE ProfessorID TO ProfessorID)" Set objProfessorRS = Server.CreateObject("ADODB.Recordset") objProfessorRS.Open strShapeSQL, objConn Do While Not objProfessorRS.EOF Response.Write "<BR>" Response.Write objProfessorRS("FirstName") & " " Response.Write objProfessorRS("LastName") & _ " (Phone: " & objProfessorRS("PhoneNumber") & ")<BR>" Response.Write "&nbsp;&nbsp;&nbsp;<B>Classes:</B><BR>" Set objClassRS = objProfessorRS("Class").Value Do While Not objClassRS.EOF Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;" & _ objClassRS("ClassName") & _ " (" & objClassRS("RefNumber") & ")<BR>" objClassRS.MoveNext Loop Response.Write "&nbsp;&nbsp;&nbsp;<B>Projects:</B><BR>" Set objProjectRS = objProfessorRS("Project").Value Do While Not objProjectRS.EOF Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;" & _ objProjectRS("Title") & "<BR>" objProjectRS.MoveNext Loop objProfessorRS.MoveNext Loop objProfessorRS.Close Set objProfessorRS = Nothing objConn.Close Set objConn = Nothing %>

To display the projects that the current professor works on, we simply iterate through the Project child recordset like we did with the Class child recordset. For a more detailed discussion on how to display data shapes, be sure to read our first article on Data Shaping.

With some sample data, the above code will produce the following output:

Dr. Smith (Phone: 123-4567)
   Classes:
     Analysis of Algorithms (CS355)
     Artificial Intelligence (CS341)
   Projects:
     Self-Documenting Programming
     Optimization Routines

Dr. Johnson (Phone: 123-8901)
   Classes:
     Distributed Systems (CS333)
   Projects:
     A.I. In the Workplace

Dr. Mitchell (Phone: 123-1234)
   Classes:
     Formal Languages & Automata Theory (CS330)
     Study of Film (ART85)
     Software Engineering (CS301)
   Projects:
     Software Reuse Study
     Image Processing
     Image Recognition

In Part 3, we will look at how to create data shapes containing grandchildren!