fgict
asked on
Dependent Dropdown List on Select Distinct Field
Hi all,
I am building a search function on a real estate website. I have the search working for searching "City" and/or Category.
However, I now wish to add a dependent dropdown underneath city, so that when i choose city it displays the address relevant to this.
All the listings are held in the tblListing e.g.
Table Structure
"ID" "ReferenceNo" "Added" "Price" "City" "Description" "Address" "Features" "Category" "ProvinceState" "PostalZip" "Image1URL"
Example Entry in Table
204 "2" 08/04/2003 0 "My City" "Description of Property" "My Suburb" "Features of Property" "Sale Agreed" "My State" "n/a" "/images/sold.jpg"
So the dropdowns should be:
<city> - working
<address> - need to get working
<category> - working
<form method="POST" action="searchmain.asp">
<select size="1" name="area">
<option selected>Select An Area</option>
<%
Set rsdropdown2 = Server.CreateObject("ADODB .Recordset ")
strSQL6 = "SELECT DISTINCT TBLListings.city FROM TBLListings WHERE TBLListings.Display = 'y' ORDER BY TBLListings.city ASC;"
rsdropdown2.Open strSQL6, strcon6
IF rsdropdown2.EOF THEN
Response.Write("")
ELSE
DO UNTIL rsdropdown2.EOF
%>
<option><%= rsdropdown2("city") %></option>
<%
rsdropdown2.MoveNext
LOOP
END IF
Set rsdropdown2 = Nothing
adoCon6.Close
Set adoCon6 = Nothing
%>
<option value="all">All Areas</option>
</select><br>
<select size="1" name="cat" style="margin-top: 5px;">
<option selected>Select A Category</option>
<%
Set rsdropdown = Server.CreateObject("ADODB .Recordset ")
strSQL5 = "SELECT TBLListingNav.* FROM TBLListingNav ORDER BY TBLListingNav.ID;"
rsdropdown.Open strSQL5, strcon5
IF rsdropdown.EOF THEN
Response.Write("")
ELSE
DO UNTIL rsdropdown.EOF
%>
<option><%= rsdropdown("cName") %></option>
<%
rsdropdown.MoveNext
LOOP
END IF
Set rsdropdown = Nothing
adoCon5.Close
Set adoCon5 = Nothing
%>
<option value="all">All Categories</option>
</select><br>
<input border="0" src="i/clear.gif" class="search" name="I1" width="61" height="15" vspace="6" type="image"><br>
<a href="searchadvanced.asp"> Advanced Search</a></form>
I am building a search function on a real estate website. I have the search working for searching "City" and/or Category.
However, I now wish to add a dependent dropdown underneath city, so that when i choose city it displays the address relevant to this.
All the listings are held in the tblListing e.g.
Table Structure
"ID" "ReferenceNo" "Added" "Price" "City" "Description" "Address" "Features" "Category" "ProvinceState" "PostalZip" "Image1URL"
Example Entry in Table
204 "2" 08/04/2003 0 "My City" "Description of Property" "My Suburb" "Features of Property" "Sale Agreed" "My State" "n/a" "/images/sold.jpg"
So the dropdowns should be:
<city> - working
<address> - need to get working
<category> - working
<form method="POST" action="searchmain.asp">
<select size="1" name="area">
<option selected>Select An Area</option>
<%
Set rsdropdown2 = Server.CreateObject("ADODB
strSQL6 = "SELECT DISTINCT TBLListings.city FROM TBLListings WHERE TBLListings.Display = 'y' ORDER BY TBLListings.city ASC;"
rsdropdown2.Open strSQL6, strcon6
IF rsdropdown2.EOF THEN
Response.Write("")
ELSE
DO UNTIL rsdropdown2.EOF
%>
<option><%= rsdropdown2("city") %></option>
<%
rsdropdown2.MoveNext
LOOP
END IF
Set rsdropdown2 = Nothing
adoCon6.Close
Set adoCon6 = Nothing
%>
<option value="all">All Areas</option>
</select><br>
<select size="1" name="cat" style="margin-top: 5px;">
<option selected>Select A Category</option>
<%
Set rsdropdown = Server.CreateObject("ADODB
strSQL5 = "SELECT TBLListingNav.* FROM TBLListingNav ORDER BY TBLListingNav.ID;"
rsdropdown.Open strSQL5, strcon5
IF rsdropdown.EOF THEN
Response.Write("")
ELSE
DO UNTIL rsdropdown.EOF
%>
<option><%= rsdropdown("cName") %></option>
<%
rsdropdown.MoveNext
LOOP
END IF
Set rsdropdown = Nothing
adoCon5.Close
Set adoCon5 = Nothing
%>
<option value="all">All Categories</option>
</select><br>
<input border="0" src="i/clear.gif" class="search" name="I1" width="61" height="15" vspace="6" type="image"><br>
<a href="searchadvanced.asp">
ASKER
Hi Dragonlaird,
I tested your code, and eventually populated the dropdown lists correctly with the code below:
However, 2 things that are not quite right about the code:
1. When I select the 2nd Dropdown list for "Address" it posts back and populates both dropdown lists with "City , Address".
I need it to hold the values I have selected.
2. It seems when I include the code on my asp pages as an include file that, it does not produce any html code after the dropdown lists. Why is this? Should I be leaving the connection string open?
Many thanks for your kind help so far :)
--- code ---
<%
Set objDC = Server.CreateObject("ADODB .Connectio n")
strConn="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("myrealest ate.mdb")
objDC.Open strConn
Set objRS = objDC.Execute("Select Distinct City FROM TBLListings WHERE Display = 'y' ORDER BY City ASC;")
%>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
<tr>
<td width="33%"> </td>
<td width="47%">
<FORM METHOD="POST" NAME="Form1" ACTION="city.asp">
<p align="left">
<SELECT NAME="City" SIZE="1" ONCHANGE=Form1.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
%>
</SELECT> <b><font face="Arial" size="2" color="#008080">Choose a City</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("City") = "" Then
Else
'If Country has a value then we get a list of cities for the second drop down
Set objDC = Server.CreateObject("ADODB .Connectio n")
strConn="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("myrealest ate.mdb")
objDC.Open strConn
Set objRS = objDC.Execute("Select distinct Address FROM TBLListings WHERE City = '" & Request.Form("City") & "'")
%><br>
<FORM METHOD="POST" NAME="Form2" ACTION="city.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("Address") %></OPTION>
<%
' Get next record
objRS.MoveNext
Loop
%>
<%
'Set a hidden value in the second form for the City
'so we can pass it along with the address to the next query
%>
</SELECT></b></font><b><fo nt size="2" color="#008080"> Choose an Address</font></b><font color="#008080"><b><input type="hidden" name="City" value="<% = Request.Form("City") %>"></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%"> </td>
<td width="47%">
</td>
<td width="20%"> </td>
</tr>
</table>
I tested your code, and eventually populated the dropdown lists correctly with the code below:
However, 2 things that are not quite right about the code:
1. When I select the 2nd Dropdown list for "Address" it posts back and populates both dropdown lists with "City , Address".
I need it to hold the values I have selected.
2. It seems when I include the code on my asp pages as an include file that, it does not produce any html code after the dropdown lists. Why is this? Should I be leaving the connection string open?
Many thanks for your kind help so far :)
--- code ---
<%
Set objDC = Server.CreateObject("ADODB
strConn="DRIVER={Microsoft
objDC.Open strConn
Set objRS = objDC.Execute("Select Distinct City FROM TBLListings WHERE Display = 'y' ORDER BY City ASC;")
%>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
<tr>
<td width="33%"> </td>
<td width="47%">
<FORM METHOD="POST" NAME="Form1" ACTION="city.asp">
<p align="left">
<SELECT NAME="City" SIZE="1" ONCHANGE=Form1.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
%>
</SELECT> <b><font face="Arial" size="2" color="#008080">Choose a City</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("City") = "" Then
Else
'If Country has a value then we get a list of cities for the second drop down
Set objDC = Server.CreateObject("ADODB
strConn="DRIVER={Microsoft
objDC.Open strConn
Set objRS = objDC.Execute("Select distinct Address FROM TBLListings WHERE City = '" & Request.Form("City") & "'")
%><br>
<FORM METHOD="POST" NAME="Form2" ACTION="city.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("Address") %></OPTION>
<%
' Get next record
objRS.MoveNext
Loop
%>
<%
'Set a hidden value in the second form for the City
'so we can pass it along with the address to the next query
%>
</SELECT></b></font><b><fo
</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%"> </td>
<td width="47%">
</td>
<td width="20%"> </td>
</tr>
</table>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The simplest method to achieve this would be to use a tiny bit of JavaScript in the City combo that forces the page to re-load whenever it's changed, but as part of this re-load, it also submits the newly selected City value so that the page can populate the Address combo.
The down-side to this is, unless you want to write a fair amount of JavaScript and modify the entire ASP page, any other values selected/entered/changed on the page before the City was changed, will be lost and thus, have to be re-entered.
As I said, this is the simplest solution, there are at least two other methods I can think of that appear much cleaner but probably a bit too in-depth for your requirements (the two other methods are AJAX and a Multiple-Form wizard).
So, let's take a look at modifying your form above...
<form method="POST" action="searchmain.asp">
<select size="1" name="area" onChange="window.location.
<option value="All">Select An Area</option>
<%
' Notice the 'onChange' property added to the SELECT above - this forces the page to reload with the currently selected value
Set rsdropdown2 = Server.CreateObject("ADODB
strSQL6 = "SELECT DISTINCT TBLListings.city FROM TBLListings WHERE TBLListings.Display = 'y' ORDER BY TBLListings.city ASC;"
rsdropdown2.Open strSQL6, strcon6
While Not rsdropdown2.EOF
%>
<option<%
If rsdropdown2.Fields("city")
' If the user has already select a city, display it as the current value
Response.Write " selected"
End If
%>><%=Server.HTMLEncode(rs
<%
rsdropdown2.MoveNext
Wend
Set rsdropdown2 = Nothing
%></select>
<select size="1" name="address">
<%
' Now we check to see if a City has been supplied and populate the Address combo as required
Set rsdropdown2 = Server.CreateObject("ADODB
strSQL6 = "SELECT DISTINCT TBLListings.Address FROM TBLListings WHERE TBLListings.Display = 'y' And TBLListing.City = " & Request.QueryString("City"
rsdropdown2.Open strSQL6, strcon6
If rsdropdown2.EOF Then
' No records, ask user to select a City
%><option value="">Select City First</option>
<%
Else
' We have some records, let's display them
While Not rsdropdown2.EOF
%>
<option><%=Server.HTMLEnco
<%
rsdropdown2.MoveNext
Wend
End If
Set rsdropdown2 = Nothing
%></select>
<!--
Now add the rest of the form as above...
-->
</form>