A ADO Data Shaping or Multiple SQL Select

Posted: 2002-06-01
By: ArchiveBot
Viewed: 152

Filed Under:

No attachments for this post

Do you have slow running mulitple SELECT Statements or long reports to fill on a web page. Use the Microsoft Shape Command. Learn to use ADO 2.1 and greater advance features. This code is 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 single threaded ADO Record Sets loops which are very slow.

Original Author: Rob Gerwing

Assumptions

Basic ADO Recordset use in Microsoft Active server Pages. Basic Knowledge of SQL "SELECT" statements.

Code

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

"
Do While Not objRS.EOF 'Looping through Parent Record Set
'Take from Parent Record Set
Response.Write("

")
Response.Write("

")
	
'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("

")
	Else
		Response.Write("

")
	End If
			
	objStartDate.Close
				
'EndDate 2nd Child RecordSet Loop Used,EXPECTING 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("

")
	Else
		Response.Write("

")
	End If
			
		objEndDate.Close
Response.Write "

"
objRS.MoveNext 
LOOP
Response.Write "

" & objRS("order_id") & "

" & objRS("l_name") & ", " & objRS("f_name") & "

" & objStartDate("event_value") & "

")
while not objEndDate.Eof
Response.write (objEndDate("event_value") & ",")
objEndDate.MoveNext
wend
Response.Write("

"
'Clean Up
connShape.Close
objRS.Close
Set connShape = Nothing
Set objRS = Nothing
%>

Comments on this post

No comments have been added for this post.

You must be logged in to make a comment.