<%@LANGUAGE="VBScript"%>
<%Option Explicit%>
<%
'-----------------------------------------------------------
'--- Declare all variables to be used
'-----------------------------------------------------------
dim objCustomerRS '--- The grandparent recordset (customers)
dim objOrderRS '--- The parent recordset (orders)
dim objLineItemRS '--- The child recordset (line items)
dim objConn '--- The connection object that connects to the database
dim objField '--- Used to represent a Field object as we loop through the fields
dim strSQL '--- The SQL that shapes our customers, orders and line items
dim strConn '--- The connection string to open our connection with
dim lngCol '--- Used to loop through the columns in a recordset
dim lngCustomerRow '--- The row for a customer
dim lngOrderRow '--- The row for an order
'-----------------------------------------------------------
'--- Declare all ADO 2.0 constants to be used
'-----------------------------------------------------------
const adChapter = 136
const adUseClient = 3
const adDouble = 5
const adCurrency = 6
Figure 7 Creating an ADO Connection
'--- Create the ADO Connection object.
set objConn = Server.CreateObject("ADODB.Connection")
'--- Generate the connection string using the
'--- OLE DB provider Access and the Data Shaping provider.
strConn = "Data Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;"
strConn = strConn & "Data Source=C:\InetPub\AdvWorks.mdb"
objConn.ConnectionString = strConn
'--- Specify that we will use the Data Shaping provider.
objConn.Provider = "MSDataShape"
'--- Open the connection
objConn.Open
Figure 8 The Shaped SQL
'-----------------------------------------------------------
'--- Generate the SQL to share customers,
'--- their orders and their line items.
'-----------------------------------------------------------
strSQL = " SHAPE ("
strSQL = strSQL & " SHAPE"
strSQL = strSQL & " {"
strSQL = strSQL & " SELECT CustomerID AS [Cust #],"
strSQL = strSQL & " CompanyName AS Customer"
strSQL = strSQL & " FROM Customers"
strSQL = strSQL & " }"
strSQL = strSQL & " APPEND"
strSQL = strSQL & " ("
strSQL = strSQL & " ("
strSQL = strSQL & " SHAPE"
strSQL = strSQL & " {"
strSQL = strSQL & " SELECT OrderID AS [Order #],"
strSQL = strSQL & " OrderDate AS [Order Date],"
strSQL = strSQL & " PurchaseOrderNumber AS [PO #],"
strSQL = strSQL & " Orders.CustomerID AS [Cust #]"
strSQL = strSQL & " FROM Orders"
strSQL = strSQL & " ORDER BY OrderDate DESC"
strSQL = strSQL & " }"
strSQL = strSQL & " APPEND"
strSQL = strSQL & " ("
strSQL = strSQL & " {"
strSQL = strSQL & " SELECT od.OrderID AS [Order #],"
strSQL = strSQL & " p.ProductType AS [Product],"
strSQL = strSQL & " p.ProductName AS [Manufacturer],"
strSQL = strSQL & " od.Quantity,"
strSQL = strSQL & " od.UnitPrice AS [Unit Price],"
strSQL = strSQL & " (od.UnitPrice * od.Quantity)"
strSQL = strSQL & " AS [Extended Price]"
strSQL = strSQL & " FROM Order_Details od INNER JOIN Products p"
strSQL = strSQL & " ON od.ProductID = p.ProductID"
strSQL = strSQL & " ORDER BY p.ProductType, p.ProductName"
strSQL = strSQL & " }"
strSQL = strSQL & " RELATE [Order #] TO [Order #]"
strSQL = strSQL & " ) AS rsLineItems,"
strSQL = strSQL & " COUNT(rsLineItems.Product) AS [Items On Order],"
strSQL = strSQL & " SUM(rsLineItems.[Extended Price])"
strSQL = strSQL & " AS [Order Total]"
strSQL = strSQL & " ) RELATE [Cust #] TO [Cust #]"
strSQL = strSQL & " ) AS rsOrders,"
strSQL = strSQL & " SUM(rsOrders.[Order Total]) AS [Total Amount Owed]"
strSQL = strSQL & " ) AS rsCustomers"
'--- Create and open the topmost parent recordset (Customers)
set objCustomerRS = server.CreateObject("ADODB.Recordset")
objCustomerRS.Open strSQL, objConn
Figure 9 Valid Expressions
|
Expression |
Description |
|
SUM |
Calculates the sum of the given child field
|
|
AVG |
Calculates the average of the given child field |
|
MIN |
Calculates the minimum value of the given
child field |
|
MAX |
Calculates the maximum value of the given
child field |
|
COUNT |
Calculates the total number of records in the child recordset |
|
STDEV |
Calculates the standard deviation of the given child field |
|
CALC |
Calculates a user-defined expression |
|
ANY |
The value of a field from the child recordset (may be unpredictable)
|
Figure 10 Formatting Functions
Function FormatValue(objField)
'--- Format the values
If IsCurrency(objField.Type) then
FormatValue = FormatCurrency(objField.Value, 2)
Else
FormatValue = objField.Value
End If
End Function
Function IsCurrency(intType)
'--- Determine if the datatype needs decimals,
'--- we'll call it currency for this page.
select case intType
case adDouble, adCurrency
IsCurrency = True
case else
IsCurrency = False
end select
End Function
%>
Figure 11 Styles and Style Sheets
<html>
<head>
<style>
<!--
FONT
{
FONT-FAMILY: Arial, Verdana
}
.Point
{
CURSOR: hand
}
.DontPoint
{
CURSOR: default
}
//-->
</style>
Figure 12 Hiding and Showing
<script LANGUAGE="vbscript">
<!--
sub Point(strImage)
'--- Make the cursor a hand for the item passed in.
window.document.all(strImage).className = "Point"
end sub
sub DontPoint(strImage)
'--- Make the cursor normal for the item passed in.
window.document.all(strImage).className = "DontPoint"
end sub
sub DisplayItem(strImage, strDiv)
dim strDisplay
strDisplay = window.document.all(strDiv).style.display
'--- Check if the DIV is already displayed
if strDisplay = "none" then
'--- We need to display the DIV
strDisplay = ""
'--- And we need to change the image to the MINUS
window.document.all(strImage).src = "Minus.gif"
else
'--- We need to hide the DIV
strDisplay = "none"
'--- And we need to change the image to the PLUS
window.document.all(strImage).src = "Plus.gif"
end if
window.document.all(strDiv).style.display = strDisplay
end sub
-->
</script>
</head>
Figure 13 Displaying the Hierarchical Recordsets
<body>
<table border="1" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td>
<!-- Begin Customers table. -->
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<!--------------------------------------------->
<!-- Display the Customer's column headings. -->
<!--------------------------------------------->
<tr>
<td width="5%" bgcolor="#ffffff"> </td>
<td width="1%" bgcolor="#000080"> </td>
<%for lngCol = 0 to objCustomerRS.Fields.Count - 1%>
<%if objCustomerRS.Fields(lngCol).Type <> adChapter then%>
<td align="right" bgcolor="#000080" nowrap>
<font size="3" color="#ffffff">
<b><%=objCustomerRS.Fields(lngCol).Name%></b>
</font>
</td>
<%end if%>
<%next%>
</tr>
<!--------------------------------------->
<!-- Display the Customer's data rows. -->
<!--------------------------------------->
<%lngCustomerRow = 0%>
<%do while not objCustomerRS.EOF%>
<%lngCustomerRow = lngCustomerRow + 1%>
<tr bgcolor="#ffffff">
<td bgcolor="#ffffff" align="center" valign="center" width="5%">
<img name="imgCustomer<%=lngCustomerRow%>"
SRC="Plus.gif" ALT="View this customer's orders"
onclick="DisplayItem 'imgCustomer<%=lngCustomerRow%>',
'divCustomer<%=lngCustomerRow%>'"
onmouseover="Point('imgCustomer<%=lngCustomerRow%>')"
onmouseout="DontPoint('imgCustomer<%=lngCustomerRow%>')"
WIDTH="16" HEIGHT="16">
</td>
<td width="1%" bgcolor="#000080"> </td>
<%for lngCol = 0 to objCustomerRS.Fields.Count - 1%>
<%set objField = objCustomerRS.Fields(lngCol)%>
<%if objField.Type <> adChapter then%>
<td align="right" bgcolor="#ffffc0" nowrap>
<font size="3" color="#000080">
<%=FormatValue(objField)%>
</font>
</td>
<%end if%>
<%next%>
</tr>
<!------------------------->
<!-- Begin Orders table. -->
<!------------------------->
<tr>
<td align="center" colspan="<%=objCustomerRS.Fields.Count + 1%>">
<div id="divCustomer<%=lngCustomerRow%>" style="display:none">
<%set objOrderRS = objCustomerRS.Fields("rsOrders").Value
if objOrderRS.EOF then%>
<font size="2" color="#ffffff"><b>This customer has no orders.</b></font>
<%else%>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<!------------------------------------------>
<!-- Display the Order's column headings. -->
<!------------------------------------------>
<tr>
<td width="5%" bgcolor="#ffffff"> </td>
<td width="5%" bgcolor="#ffffff"> </td>
<td width="1%" bgcolor="#000080"> </td>
<%for lngCol = 0 to objOrderRS.Fields.Count - 1%>
<%if objOrderRS.Fields(lngCol).Type <> adChapter then%>
<td align="right" bgcolor="#000080" nowrap>
<font size="2" color="#ffffff">
<b><%=objOrderRS.Fields(lngCol).Name%></b>
</font>
</td>
<%end if%>
<%next%>
</tr>
<!------------------------------------>
<!-- Display the Order's data rows. -->
<!------------------------------------>
<%do while not objOrderRS.EOF%>
<%lngOrderRow = lngOrderRow + 1%>
<tr>
<td bgcolor="#ffffff" align="center"> </td>
<td bgcolor="#ffffff" align="center"
valign="center" width="5%">
<img name="imgOrder<%=lngOrderRow%>"
SRC="Plus.gif" ALT="View this order's line items"
onclick="DisplayItem 'imgOrder<%=lngOrderRow%>',
'divOrder<%=lngOrderRow%>'"
onmouseover="Point('imgOrder<%=lngOrderRow%>')"
onmouseout="DontPoint('imgOrder<%=lngOrderRow%>')"
WIDTH="16" HEIGHT="16">
</td>
<td width="1%" bgcolor="#000080"> </td>
<%for lngCol = 0 to objOrderRS.Fields.Count - 1%>
<%set objField = objOrderRS.Fields(lngCol)%>
<%if objField.Type <> adChapter then%>
<td align="right" bgcolor="#ccccff">
<font size="2" color="#000080">
<%=FormatValue(objField)%>
</font>
</td>
<%end if%>
<%next%>
</tr>
<!-------------------------------->
<!-- Begin line items table. -->
<!-------------------------------->
<tr>
<td align="center" colspan="<%=objOrderRS.Fields.Count + 2%>">
<div id="divOrder<%=lngOrderRow%>" style="display:none">
<%set objLineItemRS = objOrderRS.Fields("rsLineItems").Value
if objLineItemRS.EOF then%>
<font size="2" color="#ffffff">
<b>No line items exist.</b>
</font>
<%else%>
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<!------------------------------------------------->
<!-- Display the Line Item's column headings. -->
<!------------------------------------------------->
<tr>
<td width="10%" bgcolor="#ffffff"
valign="center" align="center"> </td>
<td width="5%" bgcolor="#ffffff"
valign="center" align="right"> </td>
<td width="1%" bgcolor="#000080"
valign="center" align="center"> </td>
<%for lngCol = 0 to objLineItemRS.Fields.Count - 1%>
<%if objLineItemRS.Fields(lngCol).Type <>adChapter then%>
<td align="right" bgcolor="#000080" nowrap>
<font size="1" color="#ffffff">
<b><%=objLineItemRS.Fields(lngCol).Name%></b>
</font>
</td>
<%end if%>
<%next%>
</tr>
<!------------------------------------------->
<!-- Display the Line Item's data rows. -->
<!------------------------------------------->
<%do while not objLineItemRS.EOF%>
<tr>
<td width="10%" bgcolor="#ffffff"
valign="center" align="center"> </td>
<td width="5%" bgcolor="#ffffff"
valign="center" align="right"> </td>
<td width="1%" bgcolor="#000080"
valign="center" align="center"> </td>
<%for lngCol = 0 to objLineItemRS.Fields.Count - 1%>
<%set objField = objLineItemRS.Fields(lngCol)%>
<%if objField.Type <> adChapter then%>
<td align="right" bgcolor=#b6dd9a>
<font size="1" color="#000080">
<%=FormatValue(objField)%>
</font>
</td>
<%end if%>
<%next%>
</tr>
<%objLineItemRS.MoveNext
Loop%>
</table>
<%end if%>
</div>
</td>
</tr>
<%objOrderRS.MoveNext
Loop%>
</table>
<%end if%>
</div>
</td>
</tr>
<%objCustomerRS.MoveNext
Loop%>
</table>
</td></tr>
</table>
</body>
</html>
<%
objLineItemRS.Close
objOrderRS.Close
objCustomerRS.Close
objConn.Close
%>