Status.asp Module Code

The Status.asp module displays the status of a customer's order by using two connections simultaneously. The main steps are:

The Connection Object: Making and opening the first connection
The Command Object: Specifying a query for the first connection
The Parameter Object: Specifying and assigning a value to the input parameter of the first command
The Command Object: Creating an active connection for the first connection
The Connection Object: Making and opening the second connection
The Command Object: Specifying a query that is a stored procedure for the second connection
The Parameter Object: Specifying the input parameter of the second command
The Recordset Object: Executing the first command that returns a Recordset
The Field Object: Extracting data from a Recordset and assigning the data to variables
The Parameter Object: Assigning a value to the input parameter of the second command
The Recordset Object: Executing the second command that returns a Recordset
The Field Object: Extracting data from a Recordset, assigning the data to variables, and displaying the data

The Connection Object: Making and opening the first connection
This application uses the following steps to define the first connection to a database by setting up a Connection object to reference the data source:
1. Set up the Connection object.
2. Return the number of seconds to wait when creating a connection before stopping the attempt and returning an error specified in the global.asa module.
3. Return the number of seconds to wait when executing a command before stopping the attempt and returning an error specified in the global.asa module.
4. Specify the data source and user name to apply when opening the connection.
5. Open the connection.
The corresponding code follows:
```
Set connOrder = Server.CreateObject("ADODB.Connection")connOrder.ConnectionTimeout = Session("accts_ConnectionTimeout")connOrder.CommandTimeout = Session("accts_CommandTimeout")
connOrder.ConnectionString = "DSN=Sample;UID=sa;"
connOrder.open
```
The Command Object: Specifying a query for the first connection
After creating and opening a connection, the application sets up a Command object by using the following steps:
1. Set up the Command object.
2. Specify the text of the command to execute.
3. Specify that the command text is text.
The corresponding code follows:
```
Set cmdOrder = Server.CreateObject("ADODB.Command")cmdOrder.CommandText = sqlTextcmdOrder.CommandType = adCmdText
```
The Parameter Object: Specifying and assigning a value to the input parameter of the first command
After creating a Command object, the application sets up a Parameter object using the following steps:
1. Set up the Parameter object collection.
2. Add the following input parameters to the collection:
  @piiCustomerID of type Integer with a size of 4
3. Specify the value to assign to the input parameter.
The corresponding code follows:
```
set p = cmdOrder.Parametersp.Append cmdOrder.CreateParameter("@piiCustomerID", adInteger, 
adParamInput, 4)cmdOrder("@piiCustomerID") = Session("CustomerID")
```
The Command Object: Creating an active connection for the first connection
The following code specifies the connection to use:
```
Set cmdOrder.ActiveConnection = connOrder
```
The Connection Object: Making and opening the second connection
This application then uses the following steps to define the second connection to the database by setting up a Connection object to reference the data source:
1. Set up the Connection object.
2. Return the number of seconds to wait when creating a connection before stopping the attempt and returning an error specified in the global.asa module.
3. Return the number of seconds to wait when executing a command before stopping the attempt and returning an error specified in the global.asa module.
4. Specify the data source and user name to apply when opening the connection.
5. Open the connection.
The corresponding code follows:
```
Set connLineItems = Server.CreateObject("ADODB.Connection")connLineItems.ConnectionTimeout = Session("accts_ConnectionTimeout")
connLineItems.CommandTimeout = Session("accts_CommandTimeout")
connLineItems.ConnectionString = "DSN=Sample;UID=sa;"
connLineItems.open
```
The Command Object: Specifying a query that is a stored procedure for the second connection
After creating and opening the second connection, the application sets up a second Command object by using the following steps:
1. Set up the Command object.
2. Specify the text of the command to execute.
3. Specify that the command text is a stored procedure.
The corresponding code follows:
```
set cmdLineItems = Server.CreateObject("ADODB.Command")cmdLineItems.CommandText = "sp_get_line_item_info"cmdLineItems.CommandType = adCmdStoredProc
```
The Parameter Object: Specifying the input parameter of the second command
After creating the second Command object, the application sets up a Parameter object by using the following steps:
1. Set up the Parameter object collection.
2. Add the following input parameters to the collection:
  @piiOrderNumber of type Integer with a size of 4
The corresponding code follows:
```
set pp = cmdLineItems.Parameterspp.Append cmdLineItems.CreateParameter("@piiOrderNumber", adInteger,
 adParamInput, 4)
```
The Recordset Object: Executing the first command that returns a Recordset
The following code executes the first query which returns the first Recordset object:
```
set rsOrder = cmdOrder.Execute
```

The Field Object: Extracting data from a Recordset and assigning the data to variables

The following code extracts the values of the Field objects of the first Recordset, and then stores the values in variables:

Do until rsOrder.EOF    htmlOrderNumber                = rsOrder("OrderNumber")    htmlShippingHandlingCharges = rsOrder("ShippingHandlingCharges")    htmlTotalOrderPrice            = rsOrder("TotalOrderPrice")
htmlSalesTax                = rsOrder("SalesTax")
rsOrder.MoveNext
%>

The Parameter Object: Assigning a value to the input parameter of the second command
The following code specifies the value to assign to the input parameters of the second command:
```
cmdLineItems("@piiOrderNumber")    = htmlOrderNumber
```
The Recordset Object: Executing the second command that returns a Recordset
The following code executes the second query and returns the second Recordset object:
```
set rs = cmdLineItems.Execute
```
The Field Object: Extracting data from a Recordset, assigning the data to variables, and displaying the data
The following code extracts the values of the Field objects of the second Recordset, stores the values in variables, and then displays them:

Do until rs.EOF    htmlLineNumber            = rs("LineNumber")    htmlProductName            = rs("ProductName")    htmlItemStatusDescription = rs("ItemStatusDescription")    htmlQuantity            = rs("Quantity")    htmlLineItemPrice        = rs("LineItemPrice")    rs.MoveNext    %>    <TR>
<TD WIDTH=50>
<TD WIDTH=75 ALIGN="right"><% = htmlLineNumber %>
<TD WIDTH=240><% = htmlProductName %>
<TD WIDTH=100><% = htmlItemStatusDescription %>
<TD WIDTH=80 ALIGN="right"><% = htmlQuantity %>
<TD WIDTH=80 ALIGN="right"><% = htmlLineItemPrice %>

Customize.asp Module Code

Sets customizable properties in the Parent Categories Stack. Uses the SHAPE Provider to get a hierarchical recordset.

The Connection Object: Making and opening the first connection that specifies the OLE DB Provider for ODBC as the data source provider
The Command Object: Specifying a query that is a stored procedure for the first connection
The Parameter Object: Specifying the input parameters of a command for the first connection
The Parameter Object: Assigning a Value to the input parameter of a command for the first connection
The Recordset Object: Creating an active connection and executing a command that returns a Recordset for the first connection
The Field Object: Extracting data from a Recordset and assigning the data to a variable
The Connection Object: Making and opening the second connection that specifies the OLE DB Provider for ODBC as the data source provider and uses a Data Shaping Provider
The Recordset Object: Create a Recordset object
Construct an SQL Query to retrieve a hierarchical recordset using the Shape Provider
The Recordset Object: Open the Connection Object and specify a query that does Data Shaping and returns a Recordset for the second connection
The Field Object: Navigate through the child Recordset, extract data from its' Fields and display it

The Connection Object: Making and opening a connection that specifies the OLE DB Provider for ODBC as the data source provider
This application first defines a connection to a database by setting up a Connection object to reference the data source. The steps taken are:
1. Set up the Connection object.
2. Return the number of seconds to wait when creating a connection before stopping the attempt and returning an error specified in the global.asa module.
3. Return the number of seconds to wait when executing a command before stopping the attempt and returning an error specified in the global.asa module.
4. Specify the data source provider to use, the data source, and user name to apply when opening the connection.
5. Open the connection
The corresponding code follows:
```
Set connCategories = Server.CreateObject("ADODB.Connection")
connCategories.ConnectionTimeout=Session("accts_ConnectionTimeout")
connCategories.CommandTimeout = Session("accts_CommandTimeout")
connCategories.ConnectionString="Data Provider=MSDASQL;DSN=Sample;
                                 UID=sa; pwd=;"
connCategories.open 
```
The Command Object: Specifying a query that is a stored procedure for the first connection
After creating and opening a connection, the application sets up a Command object by using the following steps:
1. Set up the Command object.
2. Specify the text of the command to execute.
3. Specify that the command text is a stored procedure.
The corresponding code follows:
```
Set cmdCategories = Server.CreateObject("ADODB.Command")
cmdCategories.CommandText = "sp_get_category_stack"
cmdCategories.CommandType = adCmdStoredProc
```
The Parameter Object: Specifying the input parameters of a command for the first connection
After creating a Command object, the application sets up an input Parameter object using the following steps:
1. Set up the Parameter object collection.
2. Add the following input parameter to the collection:
  @pi_ProductID of type Integer with a size of 4
The corresponding code follows:
```
set p = cmdCategories.Parameters
p.Append cmdCategories.CreateParameter("@pii_ProductID", adInteger,
      adParamInput, 4)
```
The Parameter Object: Assigning a value to the input parameter of a command for the first connection
The following code assigns a value to the input parameter from a session variable:
```
cmdCategories("@pii_ProductID") = session("ProductID")
```
The Recordset Object: Creating an active connection and executing a command that returns a Recordset for the first connection
The following code specifies the connection to use and then executes the query that returns the Recordset object:
```
Set cmdCategories.ActiveConnection = connCategories
set rsCategories = cmdCategories.Execute
```
The Field Object: Extracting data from a Recordset and assigning the data to a variable
The following code extracts the value of the Field object from the Recordset, and stores the value in a variable.
```
iCategoryID = rsCategories("wk_category")
```
The Connection Object: Making and opening a second connection that specifies the OLE DB Provider for ODBC as the data source provider and also uses a Data Shaping provider
This application now defines a second connection to a database by setting up a Connection object to reference the data source. The steps taken are:
1. Set up the Connection object.
2. Return the number of seconds to wait when creating a connection before stopping the attempt and returning an error specified in the global.asa module.
3. Return the number of seconds to wait when executing a command before stopping the attempt and returning an error specified in the global.asa module.
4. Specify the data source provider to use, the data source, and user name to apply when opening the connection.
5. Specify the provider supplying data shaping support to use.
6. Open the connection
The corresponding code follows:
```
Set connProperties = Server.CreateObject("ADODB.Connection")
connProperties.ConnectionTimeout=Session("accts_ConnectionTimeout")
connProperties.CommandTimeout = Session("accts_CommandTimeout")
connProperties.ConnectionString="Data Provider=MSDASQL; DSN=Sample; 
                                 UID=sa;pwd=;"
connProperties.Provider = "MSDataShape"
connProperties.open
```
The Recordset Object: Create a Recordset Object
The following code creates a Recordset object:
```
Set rsProperties = Server.CreateObject("ADODB.Recordset")
```
Construct an SQL Query to retrieve a hierarchical recordset using the Shape provider
The following code uses the Shape provider to define a child Recordset object as the as the value of a Field object in a parent Recordset. It relates the Recordset objects from CategoryProperty and PropertyValue by PropertyID. It creates and appends a new column (chValues) to CategoryProperty. The steps taken are:
1. Use the Shape command with a query to return the parent Recordset.
2. Use the Shape Append command with a query to return the child Recordset.
3. Specify the name of the new Recordset and use the Shape Relate clause to specify the common field in the parent and child Recordset.
The corresponding code follows:
```
strSQL =    "SHAPE {select * from CategoryProperty where CategoryID =" 
strSQL = strSQL &    iCategoryID
strSQL = strSQL &    " }"

strSQL = strSQL &    " APPEND ({select * from PropertyValue}"

strSQL = strSQL &    " AS chValues RELATE PropertyID to PropertyID)"
```
The Recordset Object: Open the Connection Object and specify a query that returns a Recordset for the second connection
The following code specifies the connection to open, query string to use, and assigns the retrieved data to the Recordset object,rsProperties:
```
rsProperties.Open strSQL, connProperties
```

The Field Object: Navigate through the child Recordset, extract data from its' Fields and display it

The following code first loops through the Parent Recordset, retrieves the appended Recordset and assigns it to rsValues, then displays three fields (PropertyID, PropertyValue, and PropertyDescription) from each row in the appended recordset.

The corresponding code follows:

do until rsProperties.EOF
iPropertyCount = iPropertyCount + 1
htmlPropertyName = rsProperties("PropertyName")
%>
<TD><PRE>  </PRE>
<TD><H5><% = htmlPropertyName %></H5>
<TD VALIGN="top">
<%    
%>
<SELECT NAME="cboPropertyString<% = iPropertyCount %>">
<%
rsValues = rsProperties("chValues")

while not rsValues.EOF
%>
<OPTION VALUE="<%=(rsValues("PropertyID") & "&#9;" & 
         htmlPropertyName & "&#9;" & rsValues("PropertyValue")) %>">
<% = rsValues("PropertyDescription") %>
<BR>
<%
rsValues.MoveNext
wend
%>
</SELECT>
<INPUT TYPE="hidden" NAME=iPropertyID<% = iPropertyCount %>     VALUE="">
<INPUT TYPE="hidden" NAME=iPropertyName<% = iPropertyCount %>     VALUE="">
<INPUT TYPE="hidden" NAME=iPropertyValue<% = iPropertyCount %>     VALUE="">
<%
rsProperties.MoveNext
Loop
rsProperties.close
rsCategories.MoveNext
Loop
rsCategories.close