FrontPage Dynamic Drop Down

by frontpage

Dynamic Drop Down…

Populate drop down boxes from your database based on your selections. Hide items until they are ready to be displayed…

This example is using all ASP code. The only thing we did with FrontPage is create a database connection. FrontPage then created the global.asa file and the connection string. So we called that string for our connection.

There are a couple of things of note going on:
1. At first you see only one drop down box which is populated from the database. We are using the Northwind database and the supplier table. The second drop down will not show until you make a selection from the first and the results won’t show until you make a selection from the second.

Choose a Country

2. Notice that the drop downs maintain the selections you made. This was accomplished simply by calling the request.form method and setting this as the default value for the form field. You could get a little more elaborate with this if you wanted to use an IF THEN statement.

3. Finally, the results have a filter on them as well. Some of the records in the database do not have Fax numbers. If that field is blank, we don’t display it. This would become more apparently useful if some of the records did not contain a Phone number for example. Then the space that would be taken up by that line is eliminated. Anyone who has done something similar to this in FrontPage will understand that displaying a space for a non existent record can be ugly.

<!-- Dynamic Drop Down. A FrontPageHowTo.com ASP Example -->
<!-- Copy and paste this code into a blank page in FP between the body tags
Import the FpNWind.mdb into your web and set up a database connection called Sample to it -->
<!-- Start First Drop Down -->
<%
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.Open Application("sample_connectionstring")
Set objRS = objDC.Execute("Select Distinct Country FROM Suppliers")
%>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
<tr>
<td width="33%">&nbsp;</td>
<td width="47%">
<FORM METHOD="POST" NAME="Form1" ACTION="our_supplier_in.asp">
<p align="left">
<SELECT NAME="Country" SIZE="1" ONCHANGE=Form1.submit()>
<option selected><% = Request.Form("Country") %></option>
<%
' Continue until we get to the end of the recordset.
Do Until objRS.EOF
' For each record we create a option tag and set it's value to the countr
%>
<OPTION><%= objRS("Country") %></OPTION>
<%
' Get next record
objRS.MoveNext
Loop
%>
</SELECT> <b><font face="Arial" size="2" color="#008080">Choose a Country</font></b></p>
</FORM>
<%
' Close Data Access Objects and free DB variables
objRS.Close
Set objRS = Nothing
objDC.Close
Set objDC = Nothing
%>
<!-- End first Drop Down -->
<!--Second drop down -->
<%
'Some code to hide the second drop down until we make a selection from the first
IF Request.Form("Country") = "" Then
Else
'If Country has a value then we get a list of cities for the second drop down
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.Open Application("sample_connectionstring")
Set objRS = objDC.Execute("Select City FROM Suppliers WHERE Country = '" & Request.Form("Country") & "'")
%>
<FORM METHOD="POST" NAME="Form2" ACTION="our_supplier_in.asp">
<p align="left">
<font face="Arial"><font color="#008080"><b>
<SELECT NAME="City" SIZE="1" ONCHANGE=Form2.submit()>
<option selected><% = Request.Form("City") %></option>
<%
' Continue until we get to the end of the recordset.
Do Until objRS.EOF
' For each record we create a option tag and set it's value to the city
%>
<OPTION><%= objRS("City") %></OPTION>
<%
' Get next record
objRS.MoveNext
Loop
%>
<%
'Set a hidden value in the second form for the Country
'so we can pass it along with the city to the next query
%>
</SELECT></b></font><b><font size="2" color="#008080"> Choose a City</font></b><font color="#008080"><b><input type="hidden" name="Country" value="<% = Request.Form("Country") %>"></b></font></font></p>
</FORM>
<%
' Close Data Access Objects and free DB variables
objRS.Close
Set objRS = Nothing
objDC.Close
Set objDC = Nothing
End IF
%>
<!-- Display the records -->
</td>
<td width="20%"></td>
</tr>
</table>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber2">
<tr>
<td width="33%">&nbsp;</td>
<td width="47%">
<%
'Make sure we have submitted a city and don't show results until we do
IF Request.Form("city") = "" Then
Else
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.Open Application("sample_connectionstring")
Set objRS = objDC.Execute("Select * FROM Suppliers WHERE Country = '" & Request.Form("Country") & "' AND City = '" & Request.Form("city") & "'")
'Loop through the database and assign the appropriate values to variables
'that we will use later
Do Until objRS.EOF
CompanyName = objRS("CompanyName")
ContactName = objRS("ContactName")
Address = objRS("Address")
City = objRS("City")
Region = objRS("Region")
Zip = objRS("PostalCode")
Phone1 = objRS("Phone")
Fax1 = objRS("Fax")
Country = objRS("country")
objRS.MoveNext
Loop
objRS.Close
Set objRS = Nothing
objDC.Close
Set objDC = Nothing
%>
</div>
<p align="left"><b><font face="Arial" size="2" color="#008080">Our Supplier in: </font>
<font face="Arial" size="2" color="#800000"> <% Response.Write City & ", " & Country %> </font></b>
<br>
<font face="Arial" size="1" color="#008080">
<%
'Set up the display of the record
Response.Write CompanyName & "<br>"
Response.Write ContactName & "<br>"
Response.Write Address & "<br>"
Response.Write City & ", " & Region & " " & Zip & "<br>"
Response.Write "Phone: " & Phone1 & "<br>"
IF Fax1 <> "" Then
Response.Write "Fax: " & Fax1 & "<br>"
End IF
End IF
%>
</font>
</p>
</td>
<td width="20%">&nbsp;</td>
</tr>
</table>