In returning the customer to the eCommerce site following credit processing, a short detour is made through the OrderCapture.aspx page to generate an email confirmation. From the OrderCapture.aspx page the customer arrives at the SalesOrder.aspx page where a summary of the order is displayed. Customers are encouraged to print the sales order for their records.
The SalesOrder.aspx Page
There is similarity in the code for the SalesOrder.aspx page and the ShopCart.aspx page. Both present the same information in the same format except that the sales order form does not permit editing. This code is presented in the listing below along with the script to populate the GridView. You should be familiar with GridView coding and function calls from the shopping cart. Note that this page integrates with the eCommerce.master page. The System.Data.OleDb namespace is required for additional scripting described below.
<%@ Page MasterPageFile="eCommerce.master" Language="vb" Debug="True"%> <%@ Import Namespace="System.Data.OleDb" %> <SCRIPT Runat="Server"> Dim OrderTotal As Decimal = 0.00 Sub Page_Load If Not Page.IsPostBack Then OrderNumberLabel.Text = Session("OrderNumber") TodayDate.Text = Today End If End Sub Function Get_Amount (Price As Decimal, Quantity As Integer) Dim Amount As Decimal Amount = Price * Quantity OrderTotal += Amount Return Amount End Function Function Get_Shipping() Dim Shipping As Decimal = OrderTotal * Application("Shipping") OrderTotal += Shipping Return Shipping End Function Function Get_Order_Total() Return OrderTotal End Function ... </SCRIPT> <asp:Content id="SalesOrder" ContentPlaceHolderID="CONTENT" Runat="Server"> <asp:Label Text="Sales Order" Font-Size="14pt" ForeColor="#990000" Runat="Server"/><br/> <br/> <table border="0"> <tr> <td><b>Date: </b></td> <td><asp:Label id="TodayDate" Runat="Server"/></td> </tr> <tr> <td><b>Order No: </b></td> <td><asp:Label id="OrderNumberLabel" Runat="Server"/></td> </tr> </table> <asp:AccessDataSource id="ShopCartSource" Runat="Server" DataFile="../Databases/BooksDB.mdb" SelectCommand="SELECT * FROM ShopCart WHERE OrderNumber=@OrderNumber" > <SelectParameters> <asp:ControlParameter Name="OrderNumber" ControlId="OrderNumberLabel" PropertyName="Text"/> </SelectParameters> </asp:AccessDataSource> <asp:GridView id="ShopCartGrid" DataSourceID="ShopCartSource" Runat="Server" AutoGenerateColumns="False" ShowFooter="True" Cellpadding="3" HeaderStyle-BackColor="#990000" HeaderStyle-ForeColor="#FFFFFF" EditRowStyle-BackColor="#E0E0E0" > <Columns> <asp:BoundField HeaderText="ID" DataField="BookID" ItemStyle-Width="50"/> <asp:BoundField HeaderText="Title" DataField="BookTitle" ItemStyle-Width="200"/> <asp:BoundField HeaderText="Price" DataField="BookPrice" HtmlEncode="False" DataFormatString="{0:N}" ItemStyle-Width="60" ItemStyle-HorizontalAlign="Right"/> <asp:TemplateField HeaderText="Qty" ItemStyle-Width="40" FooterStyle-HorizontalAlign="Right" ItemStyle-HorizontalAlign="Right"> <ItemTemplate> <asp:Label id="BookQty" Runat="Server" Text='<%# String.Format("{0:D}", Eval("BookQty")) %>' Width="25" Font-Size="9pt"/> </ItemTemplate> <FooterTemplate> <asp:Label Text="Shipping" Runat="Server"/><br/> <asp:Label Text="Total" Runat="Server" Font-Bold="True" Width="50" BorderStyle="Solid" BorderWidth="0" Style="padding:2px; margin-top:5px"/> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Amount" ItemStyle-Width="80" ItemStyle-HorizontalAlign="Right" FooterStyle-HorizontalAlign="Right"> <ItemTemplate> <asp:Label Runat="Server" Text='<%# String.Format("{0:N}", _ Get_Amount(Eval("BookPrice"), Eval("BookQty"))) %>'/> </ItemTemplate> <FooterTemplate> <asp:Label Runat="Server" Text='<%# String.Format("{0:N}", Get_Shipping()) %>'/><br/> <asp:Label id="OrderTotal" Runat="Server" Text='<%# String.Format("{0:C}", Get_Order_Total()) %>' Width="80" Font-Bold="True" BorderStyle="Solid" BorderWidth="1" BorderColor="#C0C0C0" Style="padding:2px; margin-top:5px"/> </FooterTemplate> </asp:TemplateField> </Columns> </asp:GridView> <p>Thank you for your order. We appreciate your shopping at webWarehouse.com. If you have any questions about your order, email us at <a href=""mailto:orders@webWarehouse.com"">orders@webWarehouse.com</a> and reference the order number listed above.</p> </asp:Content>
When the page loads, the Session("OrderNumber") and current date are written to two Label controls. The OrderNumberLabel is for display purposes; it also is needed as a parameter reference for the AccessDataSource to issue its SELECT statement to populate the GridView. This technique has been used previously when SelectCommand parameters are not part of the control being populated.
Clearing the Shopping Cart
Once information about the order has been captured and saved for future reference (this latter step is not part of this example), ordered items can be cleared from the shopping cart. This action rids the ShopCart table of build-up of old records over time. Some online sites maintain shopping cart information for extended periods of time so that customers can return later to continue shopping or to complete the checkout process. In the current example, shopping records are deleted when order payment is completed.
Deletion of shopping cart items needs to take place after the sales order display, that is, after the page loads. If performed during the Page_Load process, the items are deleted prior to binding them to the GridView and no order gets displayed.
A counterpart to the Page_Load event is the Page_Unload event. Page_Unload occurs after all other page events occur. This event signals the proper time—after the page is finished loading and the sales order GridView is displayed—to delete items from the shopping cart. The Page_Unload subprogram of the SalesOrder.aspx page to delete shopping cart items is shown below.
Sub Page_UnLoad Dim DBConnection As OleDbConnection Dim DBCommand As OleDbCommand Dim SQLString As String DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb")) DBConnection.Open() SQLString = "DELETE FROM ShopCart " & _ "WHERE OrderNumber = '" & Session("OrderNumber") & "'" DBCommand = New OleDbCommand(SQLString, DBConnection) DBCommand.ExecuteNonQuery() DBConnection.Close() '-- Assign a new order number RANDOMIZE Session("OrderNumber") = (INT((9999999 - 1111111 + 1) _ * RND + 1111111)).toString() End Sub
After deleting all shopping cart records for this customer, a new Session("OrderNumber") is assigned. The current shopping session is over and any further shopping takes place under a different order number. The same random number generator that produces the initial order number is used to create the new order number.
Shopping Cart Maintenance
Although shopping cart records for customers who complete the shopping process are deleted, there still may be build-up of old records of customers who abandon the site. A customer, for instance, may leave the site and never return, or fail to return prior to a Session's 20-minute time-out period. This customer receives a different order number, and previous shopping cart items do not get deleted. Long-term maintenance of the shopping cart, then, requires it to be purged periodically of old records of long-lost customers.
One way to do this is by periodically running a script to check the OrderDate field written to each ShopCart record. If the date is of a certain age, then the record is deleted. This is the reason, in fact, for including this date in a shopping cart record. Below is a script to remove shopping cart records that are older than five days.
'-- Remove old shopping cart records Dim NumberOfDays as Integer Dim DBConnection As OleDbConnection Dim DBConnection1 As OleDbConnection Dim DBCommand As OleDbCommand Dim DBReader As OleDbDataReader Dim SQLString As String DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb")) DBConnection1 = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Server.MapPath("../Databases/BooksDB.mdb")) DBConnection.Open() SQLString = "SELECT * FROM ShopCart" DBCommand = New OleDbCommand(SQLString, DBConnection) DBReader = DBCommand.ExecuteReader() While DBReader.Read() If DateDiff(DateInterval.Day, Today, DBReader("OrderDate")) < -5 Then DBConnection1.Open() SQLString = "DELETE FROM ShopCart " & _ "WHERE OrderNumber = '" & DBReader("OrderNumber") & "'" DBCommand = New OleDbCommand(SQLString, DBConnection1) DBCommand.ExecuteNonQuery() DBConnection1.Close() End If End While DBReader.Close() DBConnection.Close()
This script uses the Visual Basic DateDiff() function to return the number of days between the current date and the OrderDate of a record. If the difference is greater than 5 (actually -5 because of comparing an older date to the current date), the record is deleted.
This script can be run periodically as a separate database maintenance page, or it can be coded on one of the pages of the eCommerce site to take place routinely, say, whenever shopping cart records are normally deleted following order completion. For example, this routine can be placed in the Page_Unload subprogram described above. Then, after a known customer's records are deleted, this routine checks for any other records that are older than five days and deletes them also. Thus, shopping cart maintenance is built into the normal routines of the eCommerce site.