DISC 4372-Spring 2001-Parks-Name_______________________ SSN___________________

1. (45 points) Given the following creation process for a table named shirts.

CREATE TABLE shirts (
stock-num char(5) NOT NULL,
color char(3) NOT NULL,
size char(3) NOT NULL,
inventory-on-hand int,
reorder-point int,
reorder-amount int,
on-order char(1), '**** "Y" = an order has been placed, "N" = no order has been placed
reorder-date varchar(24),
price decimal (10,2),
primary key (stock-num,size,color))

Write a single active server page that queries the inventory status:

  1. Show the user on the first pass a list of all the available shirts in stock (i.e, where inventory-on-hand > 0). The list should include a row for each stock-num, color, and size. Each item (stock-num) also has a gif file named the same as the stock-num (e.g., for stock-num = "12345", there is a gif named "12345.gif"). Show the gif file for each stock-num at the beginning of the list for that stock-num (i.e., the gif file file followed by rows that contain the size and color options for that stock-num).
    At the beginning of each row there are also the words "Click here to check the inventory". When the user clicks on a row (i.e., on the words "Click here to check the inventory", the HTML page sends the stock-num, size, and color information back to the server.
  2. On the second pass, the asp program reports the inventory-on-hand and whether an order has been placed or not. If an order has been placed show the reorder-amount and date of the order.

2. (50 points) Using the same table as above, write a three-pass asp program to record the receipt of new inventory.

  1. On the first pass, show ALL inventory items (stock-num, color, and size). Show NO gif files as above. At the beginning of each row, show a link "Record new inventory." When the link is clicked, send the row data to the server (i.e, the stock-num, color and size).
  2. On the second pass, return the stock-num, color, size and the inventory-on-hand. Provide a field for the amount of inventory received. Also provide a submit button. (Perform NO error checking on the inventory recieved amount)
  3. When the third pass receives the data from the second pass, update:

    1. the inventory-on-hand field by the inventory received
    2. set the on-order field to "N"
    3. set the reorder-date to a single blank

    Return a page to the user indicating a successful or unsuccessful update.