MIS 4372 Midterm Answers-- Fall 2018 -- Parks
     Problem 1:    Execute    Code      Problem 2:     Execute     Code     Database Table:     Rebuild     Code for rebuild    
1. (50 points) Assume, the table named f18 contains population data on the top 200 largest US cities. The column names and definitions are shown below: 
   rank int,         [rank from 1  → 200]
   city varchar(30),                       
   state varchar(20),
   pop int,          [population count]
   pc numeric(18,1),  [percent population change over the last 10 years]
   primary key(city)
Write the update (i.e., modify) program named f18mod.asp. This is a three pass program.
  1. The first pass (named: pass1) allows the users to pick the city by entering the city's name they wish to modify into a textbox named chosenCity.
    • DO NOT edit this textbox data in pass1
    • The form tag should have the following two attributes:
            a. action="http://auckland.bauer.uh.edu/students/parks/f18mod.asp"
            b. method="post"
    • include the following element in the form:
            <input type="hidden" name="passtoken" value="2">
    • provide a submit button
  2. The second pass (named: pass2):
    • read a single database row from the f18 table based on the value of chosenCity entered previously
    • if a record was returned: show on the new page:
      1. the rank as text
      2. the chosen city name as text
      3. the state name as text
      4. into textboxes place the current values of:
        1. the population [the pop column value in a textbox named pop_toUpdate]
        2. the percent population change [the pc column value in a textbox named pc_toUpdate]
        so that the user can change or replace the existing values.
      5. include the following two hidden elements in the form:
        1. <input type="hidden" name="passtoken" value="3">
        2. <input type="hidden" name="city" value="city value read from the database">
      6. provide a submit button
    • if no record was returned write an error message to the user
  3. The third pass (named: pass3):
    • Use the data from the previous pass to update the f18 database table.
    • DO NOT edit the data from pass2
    • report "Successful Update" or "Unsuccessful Update" on the page
2. (50 points) Write the two pass *.asp program named: f18LongReport.asp. where:
  1. The first pass (named: sub pass1) uses the the HTML shown to the right for the user to specify:
    • the sort order of a report. Four options are shown in a radio group named sortoption. The radio values provided are: 1,2,3, and 4.
    • which cities are to be included in the report. Five options are shown in a radio group named includeoption. The radio values provided are: 1,2,3,4, and 5.
    • Use the following two attributes in the form tag:
            1. action="http://auckland.bauer.uh.edu/students/parks/f18LongReport.asp"
            2. method="post"
    • include the following hidden form element:
            <input type="hidden" name="passtoken" value="2">
    • provide a submit button
  2. The second pass (named: sub pass2) creates a report that uses the two radio choices from pass1 to return an HTML table
    • show the SQL used in the query
    • show the HTML table with all five database columns
    • After the table show a count of the rows returned
 
 Sort by:
  City (A → Z)
  State (A → Z), then
      City (A→Z)   Population (High → Low)
  Population (Low → High)
 Include:
  All Cities
  Top 100 in Population
  Top 10 in Population
  Top 100 in Pop. Change
  Top 10 in Pop. Change

            

NOTE 1: For a recordset, use: rs.open SQL,"DSN=gl222;UID=gl222;PWD=22222;". For a connection, use: cn.open "gl222","gl222","22222"
NOTE 2: Do NOT write main