ASP Lesson 11: Combo Boxes and Searching

Viewing a list of records.

As we saw in Lesson 11, we can create a table that contains hyperlinks to a record we want to view. We can also accomplish this by creating a Combo Box that we fill dynamically. Rather than passing data in the querystring, as in Lesson 10, we will now pass data as a form element.

Combo Box Search Form (frmCombo.asp)

<!--#include virtual="/includes/adovbs.inc"-->
<!--#include virtual="/includes/subConn.inc"-->
<html>
<head>
<title>Select Student</title>
</head>

<body>
<form action="frmStudent.asp" method="post">
<table border="1">
<tr>
   <td>Student Name:</td>
   <td>
   <%
   sql = "SELECT * " & _
         "FROM gb_student " & _
         "ORDER BY lname"
   openCN
   rs.Open sql, cn

   response.write "<select name=sid size=1>" & vbCrLf
   do while not rs.EOF
      response.write "<option value=" & _
                     rs("sid") &">" & _
                     rs("lname") & ","  & _
                     rs("fname") & "</option>" & vbCrLf
       rs.MoveNext
   loop
   rs.Close
   closeCN
   response.write "</select>" & vbCrLf
   %>
  </td>
</tr>
<tr>
   <td colspan="2">
      <input type="submit" value="Submit">
   </td>
</tr>
</table>
</form>
</body>
</html>

Sample output:

Student Name:
 

Student Record Form (frmStudent.asp):

<!--#include virtual="/includes/subConn.inc"-->
<!--#include virtual="/includes/adovbs.inc"-->
<%
sid=request.form("sid")
sql = "SELECT * FROM Students WHERE sid='" & sid & "'"

openCN
rs.Open sql, cn
%>
<html>

<head>
<title>Student Information</title>
</head>

<body>

<form>
<table bgcolor="#C0C0C0">
<tr>
   <td align="left">strong>Student Information</strong></td>
</tr>
<tr>
   <td align=right>
   <table border=0>
   <tr>
      <td align="right">Student ID:</td>
      <td>
         <input type=text name=txtSID value=<%=rs("sid")%>>
      </td>
   </tr>
   <tr>
      <td align="right">First Name:</td>
      <td>
         <input type=text name=txtFName value=<%=rs("fname")%>>
      </td>
   </tr>
   <tr>
      <td align="right">Last Name:</td>
      <td>
         <input type=text name=txtLName value=<%=rs("lname")%>>
      </td>
   </tr>
   <tr>
      <td align="right">Address:</td>
      <td>
         <input type=text name=txtAddress value=<%=rs("address")%>>
      </td>
   </tr>
   <tr>
      <td align="right">City:</td>
      <td>
         <input type=text name=txtCity value=<%=rs("city")%>>
      </td>
   </tr>
   <tr>
      <td align="right">State:</td>
      <td>
         <input type=text name=txtST value=<%=rs("st")%>>
      </td>
   </tr>
   <tr>
      <td align="right">Zipcode:</td>
      <td>
         <input type=text name=txtZipcode value=<%=rs("zipcode")%>>
      </td>
   </tr>
   </table>
   </td>
</tr>
<tr>
   <td align="center">
      <input type="reset" value="Clear">
      <input type="submit" value="Submit">
   </td>
</tr>
</table>
</center></div>
</form>
</body>
</html>

Apply what you have learned.

Using the search form created in this lesson, and the grade form you have created in previous lessons, modify the grade form to display the students full name, and all their exam scores, based on the student name passed from the search form.