Assignment 9 - Music Store - Checkout (part 2) & Order History
This week you will finish Checkout.aspx and create OrderHistory.aspx.
CheckOut.aspx has three major items left to complete:
- Insert a new record in tblOrders for each new order
- Insert a new record in tblOrderItems for each item in the cart.
- Create an HTML confirmation string for the browser and email.
The "ShipOrder_click." procedure handles all the steps associated with shipping
the order. To keep this procuedure to a manageable size it is a good idea to delegate
the business logic to classes. The possible procedure names are listed in A08.
Create two new tables: tblOrders and tblOrderItems. OrderID in tblOrders is an autonumber
The sql to create the tables:
CREATE TABLE tblOrders
[orderID] INT NOT NULL PRIMARY KEY IDENTITY,
[orderDate] DATETIME NOT NULL,
[custID] INT NOT NULL
CREATE TABLE tblOrderItems
[orderID] INT NOT NULL ,
[asin] VARCHAR(50) NOT NULL,
[qty] INT NOT NULL,
[title] VARCHAR(200) NOT NULL,
[artist] VARCHAR(100) NOT NULL,
[price] FLOAT NOT NULL,
Primary Key (orderID, asin)
Steps for handling order information in ShipOrder_click:
- Retrieve CustID for new Customers: CustID is needed as an input to tblOrders.
For existing customers CustID was retrieved from the database and saved to the ViewState.
It can be retrieved from the Viewstate when handling the order.
For new customers the CustID is generated by the autonumber field when a new record
is inserted into the database. It needs to be retrieved for writing the order information.
SQL Server provides a handy function "SCOPE_IDENTITY()" that returns the value of
new identity fields. A single SQL statement can be used to insert new customer data
and retrieve the customer ID that is generated by the database. Add the following
"scope_identity()" function to the end of the sql INSERT statement:
; select SCOPE_IDENTITY()
Modify the method in checkout.cs that you use to add a new customer by replacing
the "ExecuteNonQuery" method with "ExecuteScalar." ExecuteScalar is specifically
designed for SQL statements that return a single data item. It is almost identical
to the ExecuteNonQuery except it returns an integer. Use it to retrieve the CustID
of new customers.
- Retrieve Cart Items: Before handing the order information we need to determine
if there are any items in the cart.
- Use "ListCartItems" from the shopping cart class to fill a dataTable with the items
in the cart associated with the user's SessionID. A good name for the dataTable
- If the cart does not contain any items (dt.Rows.Count == 0) write a brief confirmation
message (i.e. "Your information has been updated.") and jump out of the ShipOrder_onClick
procedure with a "return;" statement.
- Write Order to database: If the cart contains items then new records need
to be inserted into tblOrders and tblOrderItems. Create a new method in checkout.cs
named "WriteOrder" to handle this step. Sytnax:
public static int WriteOrder(DataTable dtCartItems, int CustID) .
- Write to tblOrder: The WriteOrder(...) method use a sql INSERT statement
to add a new record. Use the "scope_identity()" function in the insert sql statement
with ExecuteScalar to retrieve the OrderID, as done with the custID.
- Write to tblOrderItems: Each item in the cart needs a separate record in
tblOrderItems. A single SQL statement can be used to copy the item information from
tblCart to tblOrderItems. The sql statement uses a Select statement nested inside
of an Insert Into statement (See "sql insert subquery"). The select statement retrieves the relevant records
from tblCart and the Insert statement inserts them into tblOrderItems. The following
statement shows the syntax and uses two parameters: @orderID and @sessionID.
sql = "INSERT INTO tblOrderItems (OrderID, ASIN, Qty, Title, Artist, Price) " +
"SELECT @orderID, ASIN, Qty, Title, Artist, Price " +
"FROM tblCart " +
"WHERE SessionID= @sessionID ";
- Remove the items from tblCart using any one of the following three methods:
- Delete the items in tblCart associated with the current SessionID.
- Tag the items in tblCart with an OrderID so that we know they have been shipped.
- Abandon the current Session using Session.Abandon(). To force a new SessionID you
will also need to delete the current sessionID from the cookie using:
Response.Cookies.Add(new HttpCookie("ASP.NET_SessionId", ""));
- Create HTML Confirmation String: With the order written to the database you
are now ready to prepare a confirmation message for the user. From ShipOrder_onClick
call a function named WriteConfirmationString(intOrderID, dtCartItems). This function
returns a string of HTML containing customer and order information.
Creating the HTML for the confirmation is tedious, but we can leverage our work
by using the same HTML for both the browser confirmation and the email confirmation.
The string should contain the order number, shipping address and order item information.
- Send Email: Use this SendEmail class (source)
to send an HTML email. Unfortunately many email clients do not support styles sheets
(check current status on
campaignMonitor.com) so styles must be inline. The syntax to use the class
is something like this:
String strConfirm =
"<body style='background-color:#F5F5F5'>" +
"<div style='text-align:center'>" +
"<h2 style='color:#CC0000'>Order Confirmation from XML Music</h2>" +
"<table style='padding:3px;'><tr> " +
"<td>Order Number:</td>" +
"<td>" + OrderID + "</td></tr>" +
"<tr><td style='vertical-align:top;'>Shipped to:</td><td>" +
tbFName.Text + " " + tbLName.Text + "<br />" +
tbStreet.Text + "<br />" +
tbCity.Text + " " + tbState.Text + " " + tbZip.Text +
"<br /><br /></td></tr>" +
"<tr><td style='vertical-align:top;'>Titles:</td>" +
//Loop through items in cart and add info to strConfirm
foreach (DataRow dr in dtCart.Rows) )
double price = Convert.ToDouble(dr["Price"]);
price = Math.Round(price, 2);
int quantity = Convert.ToInt32(dr["Qty"]);
strConfirm += "<tr>" + "<td><span style='color:#006699;'>" +
dr["Title"] + "</span><br />" +
//Add shipping cost and total
strConfirm += "</table></td><tr>" +
"<tr><td>Sub-total:</td><td>" + Subtotal + "</td></tr>" +
//Add links and images
strConfirm += "<br />" +
border='0' " +; +
string subject = "Order Confirmation from XML Music";t;;
//Email class returns a text string with status.
return SendEmail.Send(toEmail, toName, "firstname.lastname@example.org", "XML Music",
Where toEmail is the destination address, toName is the recipient's name, and msgBody
is an HTML string containing the shipping and product information. Anchor tags and
image src's must include the full server URL, as shown in the code above.ve.
This page is similar to SearchBrowse but with a different SQL statement. The SQL
statement uses CustID to retrieve the customer's history.
There are a couple ways of getting the CustID to OrderHistory.aspx.
- CustID as parameter in the querystring. It is easy to add a parameter the OrderHistory.aspx
link that contains the CustID but we don't want curious and malicious users to change
the Customer ID in the querystring and see order histories of other users. Disguise
the ID by multiplying it by a large integer (5-6 digits) when writing the anchor
tag on the checkout page and dividing it by the same large number in the OrderHistory
page. The large number acts as a simple encryption key.
- CustID in a session object. This has the advantage that the CustID does not need
to be encrypted, but the disadvantage that it will timeout after 20 minutes and
cannot be used in emails.
Congratulations, you have completed your ASP.NET Music Store!
To submit your assignment for grading send an email with the URLs for your assignment
- Professor Sandvig and the TA at
(note: this address is for homework assignments only. Please send correspondence
- cc. a copy to yourself
The subject line of your email should read "MIS324 AXX YourName"
where XX is the assignment number. Please check that your URLs are correct before
submitting them for grading. Files with incorrect URLs will not be graded.
When pages are connected via navigation (as in your Music store project) it is only
necessary to submit the URL of the first page.