|Deadline extended--due 11/16, Page Revised: September 06, 2002 12:06 PM
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:
- To maintain the data in the Customer table.
- To allow users to create and update their customer record
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)
|0 (or missing)
||Show the customer data entry view
||Use ADO and a SQL Update Statement to
save data from the form into the database
||Use ADO and SQL to Insert a new Customer
Record in to the database using fields from the form
||Force the creation of a new user by
setting the CustomerID = 0 and then setting VW = 0
- You must provide entry for all fields except CustomerID, and Voided.
- 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.
- You must validate the email address. A valid address contains at
least 1 @ and one period.
- You can use the source code in 'shop.asp' to include in your project
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
- Test your page very carefully, both with new records, and editing an
existing record by calling the page with a query string of "
- Make sure your page follows the form design guidelines discussed in
- In Lab 11 you will be modifying this
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.
- I found it useful to use arrays to help in the processing. I used 4
- One to hold the captions for each of the fields
- One to hold the actual field names
- One to hold the values from the form
- One to hold flags. (Either Blank or a Red *)
- I used a Loop to draw out the customer entry form for each row:
- I draw the element in the flags array (it will show a red *
or a blank)
- Then I draw out the array of field captions
- Then I draw the form elements, I use the field names array
to set the NAME= property each control
- After the form is submitted I grab each of the values from the form
and put them into the values array.
- 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.
- 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?
- 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
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
- If CustomerID = 0
- 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
- 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