setOrders.asp Module Code

The setOrders.asp module adds shopping cart contents to an order table by executing several commands in stored procedures over one connection that uses input and output parameters. The main steps are:

The Connection Object: Making and opening a connection
The Command Object: Specifying a query that is a stored procedure
The Parameter Object: Specifying the input and output parameters of a command
The Parameter Object: Assigning values to the input parameters of a command
The Command Object: Creating an active connection and executing a command
The Parameter Object: Assigning a value from the output parameter of a command to a session variable
Clearing the Command Object
Clearing the Parameter Object
The Command Object: Specifying a new query that is a stored procedure
The Parameter Object: Specifying the input parameters of a command
Assigning the Values of Session Variables to Temporary Variables
The Parameter Object: Assigning values to the input parameters of a command
Constructing an SQL Query from Session Variables
Executing a Command with the Connection Object
The Command Object: Creating an active connection and executing a command

The Connection Object: Making and opening a connection
This application first uses the following steps to define a 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 connPubs = Server.CreateObject("ADODB.Connection")
connPubs.ConnectionTimeout = Session("accts_ConnectionTimeout")
connPubs.CommandTimeout = Session("accts_CommandTimeout")
connPubs.ConnectionString = "DSN=Sample;UID=sa;"
connPubs.open
```
The Command Object: Specifying a query that is a stored procedure
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 cmdPubs = Server.CreateObject("ADODB.Command")cmdPubs.CommandText = " sp_add_orders_header"
cmdPubs.CommandType = adCmdStoredProc
```
The Parameter Object: Specifying the input and output parameters of a command
After creating a 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:
  @piiCustomerID of type Integer with a size of 4
  @picTotalOrderPrice of type Currency with a size of 8
  @picShippingCharges of type Currency with a size of 8
  @picSalesTax of type Currency with a size of 8
3. Add the following output parameter to the collection:
  @poiNextOrderID of type Integer with a size of 4
The corresponding code follows:
```
set p = cmdPubs.Parameters

p.Append cmdPubs.CreateParameter("@piiCustomerID",        adInteger, 
adParamInput, 4)p.Append cmdPubs.CreateParameter("@picTotalOrderPrice", adCurrency, 
adParamInput, 8)
p.Append cmdPubs.CreateParameter("@picShippingCharges", adCurrency, 
adParamInput, 8)p.Append cmdPubs.CreateParameter("@picSalesTax",        adCurrency, 
adParamInput, 8)
p.Append cmdPubs.CreateParameter("@poiNextOrderID",    adInteger, 
adParamOutput, 4)
```

The Parameter Object: Assigning values to the input parameters of a command

The following code specifies the values to assign to the input parameters:

cmdPubs("@piiCustomerID")        = Session("CustomerID")cmdPubs("@picTotalOrderPrice")    = Session("TotalOrderPrice")
cmdPubs("@picShippingCharges")    = Session("ShippingCharges")
cmdPubs("@picSalesTax")            = Session("SalesTax")

The Command Object: Creating an active connection and executing a command
The following code specifies the connection to use and then executes a command:
```
Set cmdPubs.ActiveConnection = connPubs
cmdPubs.Execute
```
The Parameter Object: Assigning a value from the output parameter of a command to a session variable
The following code assigns the values of the output parameters to session variables:
```
Session("OrderID") = cmdPubs("@poiNextOrderID")
```
Clearing the Command Object
The following code clears the contents of the Command object:
```
Set cmdPubs = Nothing
```
Clearing the Parameter Object
The following code clears the contents of the Parameter object:
```
Set p = Nothing
```
The Command Object: Specifying a new query that is a stored procedure
Resets the Command object by using the following steps:
1. Specify the text of the command to execute.
2. Specify that the command text is a stored procedure.
The corresponding code follows:
```
cmdPubs.CommandText = "sp_add_order_line_item"cmdPubs.CommandType = adCmdStoredProc
```
The Parameter Object: Specifying the input parameters of a command
After creating a 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
  @piiLineNumber of type Integer with a size of 4
  @piiProductID of type Integer with a size of 4
  @piiQuantity of type Integer with a size of 4
  @picPrice of type Currency with a size of 4
The corresponding code follows:
```
set pp = cmdPubs.Parameterspp.Append cmdPubs.CreateParameter("@piiOrderNumber", adInteger, 
adParamInput, 4)pp.Append cmdPubs.CreateParameter("@piiLineNumber", adInteger, 
adParamInput, 4)pp.Append cmdPubs.CreateParameter("@piiProductID", adInteger, 
adParamInput, 4)pp.Append cmdPubs.CreateParameter("@piiQuantity", adInteger, 
adParamInput, 4)pp.Append cmdPubs.CreateParameter("@picPrice", adCurrency, 
adParamInput, 8)
```

Assigning the Values of Session Variables to Temporary Variables

The following code assigns the values of session variables to temporary variables:

aiSKProductIDs = session("aiSKProductIDs")aiSKCategoryIDs = session("aiSKCategoryIDs")
aiSKProductQuantitys = session("aiSKProductQuantitys")
aiSKProductPrices = session("aiSKProductPrices")
acSKProductNames = session("acSKProductNames")
acSKProductDescriptions = session("acSKProductDescriptions")

The Parameter Object: Assigning values to the input parameters of a command

The following code specifies the values to assign to the input parameters:

cmdPubs("@piiOrderNumber") = Session("OrderID")cmdPubs("@piiLineNumber") = icmdPubs("@piiProductID") = aiSKProductIDs(i)
cmdPubs("@piiQuantity") = 1
cmdPubs("@picPrice") = aiSKProductPrices(i)

Constructing an SQL Query from Session Variables

The following code constructs an SQL query:

SQL = "insert OrderLineItem "SQL = SQL & "values (" SQL = SQL & Session("OrderID") & ", " 
SQL = SQL & i & ", " 
SQL = SQL & aiSKProductIDs(i) & ", 0, " 
SQL = SQL & aiSKProductQuantitys(i) & ", 0, "
SQL = SQL & "null, "
SQL = SQL & "null, "
SQL = SQL & aiSKProductPrices(i)
SQL = SQL & ")"

Executing a Command with the Connection Object
The following code executes a command directly through the Connection object:
```
connPubs.Execute(SQL)
```
The Command Object: Creating an active connection and executing a command
The following code specifies the connection to use and then executes a command:
```
Set cmdPubs.ActiveConnection = connPubs
cmdPubs.Execute
```