1. Problem 1 Execute
   Problem 1 Code

2. Problem 2 Execute
   Problem 2 Code

3. Rebuild database tables for Problem 1 Execute
   Code for database tables rebuild

Assume, there is a table structure as shown below:

table name: socks_inv

prod_id int NOT NULL, [1 thru 8] pattern_name varchar(20) NOT NULL, mf char(1) NOT NULL, ["M"=male, "F" = female] sock_size int NOT NULL, [6 to 16 for "M", 1 to 12 for "F"] cur_inv int NOT NULL, [number of pairs of socks] PRIMARY KEY (primary key(prod_id,mf,sock_size)) Assume there is a company that sells socks to both men and women. There are 8 sock patterns. The socks for Men are available in sock sizes 6 → 16 and for Women sock sizes 1 → 12. The eight sock products have pattern names: "blue/yellow stripes", "red/green stripes", "blue argyle", "red argyle", "green argyle", "white/black checks", "blue polka dots", and "red polka dots". The current product inventory (for all product variations of product (pattern), sizes for males and females) is stored in the database table named sock_inv as defined above. There are 184 rows in the socks_inv table. The primary key for the table is the composite key: prod_id, mf, sock_size.

The table socks_inv is in a database named "gl001". For a recordset, the credential string is: rs.open some_sql_string,"DSN=gl001;UID=gl001;PWD=11111;" For a connection, the credential string is: cn.open "gl001","gl001","11111"

1. (50 points) Write the 1st and 2nd for an socks_inv update. Specifically:

1. The first pass (named: sub pass1) provides
   1. A form like this: <form name="f1" action="http://auckland.bauer.uh.edu/socks_update.asp" method="post">
   2. A textbox for specifying the prod_id named "pid" with size="1"
   3. Two radio buttons with name="mf" and values of "M" and "F", respectively
   4. A textbox for sock_size with size="2" named "ss"
   5. A textbox named recvd with size "4" that is used to record the receipt of new product inventory
   6. A hidden variable named token with value="2"
   7. A submit button
   This is a simple HTML page allows the user to enter new product receipts (i.e., inventory received that must be recorded in the socks_inv table) into a textbox named recvd and record the prod_id, male or female, and sock_size. (Do not edit the data)
2. The second pass (named: sub pass2) attempts to update the cur_inv value for the product with the amount of the new product received (the amount entered in the recvd textbox above). In order for the update to proceed, the specific values for prod_id, mf and sock_size must exist in the data.
   1. Using a recordset, test for existence of the primary key in the database using the values of the prod_id, mf and sock_size entered by the user.
   2. If the record exists, perform the update the specified row:
         Using an SQL UPDATE, add the value of recvd to the current value of cur_inv
      If the SQL update works respond: "UPDATE OK", otherwise "UPDATE FAILS"
   3. If the record does NOT exist:
         Return an HTML page that says: "RECORD NOT FOUND. TRY AGAIN"

token_value = request.form ("token") 
select case token_value
case ""
  call pass1
case "2"
  call pass2
end select  

1. in pass1 (named: sub pass1):
   1. the same form as above except the action value is: action="http://auckland.bauer.uh.edu/socks_report.asp"
   2. eight radio buttons labeled: Product 1 → Product 8, all named which_prod with values 1 → 8 respectively
   3. A hidden variable named token with value="2"
   4. A submit button
2. in pass 2 (named: sub pass2) produce an HTML table showing:
   1. all the columns value in socks_inv where prod_id is equal to the value of the radio button clicked in pass1
   2. order the report sorted by mf ASC and sock_size ASC order