Syllabus Schedule Project Labs Roster Grades CSS Tutorial Server Access Assignment 1 Assignment 2 Assignment 3 Assignment 4 Assignment 5 Assignment 6 Assignment 7 Assignment 8 Assignment 9

Assignment 9 - Music Store - Checkout (part 2) & Order History

This week you will finish Checkout.aspx and create OrderHistory.aspx.

1. CheckOut.aspx has three major items left to complete:

  1. Insert a new record in tblOrders for each new order
  2. Insert a new record in tblOrderItems for each item in the cart.
  3. 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 field.

tblOrders

tblOrders

tblOrderItems

tblOrderItems

Steps for handling order information in ShipOrder_click:

  1. 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.
  2. Retrieve Cart Items: Before handing the order information we need to determine if there are any items in the cart.
    1. 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 is dtCartItems.
    2. 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.
  3. 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. Syntax:
    public static int WriteOrder(DataTable dtCartItems, int CustID)
    1. 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.
    2. 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 ";

    3. Remove the items from tblCart using any one of the following three methods:
      1. Delete the items in tblCart associated with the current SessionID.
      2. Tag the items in tblCart with an OrderID so that we know they have been shipped.
      3. 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", ""));

  4. Create HTML Confirmation String: With the order written to the database you are now ready to prepare a confirmation message for the user. Both the email and the browser need a confirmation. 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.
    From ShipOrder_onClick call a function named WriteConfirmationString(intOrderID, dtCartItems). This function returns a string of HTML containing customer and order information.

  5. 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>" +
    "<td>" +
    "<table style='padding:3px;'>";

    //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 />" +
    "<a href='http://yorktown.cbe.wwu.edu/sandvig/MIS324/assignments/musicstore/'><img border='0' " +; +
    "src='http://yorktown.cbe.wwu.edu/sandvig/mis324/assignments/musicstore/MusicImages/continue-shopping.gif'></a></p>";

    //Send email
    string subject = "Order Confirmation from XML Music";t;;

    //Email class returns a text string with status.

    return SendEmail.Send(toEmail, toName, "csandvig@wwu.edu", "XML Music", subject, strConfirm);

    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.

2. OrderHistory.aspx

This page is similar to default but with a different SQL statement. The SQL statement uses CustID to retrieve the customer's history.

Steps:

  1. Add a hyperlink in the checkout.aspx page that points to OrderHistory.aspx. Include CustID in the link as a parameter. CustID will need to be encrypted so that curious and/or malicious users cannot impersonnate other users. Add the encryption class to your App_Code folder. Encryption sample (source) illustrates how to encrypt a string.
  2. In OrderHistory retrieve the custID from the querystring and use the decrypt method of encrypt.cs to decrypt it.

Congratulations, you have completed your ASP.NET Music Store!


To submit your assignment for grading send an email with the URLs for your assignment to:

  1. Professor Sandvig and the TA at (note: this address is for homework assignments only. Please send correspondence to )
  2. 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.
 
 

Regular Expressions HTML Color Names Color Picker ASCII Character Codes Unicode Character Codes