|
Building our Order Processing Page
Page
17 of 22 - Chapter 14
OK,
now let's make a start on creating ProcessOrder.asp
At
the very top of the page are two pre-processor directives, the first
stating that this page requires a new transaction, the second making the
default language JScript. We need to explicitly state we want this to be a
transaction-based page by using the TRANSACTION pre-processor directive.
| <%@ TRANSACTION=Requires_New
Language="JScript" %> |
Next
we define some page level variables and set the page to buffer which
means no response will be sent to the client browser until we reach the
end of the page or we explicitly say so. This allows us to redirect the
response in mid flow.
Event
code has been written for OnTransactionAbort and OnTransactionCommit.
OnTransactionAbort checks the error number and if it's not 547
indicating an error of insufficient stock then the response is
redirected to the transerror.asp page which lists the error cause.
OnTransactionCommit clears the basket cookie, then redirects to the
acceptOrder.asp page which displays a success message and e-mails the
customer and the shipping department who actually sends the goods.
<%
Response.Buffer = true;
// Database connection string
var sdbConnString =
"Provider=SQLOLEDB.1;Password=madforit; _
Persist Security Info=True;User ID=MMCustomer; _
Initial
Catalog=MusicMad;Data Source=.";
var iErrorNo = 0;
var sErrorDescription;
// Index of first Item element in form
var iItemElementStart = 1;
// ADO constants make code more readable
var adCmdStoredProc = 4;
var adParamReturnValue = 4;
var adParamInput = 1;
var adParamOutput = 2;
var adVarChar = 200;
var adInteger = 3;
function OnTransactionAbort()
{
// If error is not insufficent stock
if (iErrorNo != 547)
{
Response.Redirect("transerror.asp?" +
escape(sErrorDescription));
}
}
function OnTransactionCommit()
{
// Everything went ok - re-direct to confirmation
page
Response.Cookies("Basket")= "";
Response.Redirect("acceptorder.asp?" +
iOrderId);
} |
Now
we open up a connection to the database and create a command object
which we will use to execute our stored procedure NewOrder. NewOrder
will add the customer and credit card details to the database.
Note
the whole of the transaction code is inside a JScript 5.0 try
catch
clause. If any other non-database errors occur we can catch them and
ensure the transaction is aborted.
// Process order
form
try
{
var loConn =
Server.CreateObject("ADODB.Connection");
var loCommand =
Server.CreateObject("ADODB.Command");
var loParam;
var iCustId = -1;
var iOrderId = -1;
loConn.Open(sdbConnString);
// Create ADO command object which will execute our
stored procedure
loCommand.CommandText = "NewOrder";
loCommand.CommandType = adCmdStoredProc;
loCommand.Name = "NewOrder"; |
Before
we can execute the ADO Command we need to append the parameters the
stored procedure expects and this is the task of the next piece of code.
There
are a lot of parameters to append but the method is the same for each.
-
First
a new parameter object is created using the ADO command object's
CreateParameter method whose arguments are parameter name, parameter
type, direction, size and default value.
-
Then
the new parameter is appended to the Command object using the Append
method.
The
first parameter we define is the return value which is an integer. If a
return value is expected then it must always be the first parameter
appended. Here the return value is used to notify us of any errors that
occurred inside the stored procedure. The remaining parameters except for
the last two are input parameters used to pass values to the stored
procedure. The final two parameters are output parameters which the stored
procedure populates with the CustId of the customer row added to the
database and OrderId of the order.
// @Title, @FirstName
varchar(50), @LastName varchar(50),
// @Email varchar(75), @Street varchar(75)
loParam =
loCommand.CreateParameter("RV",adInteger, _
adParamReturnValue);
loCommand.Parameters.Append(loParam);
loParam =
loCommand.CreateParameter("Title",adVarChar,adParamInput,
_
4, Request.Form("txtTitle"));
loCommand.Parameters.Append(loParam);
loParam = loCommand.CreateParameter("FirstName",
adVarChar, _
adParamInput, 50, _
Request.Form("txtFirstName"));
loCommand.Parameters.Append(loParam);
loParam = loCommand.CreateParameter("LastName",
adVarChar, _
adParamInput, 50, _
Request.Form("txtLastName"));
loCommand.Parameters.Append(loParam);
loParam = loCommand.CreateParameter("Email",
adVarChar, adParamInput,_
75, Request.Form("txtEmail"));
loCommand.Parameters.Append(loParam);
loParam = loCommand.CreateParameter("Street",
adVarChar, adParamInput,
75, Request.Form("txtStreet"));
loCommand.Parameters.Append(loParam);
//@City varchar(50), @Locality varchar(50),
@Country varchar(50)
loParam = loCommand.CreateParameter("City",
adVarChar, adParamInput, _
50, Request.Form("txtCity"));
loCommand.Parameters.Append(loParam);
loParam = loCommand.CreateParameter("Locality",
adVarChar, _
adParamInput, 50, _
Request.Form("txtLocality"));
loCommand.Parameters.Append(loParam);
loParam = loCommand.CreateParameter("PostCode",
adVarChar, _
adParamInput, 15, _
Request.Form("txtPostCode"));
loCommand.Parameters.Append(loParam);
loParam = loCommand.CreateParameter("Country",
adVarChar, _
adParamInput, 50, _
Request.Form("txtCountry"));
loCommand.Parameters.Append(loParam);
// @CCHolderName varchar(50), @CCType varchar(25),
// @CCNo varchar(20), @CCExpire varchar(7)
loParam =
loCommand.CreateParameter("CCHolderName", adVarChar, _
adParamInput, 50, _
Request.Form("txtCCHolderName"));
loCommand.Parameters.Append(loParam);
loParam = loCommand.CreateParameter("CCType",
adVarChar, adParamInput,
25, Request.Form("txtCCType"));
loCommand.Parameters.Append(loParam);
loParam = loCommand.CreateParameter("CCNo",
adVarChar, adParamInput, _
20, Request.Form("txtCCNo"));
loCommand.Parameters.Append(loParam);
loParam = loCommand.CreateParameter("CCExpire",
adVarChar, _
adParamInput, 7, _
Request.Form("txtCCExpire"));
loCommand.Parameters.Append(loParam);
// @CustId int, @OrderId int
loParam = loCommand.CreateParameter("CustId",
adInteger, _
adParamOutput);
loCommand.Parameters.Append(loParam);
loParam = loCommand.CreateParameter("OrderId",
adInteger, _
adParamOutput);
loCommand.Parameters.Append(loParam); |
The Command's
connection is set to the ADO Connection we opened and the Command is
executed.
|
loCommand.ActiveConnection =
loConn;
loCommand.Execute();
|
If
there were any problems with the stored procedure's execution, its return
value will not be zero and we need to abort this transaction. Calling
ObjectContext.SetAbort will abort the transaction and cause
OnTransactionAbort to fire and run any 'clean-up code' we put there. In
our case this is where we redirect the user to the transerror.asp page.
|
