CSCI 4230
Internet Applicaiton Development
Fall 2000
Suugested Solution to Homework #8

(1)    For example:

<html>
<head>
   <!--  h8.asp  -->
   <title>Homework #8, CSCI 4230, Fall 2000</title>
<% ' Porting constants
 DSN = "yue"

 ' Get HTTP parameter
 Function GetParameter(Param)
  If (Request.ServerVariables("REQUEST_METHOD") = "POST") Then
   GetParameter = Request.Form(Param)
  Else
   GetParameter = Request.QueryString(Param)
  End If
 End Function

 ' Open database connection
    set conn = Server.createObject("adodb.connection")
    conn.open DSN

 ' Show the result of a sql statement.
 Function showResult(cond)
  sql = "select LastName, FirstName, URL, Program " & _
        "from f00h8 " & cond
  result = ""
  count = 0

  Set ResultRS = conn.execute(sql)
  if ResultRS.eof then
   Response.write "Sorry, there is no match.  Please try again."
  else
   ' Not efficient
   do while not ResultRS.eof
    count = count + 1
    result = result + "<a href='" & ResultRS.fields("URL") & _
             "'>" + ResultRS.fields("FirstName") & " " & _
       ResultRS.fields("LastName") & "</a>: " & _
       ResultRS.fields("Program") & ".<br>" & vbLfCR
                 ResultRS.movenext
            loop
   if count > 1 then
    Response.write "There are " & count & " matches.<p>" & _
          vbLfCr & result
   else
    Response.write "There is " & count & " match.<p>" & _
          vbLfCr & result
   end if
  end if
 End Function

    '   Get self url
 selfURL = "http://" & Request.ServerVariables("HTTP_HOST") & Request.ServerVariables("URL")
%>
<style>
h2, h3 { color:#3333ff;
     font-family: Arial
}
body {
   font-family:bookman old style, times new roman, times;
}
</style>
</head>
<body bgcolor="#ccccff">
<h2>Faculty Directory</h2>
This is the faculty directory of School of NAS.
<%
 ' Get parameters.
 fchar = GetParameter("fchar")
 program = GetParameter("program")
 key = GetParameter("key")

 if (key <> "") then
  Response.write "<h3>Search Result " & _
        "for last name with substring " & _
                 "<span style='color:green'>" & key & "</span>:</h3>"
        cond = "where LastName like '%" & key & "%' order by LastName"
  showResult(cond)
 elseif (fchar <> "") then
  Response.write "<h3>Search Result " & _
        "for last name with first character " & _
                 "<span style='color:green'>" & fchar & "</span>:</h3>"
        cond = "where LastName like '" & fchar & "%' order by LastName"
  showResult(cond)
 elseif (program <> "") then
  Response.write "<h3>Search Result " & _
        "for the program " & _
                 "<span style='color:green'>" & program & "</span>:</h3>"
        cond = "where Program = '" & program & "' order by LastName"
  showResult(cond)
 else
  Response.write "Please use the following form to search."
 end if
%>

<!--  show search form -->
<p>
<hr>
<h3>Search Form</h3>

<!--  Search by keyword -->
<form method="get">
Last name: &nbsp;&nbsp;
<input type="text" size="20" maxlength="50" name="key">
&nbsp;&nbsp;&nbsp;&nbsp;
<input type="submit" value="Search Last Name!">
</form>

<!--  Search by first char of last name  -->
<p>
Select the first character of the last name:
<p>
<%
 Dim chars
 chars = Array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z")
 for each ch in chars
  Response.write "<a href='" & selfURL & "?fchar=" & ch & "'>" & ch & "</a>&nbsp;&nbsp;"
 next ' ch
%>
<p>
<%
 sql= "select distinct program from f00h8"
 set result = conn.execute(sql)
%>

<form method="get">
Select programs: &nbsp;
<select name="program">
<% do while not result.eof %>
  <option value="<% =result.fields("program") %>"><% =result.fields("program") %>
<%  result.movenext
 loop
 conn.close
%>
</select>
&nbsp;&nbsp;&nbsp;&nbsp;
<input type=submit value="Search Program!">
</form>
</body>
</html>