'**************************************
    ' Name: A ADO Data Shaping or Multiple S
    '     QL Select
    ' Description:Do you have slow running m
    '     ulitple SELECT Statements or long report
    '     s to fill on a web page. Use the Microso
    '     ft Shape Command. Learn to use ADO 2.1 a
    '     nd greater advance features. This code i
    '     s great for three things, (1) Very fast 
    '     way to do multiple SQL select statements
    '     and reports. (2) Great for databases not
    '     Normalized. (3) Avoids multiple nested s
    '     ingle threaded ADO Record Sets loops whi
    '     ch are very slow.
    ' By: Rob Gerwing
    '
    ' Assumes:Basic ADO Recordset use in Mic
    '     rosoft Active server Pages. Basic Knowle
    '     dge of SQL "SELECT" statements.
    '
    'This code is copyrighted and has    ' limited warranties.Please see http://w
    '     ww.Planet-Source-Code.com/xq/ASP/txtCode
    '     Id.6512/lngWId.4/qx/vb/scripts/ShowCode.
    '     htm    'for details.    '**************************************
    
    <%Response.Buffer = True
    Const adOpenForwardOnly = 2
    Dim connShape,strShape
    Dim objConn,objRS,objStartDate,objEndDate
    'ADO CONNECTION
    Set connShape = Server.CreateObject("ADODB.Connection")
    connShape.Provider = "MSDataShape" 'Tell ADO To expect MSShape Command In SQL Syntax
    connShape.Open "DSN=NAME-OF-ODBC-SOURCE" 'Insert your Data Source Name String
    'ADO RECORDSET
    Set objRS = Server.CreateObject("ADODB.Recordset")
    'Shape SQL Syntax
    [Available at Microsoft KB Article Q189657]
    'WHY DID I USE SHAPE AND NOT A JOIN?
    'Look at the 2nd and 3rd SELECTS, I need
    '     ed to retrieve a record associated to
    'order_id BUT the same field name "event
    '     _value" and different event_types.
    'Working Example -->
    	strShape = "SHAPE {Select order_id,f_name,l_name FROM Order_Table"&_
    	" WHERE l_name = 'SMITH' ORDER BY l_name} AS OrderData "&_
    		 "APPEND "&_
    		 "({ Select order_id, event_value, event_type FROM event" &_
    		 " WHERE event_type = 'UserStartDate' } " &_
    		 " RELATE order_id To order_id) AS STARTDATE, "&_
    		 " ({ Select order_id, event_value, event_type FROM event" &_
    		 " WHERE event_type = 'UserEndDate' } " &_
    		 " RELATE order_id To order_id) AS ENDDATE"
    'Open RecordSet
    objRS.OPEN strShape,ConnShape,adOpenForwardOnly
    Response.Write "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
    Do While Not objRS.EOF 'Looping through Parent Record Set
    'Take from Parent Record Set
    Response.Write("<TR><TD>" & objRS("order_id") & "</TD>")
    Response.Write("<TD>" & objRS("l_name") & ", " & objRS("f_name") & "</TD>")
    'StartDate 1st Child RecordSet No Loop, 
    '     EXPECTING ONLY ONE RECORD VALUE
    	'Must use "STARTDATE" as reference In SQL "AS STARTDATE"
    	Set objStartDate = objRS("STARTDATE").Value 
    	if objStartDate.Eof = True Then
    		Response.Write("<TD> </TD>")
    	Else
    		Response.Write("<TD>" & objStartDate("event_value") & "</TD>")
    	End if
    	objStartDate.Close
    'EndDate 2nd Child RecordSet Loop Used,E
    '     XPECTING MORE MULTIPLE RECORD VALUES
    	'Must use "ENDDATE" as reference In SQL "AS ENDDATE"
    	Set objEndDate = objRS("ENDDATE").Value
    	if objEndDate.Eof = True Then
    		Response.Write("<TD> </TD>")
    	Else
    		Response.Write("<TD>")
    			While Not objEndDate.Eof
    				 Response.write (objEndDate("event_value") & ",")
    			objEndDate.MoveNext
    			Wend
    		Response.Write("</TD>")
    	End if
    		objEndDate.Close
    Response.Write "</TR>"
    objRS.MoveNext 
    LOOP
    Response.Write "</TABLE>"
    'Clean Up
    connShape.Close
    objRS.Close
    Set connShape = Nothing
    Set objRS = Nothing
    %>