Problem 1 answer (source code)
Problem 2 answer (source code)
(NOTE: Non-required color in Problem 2 pass2 and an edit in Problem 1 pass 2 were added. Changes are noted in the source code and the output)

Assume, there is a consulting firm that develops custom solutions for clients. The firm has a database of employees that contains technical skills for each of the employees. There is a table named emp_skills that holds the employee skill data that is maintained by the employees themselves. Further, there is a table named skills that holds the skill names. The data structures for these tables is shown below:

emp_skills	skills
emp_id int IDENTITY (1,1), efn varchar(25), '*** employee first name eln varchar(25), '*** employee last name off_loc varchar(25), '*** office location skill_list varchar(1000), (PRIMARY KEY (empid))	skill_id int IDENTITY (1,1), skill_name varchar(25), (PRIMARY KEY (skill_id))

1. Part I (70 points)

   Write an asp program named p1.asp that allows an employee to update their skill list. Specifically:

   1. The first pass provides the following HTML page -- [DO NOT WRITE this first pass]

      UPDATE MY SKILLS LIST
      <form name="alice" method="POST" action="p1.asp">
      Enter your Employee ID <input type="textbox name="eid" size="6">
      <input type="hidden" value="2" name="token">
      <input type="submit">
      

   2. The second pass -- [WRITE this second pass]
      • uses the employee id from pass 1 ("eid") and retrieves the employee's row from the emp_skills [←correction] table.
      • If not found, return a message to the user that says "EMPLOYEE NOT FOUND"
      • If found, this pass returns a HTML form with:
        • <form name="alice" method="POST" action="p1.asp">
        • a hidden textbox named "eid2" containing a value clause set equal to the value "eid" received.
        • the employee first name (efn) shown on a line by itself
        • the employee last name (eln) on a line by itself
        • the employee office location (off_loc) shown on a line by itself
        • the employee skill list (skill_list) on a line by itself
        • a select box named "new_skill" that has ALL of the skill_name values from the skills table values shown as options. The general format of this select is:
             <select name="new_skill">
             <option value="0">Select a skill to add to your skill list
             <option value="1st skill_name from skills table"> 1st skill_name from skills table
             <option value="2nd skill_name from skills table"> 2nd skill_name from skills table
             <option value="3rd skill_name from skills table"> 3rd skill_name from skills table
             ...etc.
             <option value="last skill_name from skills table"> last skill_name from skills table
             </select>
          
        • <input type="hidden" value="3" name="token">
        • <input type="submit">

   3. the third pass -- [WRITE this third pass]
      This pass should update the emp_skills table by appending the newly selected skill to the employee's current skill_list value in the emp_skills table.
      • use the value of the employee id from pass 2 ("eid2") in the SQL "WHERE" clause
      • the "SET" portion of the SQL should say:

        "SET skill_list=skill_list+" + request.form("new_skill") + ","

      • update the emp_skills table
      • if OK (i.e., numa=1) write a response to the user that says: "Added the selected skill OK."
      • if numa <> 1, the response should say: "UPDATED FAILED"

      Show no "main"

2. Part II (30 points)

   Provide a search program (named "p2.asp") for the emp_skills table that retrieves ALL employees with specific skill(s).

   1. the first pass -- [DO NOT WRITE this first pass]

      This pass shows ALL of the "skill_name" values in the skills table as checkboxes. The checkboxes would appear as:

      <form name="cform" method="POST" action="p2.asp"> <input type="text" name="token" value="2">
      <input type="checkbox" name="c1" value="1st skill_name">1st skill_name
      <input type="checkbox" name="c2" value="2nd skill_name">2nd skill_name
      <input type="checkbox" name="c3" value="3rd skill_name">3rd skill_name
      etc.
      <input type="submit">
      </form>

      This allows the user to search for one or many skills by checking the checkbox for each desired skill.

   2. the second pass -- [WRITE THIS 2nd PASS ONLY]

      This pass loops through all the checkboxes values that were sent [NOTE: A checkbox's 'name-value pair' is only sent if the checkbox was checked]. The ones checked will be used to find which skills will be returned in the search.

      To construct the WHERE clause for the SQL for the search, you can use a loop to append the Request.Form values (i.e., "skill_name") that where sent in "like" clauses with the loop:

      sql=...WHERE "
      For i = 2 To Request.Form.count
          sql=sql+" skill_list like '%"+Request.Form(i)+"%'"
          if i < Request.Form.count then
              sql=sql+" AND "
          end if
      Next

      the result would be something akin to:

      Select * FROM emp_skills WHERE skill_list '%VB.NET%' AND skill_list like '%php%' ...

      Note: in the above code, the Request.Form.count is the total number of name-value pairs received.

      Note: in the above code, the Request.Form(i) is the value of the ith name-value pair received. This collection starts its index at 1 not zero. So,

      request.form(1) is the 1st name-value pair, (in this case the first one will be the "token" field)
      request.form(2) is the 2nd name-value pair,
      request.form(3) is the 3rd name-value pair,
      request.form(4) is the 4th name-value pair,
      etc.

      I started at item 2 because the "token" name-value pair would be received first.

      In the SQL statement use the " order by eln ASC, efn ASC" clause to alphabetize the names.

      Thus, the SELECT will return all employees in an HTML table (columns are: emp_id,efn,eln,off_loc,skill_list) that have ALL the checked skill_names in their "skill_list" field.

      Display the results in an HTML table and show a count of the number of employees that have all the selected skills.

      Do not write "main".