<%@ Language=VBScript %> <% Option Explicit %> <% Dim objCN Dim strsql, objRS, objRS2 Dim objCmd Dim RecordsArray1, i Dim RecordsArray2, j Dim S, E ' Connect to the database Set objRS = Server.CreateObject("ADODB.Recordset") Set objCN = Server.CreateObject("AdoDB.Connection") objCN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"&_ "Data Source=C:\Inetpub\wwwroot\data\Northwind.mdb;"&_ "Persist Security Info=False" objCN.Open ' Create the second recordset object, and initialize it Set objRS2 = Server.CreateObject("ADODB.RecordSet") With objRS2 .CursorType = adOpenForwardOnly .LockType = adLockReadOnly .CursorLocation = adUseServer End With ' Create a parameterized command object Set objCmd = Server.CreateObject("ADODB.Command") With objCmd .ActiveConnection = objCN .CommandType = adCmdText .Prepared = True .CommandText = _ " SELECT ProductName, [Order Details].UnitPrice "&_ " FROM [Order Details] "&_ " INNER JOIN Products ON [Order Details].ProductID=Products.ProductID"&_ " WHERE [Order Details].OrderID=?" End With ' Create unnamed Parameter and append it to Parameters collection objCmd.Parameters.Append _ objCmd.CreateParameter(,adInteger,adParamInput,4) S = Timer ' Perform ordinary select query. Use the parameterized command ' object to perform the nested sub-query. strsql = "SELECT OrderID, CompanyName, OrderDate"&_ " FROM Orders "&_ " INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID "&_ " ORDER BY OrderID" objRS.Open strsql,objCN,adOpenForwardOnly,adLockReadOnly,adCmdText response.write "
"
Do While Not objRS.EOF
	RecordsArray1 = objRS.GetRows(30)
	
	For i = 0 To UBound(RecordsArray1, 2)
		' Print order information
		Response.write "Order #"
		Response.write RecordsArray1(0, i)
		Response.write " from "
		Response.write RecordsArray1(1, i)
		Response.write " on "
		Response.write RecordsArray1(2, i)
		Response.write ""
		Response.write vbCrLf

		' Print order details
		objCmd(0) = RecordsArray1(0, i)
		objRS2.Open objCmd
		Do While Not objRS2.EOF
			RecordsArray2 = objRS2.GetRows(30)
			For j = 0 To UBound(RecordsArray2, 2)
				' Print product name and price
				Response.write vbTab
				Response.write RecordsArray2(0,j)
				Response.write " "
				Response.write FormatCurrency(RecordsArray2(1,j))
				Response.write vbCrLf
			Next
		Loop
		objRS2.Close
	Next
	
Loop
objRS.Close
response.write "
" E = Timer Response.write "

time: "&(E - S) S = Timer ' Perform UNION ALL Query strsql = _ "SELECT 1 AS RecordType, OrderID, CompanyName, CStr(OrderDate) "&_ " FROM Orders "&_ " INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID "&_ " UNION ALL "&_ " SELECT 2 AS RecordType, [Order Details].OrderID, "&_ " ProductName, CStr([Order Details].UnitPrice) "&_ " FROM ([Order Details] "&_ " INNER JOIN [Orders] ON [Order Details].OrderID=[Orders].OrderID)"&_ " INNER JOIN Products ON [Order Details].ProductID=Products.ProductID"&_ " ORDER BY OrderID, RecordType" objRS.Open strsql,objCN,adOpenForwardOnly,adLockReadOnly,adCmdText response.write "

"
Do While Not objRS.EOF
	RecordsArray1 = objRS.GetRows(30)
	
	For i = 0 To UBound(RecordsArray1, 2)
		' Print differently depending on row type
		Select Case RecordsArray1(0, i)
			Case 1
			' Print order information
			Response.write "Order #"
			Response.write RecordsArray1(1, i)
			Response.write " from "
			Response.write RecordsArray1(2, i)
			Response.write " on "
			Response.write RecordsArray1(3, i)
			Response.write ""
			Response.write vbCrLf
			Case 2
			' Print details; product name and price
			Response.write vbTab
			Response.write RecordsArray1(2,i)
			Response.write " "
			Response.write FormatCurrency(RecordsArray1(3,i))
			Response.write vbCrLf
		End Select
	Next
Loop
objRS.Close
response.write "
" E = Timer Response.write "

time: "&(E - S) objCN.Close Set objRS = Nothing Set objCN = Nothing %>