Building our Order Processing Page
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.
If all has gone without error then we can start processing the order items. We create the command object we will use to execute the AddOrderItem stored procedure then we append the parameters.
// check if stored procedure executed ok // abort transaction if failed if (loCommand.Parameters("RV") != 0) { // get Stored Procs return VALUE iErrorNo = loCommand.Parameters("RV"); ObjectContext.SetAbort; } else { // Retrieve CustId and OrderId from // stored procs output variables iCustId = loCommand.Parameters("CustId"); iOrderId = loCommand.Parameters("OrderId"); // Create new command object // to add each order item detail to database loCommand = null; loCommand = Server.CreateObject("ADODB.Command"); loCommand.CommandText = "AddOrderItem"; loCommand.CommandType = adCmdStoredProc; loCommand.Name = "AddOrderItem"; //Append Parameters @OrderId int, @ItemId int, @Qty int loParam = loCommand.CreateParameter("RV", adInteger, _ adParamReturnValue); loCommand.Parameters.Append(loParam); loParam = loCommand.CreateParameter("OrderId", adInteger, _ adParamInput,0,iOrderId); loCommand.Parameters.Append(loParam); loParam = loCommand.CreateParameter("ItemId", adInteger, _ adParamInput); loCommand.Parameters.Append(loParam); loParam = loCommand.CreateParameter("Qty", adInteger, _ adParamInput); loCommand.Parameters.Append(loParam); loCommand.ActiveConnection = loConn;
Having created our Command object we can reuse it as we loop
through the form elements containing the ItemIds and quantities making up the
order. After each item has been added using the stored procedure we check that
the return value is zero indicating no errors. If there were errors then we call
ObjectContext.SetAbort and break out of the loop. Calling SetAbort will roll
back all the order items already added to the database as well as the customer
and order details added to the database. We are therefore assured that the database
will not become corrupted by half completed transactions.
Remember that a return value of 547 indicates an error due to insufficient stock
and we will write the code to handle that soon. If it's any other error then
the OnTransactionAbort event, which fires as a result of calling SetAbort, will
redirect the response to transerror.asp - a page we have yet to create.
Assuming that all has gone well, we call SetComplete to indicate the transaction's
success and to ensure the data gets committed to the database. The OnTransactionCommit
event fires and the customer is directed to a page confirming the transactions
success and e-mails both them and the order shipping department.
var sElementKey; var iItemId; var iQty; // Loop through the item form elements for (var iElementCounter = iItemElementStart; _ iElementCounter <= Request.Form.Count; iElementCounter++) { sElementKey = new String(Request.Form.Key(iElementCounter)); // if element NAME starts with ID its an item form element if (sElementKey.substr(3,2) == "ID") { // Get ItemId from form VALUE passed iItemId = parseInt _ (sElementKey.substring(5,sElementKey.length)); // move to next element which is quantity VALUE for that item iElementCounter++; // get quantity iQty = parseInt(Request.Form(iElementCounter)); // set command's parameters loCommand.Parameters("ItemId") = iItemId; loCommand.Parameters("Qty") = iQty; // execute stored procedure loCommand.Execute(); // Any errors? if (loCommand.Parameters("RV") != 0) { // set iErrorNo, abort transaction and break out of loop iErrorNo = loCommand.Parameters("RV"); ObjectContext.SetAbort; break; } } } } // only set complete if transaction complete with no errors if (iErrorNo == 0) ObjectContext.SetComplete; }
Finally we come to the end of our order processing script and the catch part of the try statement we set at the beginning of the code. If an error occurs in our code, the statements here execute and we are able to store the description in sErrorDescription and abort the transaction which will lead to the page being redirected to transerror.asp by OnTransactionAbort.
catch(e) { sErrorDescription = e; ObjectContext.SetAbort; } %>
Save the page as ProcessOrder.asp.
Roll with it - Degrading Gracefully When It All Goes Wrong
We have not finished with the ProcessOrder.asp page yet: we need to add some
code to handle the out of stock error (547) in a more user friendly way then
just redirecting to an error page.
The out of stock problem is dealt with by displaying a list of the order items
not available in sufficient quantity to fulfill the order and letting the user
choose to either cancel the order completely or proceed with the amounts available.
If the order was successful, or any error other than out of stock occurred, then
the user will be redirected to another page elsewhere and will never see the
HTML we are about to create.
The ASP page will be processed in totality even if re-direction occurs, so, to
avoid the overhead of the order failed code, we nest it inside an if statement.
Place the code at the bottom of the page immediately following the code we just
created.
<HTML> <BODY> <% // If error is Out Of Stock then create HTML if (iErrorNo == 547) { %>
The next task is to create a form replicating the form elements originally passed when the customer submitted their order – the last thing they want to do is be retyping the information. The form action returns to this page for reprocessing, this time hopefully with sufficient stock (though it is possible others may place orders whilst this customer is thinking about what to do).
<H2>Sorry we are unable to fully satisfy your order</H2> <P> <FORM ACTION="ProcessOrder.asp" method="POST"> We do not currently have sufficent stock for some items in your order, details listed below<BR> <!-- Name/Address Details --> <INPUT TYPE="HIDDEN" NAME="txtTitle" VALUE="<%=Request.Form("txtTitle")%>"> <INPUT TYPE="HIDDEN" NAME="txtFirstName" VALUE="<%=Request.Form("txtFirstName")%>"> <INPUT TYPE="HIDDEN" NAME="txtLastName" VALUE="<%=Request.Form("txtLastName")%>"> <INPUT TYPE="HIDDEN" NAME="txtEmail" VALUE="<%=Request.Form("txtEmail")%>"> <INPUT TYPE="HIDDEN" NAME="txtStreet" VALUE="<%=Request.Form("txtStreet")%>"> <INPUT TYPE="HIDDEN" NAME="txtCity" VALUE="<%=Request.Form("txtCity")%>"> <INPUT TYPE="HIDDEN" NAME="txtLocality" VALUE="<%=Request.Form("txtLocality")%>"> <INPUT TYPE="HIDDEN" NAME="txtPostCode" VALUE="<%=Request.Form("txtPostCode")%>"> <INPUT TYPE="HIDDEN" NAME="txtCountry" VALUE="<%=Request.Form("txtCountry")%>"> <!-- Credit Card Details --> <INPUT TYPE="HIDDEN" NAME="txtCCHolderName" VALUE="<%= Request.Form("txtCCHolderName") %>"> <INPUT TYPE="HIDDEN" NAME="txtCCNo" VALUE="<%= Request.Form("txtCCNo") %>"> <INPUT TYPE="HIDDEN" NAME="txtCCType" VALUE="<%= Request.Form("txtCCType") %>"> <INPUT TYPE="HIDDEN" NAME="txtCCExpire" VALUE="<%= Request.Form("txtCCExpire") %>">
We need to create a list of the items the customer ordered and check the availability of each item. In order to do so, we use the ItemAvailability stored procedure to retrieve the quantities available. Then we create a new command object which will be reused to execute the stored procedure as we loop though the shopping basket's items.
<% var loRS; // Create new command object loCommand = null; loCommand = Server.CreateObject("ADODB.Command"); loCommand.CommandText = "ItemAvailability"; loCommand.CommandType = adCmdStoredProc; loCommand.Name = "ItemAvailability"; //@ItemId int loParam =loCommand.CreateParameter("ItemId", adInteger, adParamInput); loCommand.Parameters.Append(loParam); loCommand.ActiveConnection = loConn;
Next we start looping though the item elements in the form, retrieving the ItemId and quantity and using this information to access the database with the Command object and find out how many we actually have in stock.
var sElementKey; var iItemId; var iQty; var iQtyInStock; for (var iElementCounter = iItemElementStart; _ iElementCounter <= Request.Form.Count; iElementCounter++) { sElementKey = new String(Request.Form.Key(iElementCounter)); // If this is an item element if (sElementKey.substr(3,2) == "ID") { // get ItemId iItemId = parseInt(sElementKey.substring(5,sElementKey.length)); iElementCounter++; // Get desired quantity iQty = parseInt(Request.Form(iElementCounter)); // access database to see how many are actually available loCommand.Parameters("ItemId") = iItemId; loRS = loCommand.Execute(); iQtyInStock = loRS("QtyInStock");
If we find that there is insufficient stock available for
the item we need to let the customer know. If there is no stock at all then we
tell the user, otherwise we tell them how many are actually available. We also
create hidden form elements to store the ItemId and quantities actually available.
If the user goes ahead with the quantities available then this information can
be retrieved after the form post.
We are only informing the user of problem items so if there are sufficient available we just create the hidden form elements for ItemId and quantity. The loop continues though each item.
// If insufficent stock if (iQtyInStock < iQty) { // no stock at all - so no able to provide any of this item if (iQtyInStock == 0) { %> <P><STRONG> <%= loRS("Title") %> by <%= loRS("ArtistName") %> is currently unavailable, we hope to have new stock in shortly </STRONG></P> <% } else // some stock available but not enough { %> <P> <STRONG> You requested <%= iQty %> copies of <%= loRS("Title") %> by <%= loRS("ArtistName") %>, but unfortunately we only have <%= iQtyInStock %> in stock </STRONG> <INPUT TYPE="Hidden" NAME="<%= "txtID" + iItemId %>" VALUE="<%= "ID" + iItemId %>"> <INPUT TYPE="Hidden" NAME="<%= "txtQtyID" + iItemId %>" VALUE="<%= iQtyInStock %>"> </P> <% } } else // sufficent stock - add hidden elements to form { %> <INPUT TYPE="Hidden" NAME="<%= "txtID" + iItemId %>" VALUE="<%= "ID" + iItemId %>"> <INPUT TYPE="Hidden" NAME="<%= "txtQtyID" + iItemId %>" VALUE="<%= iQtyInStock %>"> <% } } } %>
Finally we add two buttons giving the customer the choice of continuing with the available amounts or canceling altogether.
<P> Click <INPUT TYPE="Submit" NAME="cmdSubmit" VALUE="Process Order"> to submit your order with the maximum amount amounts available. </P> <P> Click <INPUT TYPE="button" NAME="cmdCancel" VALUE="Cancel Order" onClick="window.location.replace('ordercancel.htm');"> to end this transaction, no monies have been debited from your credit card. </P> </FORM> <% } // close database connection loConn.Close(); loConn = null; %> </BODY> </HTML>
We have completed this page so re-save the file before closing
it and continuing.
The OrderCancel and TransactionError Pages
Let's take the ordercancel.htm page first. Here we inform the user that no money
has been debited from their card and give them the option to continue shopping.
<HTML> <BODY> <DIV ALIGN="center"> <FONT FACE="Comic Sans MS" SIZE="4"> Your order has been cancelled successfully.<br> No monies will be deducted from your credit card </FONT> <FORM ACTION="" method="POST"> <INPUT TYPE="button" NAME="cmdMainPage" VALUE="Continue shopping" onClick="top.location.replace('musicmad.htm')"> </FORM> </DIV> </BODY> </HTML>
Save the page as ordercancel.htm before continuing.
For the transerror.asp page we reassure them that no money has been debited and
will also display the error message, more for our benefit when trying to debug.
The actual error description was passed in the URL by the OnTransactionAbort
method of the processorder.asp page which redirected us here.
<!--#include file="ServerSideGlobalDef.inc"--> <% var sErrorDescription = unescape(Request.QueryString); %> <HTML> <HEAD></HEAD> <BODY> <FONT FACE="Comic Sans MS" SIZE="3"> Due to a technical fault we have been unable to complete your order.<BR> Your order has been cancelled and no monies will be deducted from your credit card.<BR> The problem is listed below </FONT> <BR> <P><%= sErrorDescription %></P> </BODY> </HTML>
This page needs to be saved as transerror.asp.
Sweetness Follows - Order Transaction Completed Successfully
The transaction has completed successfully, the sun is shining and MusicMadOnline.com
are a few dollars richer. All that needs doing now is to e-mail the order shipping
department telling them what to send and to whom. We'll also e-mail the customer
just to confirm the order is being sent. The page itself also displays confirmation
and a thank you message.
To send an e-mail we will use Collaboration Data Objects for Windows NT Server,
which are installed on NT Server when you install Option Pack 4. You'll need
to either have a mail server, such as Microsoft Exchange, located on your server
machine or use SMTP (Simple Mail Transfer Protocol) to divert messages to a machine
with a mail server. If you don't have a mail server on your machine, using SMTP's
smart host facility enables you to point to your mail server. Open up the IIS
console and check that SMTP is loaded and running on your server machine. Right
click the SMTP service and choose Properties. Select the Delivery tab and in
the Smart host property enter either your mail server's IP address or friendly
name. Now messages should be routed for delivery to your mail server.
Our first goal is to build up the text for the e-mails, retrieving
the information from the database. To do this we need to create two more stored
procedures.
First, to retrieve the customer details, we need to create a CustomerDetails
stored procedure. We know the OrderId as it's passed from the proceeding page
so we can use that to do a join between the Orders table and Customer table.
CREATE PROCEDURE [CustomerDetails] ( @OrderId int ) AS SELECT Title, FirstName, LastName, Email, Street, City,Locality,PostCode,Country FROM Orders JOIN Customer ON Customer.CustId = Orders.CustId WHERE Orders.OrderId = @OrderId
Our second stored procedure is OrderDetails, which returns details of each item the customer ordered.
CREATE PROCEDURE [OrderDetails] ( @OrderId int ) AS SELECT OrderItem.ItemId, Qty, ArtistName, Title, Price FROM (OrderItem JOIN Stock ON Stock.ItemId = OrderItem.ItemId) JOIN _ Artist ON Artist.ArtistId = Stock.ArtistId WHERE OrderItem.OrderId = @OrderId
Before leaving Enterprise Manager remember to give MMCustomer
Exec permissions for both the stored procedures.
Now to create the AcceptOrder.asp page. We appended the OrderId to the end of
the URL in ProcessOrder.asp when we redirected to this page. We can now retrieve
it using Response.QueryString. This information is all we need to start pulling
back customer and order information from the database.
Having created a new ADO.Connection object and opened a connection to the database
we then populate a Recordset using the CustomerDetails query.
<!--#include file="ServerSideGlobalDef.inc"--> <% // Get Order info var sCustEmail = "Thank you for shopping with MusicMadOnline.com\n\n _ The following items\n\n"; var sOrderDeptEmail = "Send the following items - \n\n"; var sAddress = ""; var iTotal = 0; var iOrderId = Request.QueryString; var loConn = Server.CreateObject("ADODB.Connection"); var loRS; // Retrive customer details from database loConn.Open(sdbConnString); loRS = loConn.Execute("CustomerDetails " + iOrderId);
Now we can retrieve the customer's address and e-mail address from the database before closing the recordset.
// create customer address part of e-mail sAddress = sAddress.concat(loRS("Title"), " ", loRS("FirstName"), _ " ", loRS("LastName"),"\n"); sAddress = sAddress.concat(loRS("Street"), "\n", loRS("City"), "\n", _ loRS("Locality"), "\n"); sAddress = sAddress.concat(loRS("PostCode"), "\n", loRS("Country"), _ "\n"); sEmail = new String(loRS("Email")); loRS.Close();
Now we need to build up a list of the items the customer has ordered. Another recordset is created using the OrderDetails stored procedure and we then loop though it row by row building up the e-mail's text: one e-mail for the shipping department and one as confirmation for the customer.
// Retrive order details - list of all items to be sent loRS = loConn.Execute("OrderDetails " + iOrderId); // create the order detail par of the e-mail while (!loRS.Eof) { sCustEmail = sCustEmail.concat(loRS("Title"), " by ", _ loRS("ArtistName"), " Qty ", loRS("Qty"), " @ £", _ loRS("Price"), "\n"); iTotal = iTotal + loRS("Qty") * loRS("Price"); sOrderDeptEmail = sOrderDeptEmail.concat("ItemId : ", _ loRS("ItemId"), "\nArtist Name : ", _ loRS("ArtistName"), "\nTitle : ", loRS("Title"), _ "\nQty : ", loRS("Qty"), "\n\n"); loRS.MoveNext(); } loRS.Close(); loConn.Close(); loRS = null; loConn = null; // complete e-mail message sOrderDeptEmail = sOrderDeptEmail.concat _ ("\n\nto the address below\n\n",sAddress); sCustEmail = sCustEmail.concat("\n\nHave been shipped to\n\n", sAddress, "\n\n£", iTotal + 2.5, _ " will be debited from your credit card\n"); sCustEmail = sCustEmail.concat("\nIf you have any queries please _ email us at [email protected]");
With the e-mail message created we can now send it using CDONTS's
NewMail object.
First we create a new NewMail object, simply use NewMail's Send method to send
the e-mail. The Send method has 5 parameters: From E-mail Address, Send to E-mail
Address, Subject, Message Body and importance. I have left the importance parameter
at its default value of normal.
The NewMail object is a use once and throw away object. If we want to send another
e-mail we must create a new NewMail object.
loEMailer = Server.CreateObject("CDONTS.Newmail"); // Email order person loEMailer.Send("[email protected]", "[email protected]", "New Order", sOrderDeptEmail); // Email Customer var loEMailer = Server.CreateObject("CDONTS.Newmail"); loEMailer.Send("[email protected]", sEmail, _ "Your order with MusicMadOnline.com", sCustEmail); %> <HTML>
Finally, lets create the page text itself confirming the order transaction's success.
<HEAD> <TITLE>Order Successful</TITLE> </HEAD> <BODY> <DIV ALIGN="center"> <FONT FACE="Comic Sans MS" SIZE="5" color="Navy"> Your order was successful </FONT> <FONT FACE="Comic Sans MS" SIZE="3" color="BLACK"> <P> Your goods will be sent to you shortly. Confirmation of your order items have been e-mailed to you </P> </FONT> <FONT FACE="Comic Sans MS" SIZE="4" color="#FF8040"> <P> Thank you for shopping with MusicMadOnline.com </P> </FONT> <FORM ACTION="" METHOD="POST"> <INPUT TYPE="Button" NAME="cmdGoShop" VALUE="Contine Shopping" onClick="window.location.replace('home.asp');"> </FORM> </DIV> </BODY> </HTML>
Save the page as AcceptOrder.asp.
Comments