VB icon

A ADO Data Shaping or Multiple SQL Select

Email
Submitted on: 1/5/2015 12:32:00 PM
By: Rob Gerwing (from psc cd)  
Level: Intermediate
User Rating: By 6 Users
Compatibility: ASP (Active Server Pages)
Views: 2455
 
     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.
 
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
				
'**************************************
' Name: A ADO Data Shaping or Multiple SQL Select
' Description: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.
' By: Rob Gerwing (from psc cd)
'
' Assumes:Basic ADO Recordset use in Microsoft Active server Pages. Basic Knowledge of SQL "SELECT" statements.
'**************************************

<%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 "<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,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("<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
%>


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Intermediate category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.