Solved

Dependent Dropdown List on Select Distinct Field

Posted on 2006-10-31
5
342 Views
Last Modified: 2012-06-27
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>
0
Comment
Question by:fgict
  • 2
5 Comments
 
LVL 6

Expert Comment

by:Dragonlaird
ID: 17841913
Firstly, I suspect you're trying to populate the Address drop-down on-the-fly, so that if someone changes the city, the list of addresses is automatically re-populated with the list for that city only?

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.href=location.pathname+'?City='+escape(this.options[this.selectedIndex].value);">
<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.Recordset")
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")=Request.QueryString("City") Then
    ' If the user has already select a city, display it as the current value
    Response.Write " selected"
  End If
%>><%=Server.HTMLEncode(rsdropdown2.Fields("city"))%></option>
<%
  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.Recordset")
  strSQL6 = "SELECT DISTINCT TBLListings.Address FROM TBLListings WHERE TBLListings.Display = 'y' And TBLListing.City = " & Request.QueryString("City") & "' ORDER BY TBLListings.Address ASC;"
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.HTMLEncode(rsdropdown2.Fields("Address"))%></option>
<%
      rsdropdown2.MoveNext
    Wend
  End If
  Set rsdropdown2 = Nothing
%></select>
<!--
 Now add the rest of the form as above...
-->
</form>
0
 
LVL 1

Author Comment

by:fgict
ID: 17847783
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.Connection")
  strConn="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("myrealestate.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%">&nbsp;</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.Connection")
    strConn="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("myrealestate.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><font 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%">&nbsp;</td>
<td width="47%">
 </td>
<td width="20%">&nbsp;</td>
</tr>
</table>
0
 
LVL 6

Accepted Solution

by:
Dragonlaird earned 500 total points
ID: 17849078
Rather than use two forms, why not just populate a single form and re-draw it with the relevant options selected?

One of the problems with the method you used above, you're still referring to the City parameter when populating the Address combo... Try the code below and let me know if it works...

You will also notice that the Results table at the bottom of the page should only appear if both City and Address have been selected...

<%
  Set objDC = Server.CreateObject("ADODB.Connection")
  strConn="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("myrealestate.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%">&nbsp;</td>
<td width="47%">
<FORM METHOD="POST" NAME="Form1" ACTION="city.asp">
<!-- Start first Drop Down -->
<p align="left">
<SELECT NAME="City" SIZE="1" ONCHANGE=Form1.submit()>
<option value=""<%
      If Request.Form("City") = "" Then
            Response.Write " select"
      End If
%>>Choose a City</option>
<%
  ' Continue until we get to the end of the recordset.
      While Not objRS.EOF
  ' For each record we create a option tag and set it's value to the city
  %>
<option<%
            If objRS.Fields("City") = Request.Form("City") Then
                  Response.Write " selected"
            End If
%>><%= Server.HTMLEncode(objRS.Fields("City")) %></option>
<%
  ' Get next record
            objRS.MoveNext
      Wend
 %>
</SELECT> <b><font face="Arial" size="2" color="#008080">Choose a City</font></b></p>
<%
      objRS.Close
      Set objRS = Nothing
  ' Close Data Access Objects and free DB variables
    objRS.Close
    Set objRS = Nothing
      Set objRS = objDC.Execute("Select distinct Address FROM TBLListings WHERE City = '" & Request.Form("City") & "'")
%>      <!-- End first Drop Down -->
      <!-- Start second Drop Down -->
<p align="left">
<SELECT NAME="Address" SIZE="1" ONCHANGE=Form1.submit()>
<option value=""<%
      If Request.Form("Address") = "" Or Request.Form("City") = "" Then
            Response.Write " select"
      End If
%>>Choose an Address</option>
<%
  ' Continue until we get to the end of the recordset.
      While Not objRS.EOF
  ' For each record we create a option tag and set it's value to the Address
  %>
<option<%
            If (objRS.Fields("Address") = Request.Form("Address")) And Request.Form("City") <> "" Then
                  Response.Write " selected"
            End If
%>><%= Server.HTMLEncode(objRS.Fields("Address")) %></option>
<%
  ' Get next record
            objRS.MoveNext
      Wend
  %>
</SELECT> <b><font size="2" color="#008080">Choose an Address</font></b></p>
<%
    objRS.Close
    Set objRS = Nothing
    objDC.Close
    Set objDC = Nothing
%>
  <!-- End second Drop Down -->
  </form>
</td>
<td width="20%">&nbsp;</td>
</tr>
</table>
<%
      If Request.Form("City") <> "" And Request.Form("Address") <> "" Then
%>
  <!-- Display the records; City and Address have been populated -->
<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%">
 </td>
<td width="20%">&nbsp;</td>
</tr>
</table>
<%
      End If
%>
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now