Server-Side Web Programming with Active Server Pages
Lab 10 - Customer Maintenance Pages

Home Lectures Lab Resources  

 
Deadline extended--due 11/16, Page Revised: September 06, 2002 12:06 PM
Solution:

In this lab you will create a self-referencing page that allows the customer to maintain their account. The page is to be named 'Customer.asp'. This lab is harder then it looks. Use the example at: http://www.valtara.com/csc123/shop/customer.asp?CustomerID=3 .

Purpose of the page:

  1. To maintain the data in the Customer table.
  2. To allow users to create and update their customer record

Page Views

The page is self referencing so the query string VW must be used to control what happens in the page (usually via a select statement)

VW Value Meaning
0 (or missing) Show the customer data entry view
1 Use ADO and a SQL Update Statement to save data from the form into the database
2 Use ADO and SQL to Insert a new Customer Record in to the database using fields from the form
3 Force the creation of a new user by setting the CustomerID = 0 and then setting VW = 0

Requirements:

  1. You must provide entry for all fields except CustomerID, and Voided.
  2. You must validate that all fields have data except for the optional Fax and Extension fields. Your page must show users what fields are missing or incorrect.
  3. You must validate the email address. A valid address contains at least 1 @ and one period.
  4. You can use the source code in 'shop.asp' to include in your project
  5. This file is a nice demo of some advanced ADO tricks. In particular the function InsertWithIdentity() can be used in combination with your SQL Insert statement to generate a new customer record (when adding new customers) and get back the resulting CustomerID value. 
  6. Test your page very carefully, both with new records, and editing an existing record by calling the page with a query string of "?CUSTOMERID=x".
  7. Make sure your page follows the form design guidelines discussed in class
  8. In Lab 11 you will be modifying this page to use cookies. The shop system uses a persistent cookie to store the CustomerID value. So that when a page is visited in the shop system the CustomerID of the user can be retrieved from the cookie and used to power up various queries. To do this when you add a new user you should write out the CustomerID to the cookie. More details about this in lab 11.

Hints:

  1. I found it useful to use arrays to help in the processing. I used 4 arrays. 
    1. One to hold the captions for each of the fields
    2. One to hold the actual field names
    3. One to hold the values from the form
    4. One to hold flags. (Either Blank or a Red *) 
  2. I used a Loop to draw out the customer entry form for each row:
    1. I draw the element in the flags array (it will show a red * or a blank)
    2. Then I draw out the array of field captions
    3. Then I draw the form elements, I use the field names array to set the NAME= property each control
  3. After the form is submitted I grab each of the values from the form and put them into the values array.
  4. I set a flag sOK = True. 
    Then I spin the array with a loop and test each value to make sure it is correct. 
    If it is not I set sOK = false and set the element in the flags array to a red *
    Otherwise I leave it blank.
  5. OK. here is where the going gets narrow. Suppose that I was editing an existing record from the data base (Flag = 0). I filled the values array by querying the database. Now, I press the Submit button, the form self submits with a VW of 1 (Save). The spin logic above kicks in. If the sOK flag is false, I switch the VW to be equal to zero and draw the form again (As in step 2) the result of which is the user see the form drawn with the red * next to the bad fields. Cool. eh? 
  6. For New records, (either because there was no CustomerID or came into the form with a flag of 3) the values array is empty so the form is blank. Note that when drawing the <FORM> element the flag is set to a value of 2 to force a new record to be created.

Form Flow (Top to bottom):

Here is the flow of the form from top to bottom e.g. From line 1 to the last line.

Lines of code above <html>

  • Option Explicit
  • Dim Statements
  • Getting of VW value
  • Filling of arrays
  • Call to ValidateCustomer() to get the value of CustomerID
  • Check to see if VW=3, if so set CustomerID = 0 and VW = 0
  • If CustomerID > 0 
    • Set Flag=1 (Save)
    • Open a recordset
      If there is a record fill values array
      Else set flag=2
      Close recordset
  • If CustomerID = 0
    • Se the Flag =2
  • If VW > 0, spin the values array to validate data, fill the flag array and set sOK if needed. Again of sOK is false, reset VW to 0.

Lines of code  Between <html> and </html>

  • Fill in the <HEAD> section
  • In the <BODY>
    • Draw the header from the #Include
    • Draw the title
    • Do a select statement
      • Case 1: Turn the values array into an UPDATE SQL statement and then use ExecuteSQL() to update and the database.
      • Case 2: Turn the values array into an INSERT SQL statement and then use InsertWithIdentity() to add a record to the database
      • Paint the form use the FLAG value to determine what the VW will be when the form is submitted.
    • Draw the footer from the #Include

Lines of code after </html>

  • Use Set ... = Nothing to dispose the recordset object.

This lab is worth 20 points and due on November 16th.  When you have completed the lab, send an email to Chris Allen indicating you are done and providing the external URL to your pages.

Valtara Digital Design  http://www.valtara.com/csc123/   
Copyright 1999, 2001, Valtara Digital Design, Blitzkrieg Software