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: Saturday, September 25, 1999

Data Shaping, Part 3

In Part 2 we looked at the syntax for data shaping. Great, but how do we obtain a data shape through ADO? For starters, we need to set the Provider option of the Connection object to MSDataShape. So, before you open your Connection object, make sure you set the provider. Creating and opening a connection object might look like:

- continued -

   Dim objConn
   Set objConn = Server.CreateObject("ADODB.Connection")

   objConn.Provider = "MSDataShape"
   objConn.ConnectionString = "DSN=University"

The rest of the code doesn't change much. We simply use a recordset to obtain our data shape. We can create a recordset object and use the Open method, just like normal. Here is some syntax:

   strShapeSQL = "SHAPE {SELECT ProfessorID, FirstName, LastName, " & _
			"PhoneNumber FROM Professor}" & _
		" APPEND({SELECT ClassName, RefNumber, ProfessorID " & _
			"FROM Class} AS Class " & _
		" RELATE ProfessorID TO ProfessorID)"

   Dim objProfessorRS
   Set objProfessorRS = Server.CreateObject("ADODB.Recordset")
   objProfessorRS.Open strShapeSQL, objConn

The above code uses the data shaping syntax that obtains the list of professors and the classes they teach. This syntax was shown in Part 2. Now, we can simply iterate through the recordset of professors! Remember, we have a class recordset inside our professor recordset. We'll iterate through that for each professor. Here is the syntax to iterate through each professor and class recordset:

'Our class recordset Dim objClassRS Do While Not objProfessorRS.EOF Response.Write "<BR>" Response.Write objProfessorRS("FirstName") & " " Response.Write objProfessorRS("LastName") & _ " (Phone: " & objProfessorRS("PhoneNumber") & ")<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 objProfessorRS.MoveNext Loop 'Clean up... objProfessorRS.Close Set objProfessorRS = Nothing objConn.Close Set objConn = Nothing

Notice how the outter Do ... Loop steps through the professor recordset. Inside that loop, we set objClassRS to the Class recordset in the professor recordset! We then iterate through the class recordset. The output for this script is:

Dr. Smith (Phone: 123-4567)
    Analysis of Algorithms (CS355)
    Artificial Intelligence (CS341)

Dr. Johnson (Phone: 123-8901)
    Distributed Systems (CS333)

Dr. Mitchell (Phone: 123-1234)
    Formal Languages & Automata Theory (CS330)
    Study of Film (ART85)
    Software Engineering (CS301)

If you used the syntax where the Class table was the parent SQL statement and the Professor table the child, a slight change in the code above could yield a listing of classes and the professors that taught them. However, I will leave this as an exercise to the reader! :)

Well, that's data shaping in a nutshell. There are a bunch of other neat things that data shaping can do, such as related grandchilden, multiple related children... it's really quite amazing. Those topics are covered in Advanced Data Shaping!

Happy Programming!

(Read Part 1)
(Read Part 2)
(Read Advanced Data Shaping)


  • Some data shaping code in text format

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