Search Tools Links Login

A ADO Data Shaping or Multiple SQL Select


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

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 174 times

Categories

ASP/ HTML

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.