Solved

Dependent Dropdown List on Select Distinct Field

Posted on 2006-10-31
5
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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/…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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