ADO Queries and Working with Recordsets - Displaying Selected Records Using a Drop Down Menu
(Page 2 of 5 )
One way that you can allow users to select data from your database is through the use of drop-down lists or menus. In the following program we will create a drop-down list containing names that appear in our VictimName column:
<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("/db/chucknorrisvictims.mdb"))
set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT DISTINCT VictimName FROM Victims"
rs.Open sql,conn
victimname=request.form("victimname")
%>
<form method="post">
Please Select a Name from the List <select name="victimname">
<% do until rs.EOF
response.write("<option")
if rs.fields("victimname")=victimname then
response.write(" selected")
end if
response.write(">")
response.write(rs.fields("victimname"))
rs.MoveNext
loop
rs.Close
set rs=Nothing %>
</select>
<input type="submit" value="Click for Victim Information">
</form>
<%
if victimname<>"" then
sql="SELECT VictimName,VictimInjury,HowItHappened FROM Victims WHERE victimname='" & victimname & "'"
set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn
%>
<table width="100%" cellspacing="0" cellpadding="2" border="1">
<tr>
<th>VictimName</th>
<th>VictimInjury</th>
<th>HowItHappened</th>
</tr>
<%
do until rs.EOF
response.write("<tr>")
response.write("<td>" & rs.fields("victimname") & "</td>")
response.write("<td>" & rs.fields("victiminjury") & "</td>")
response.write("<td>" & rs.fields("howithappened") & "</td>")
response.write("</tr>")
rs.MoveNext
loop
rs.close
conn.Close
set rs=Nothing
set conn=Nothing%>
</table>
<% end if %>
</body>
</html>
This program will create a drop down menu with a list of names from the Victims database, and a submit button. When the user selects a name from the list and clicks on the button, it will return that record and any data pertaining to it. Let's say the user selects the name Ralph Macchio. Here is the result:
Ralph Macchio | Broken Foot | Ralph Macchio tried his crane kick on Chuck. When it connected to Chuck's powerful chest hairs, his foot shattered. |
Next: Sorting Records >>
More BrainDump Articles
More By James Payne
