Multiple Drop-Down list box (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

RobM_01 -> Multiple Drop-Down list box (1/1/2004 18:50:04)

I have created a database with 5 columns which include the following:

"Category" "Sub_Category" "Description" "Image" "Photographer"

I have been able to create a form with a Drop-Down list box on a page that will list all the categories just the once (using the distinct parameter) - then by clicking the submit button it will load the results page listing all the records for the category selected.

I would now like to extend this by adding a second Drop-Down list box to list the sub_categories that relate to the category and only listing the items once.
Below is a small section of the table.

"Category"....."Sub_Category"....."Description

"Sport".........."Football".............."David Beckham"
"Sport".........."Football".............."Sven"
"Sport".........."F1"....................."Michael Schumacha"
"Sport".........."Football".............."Beckham Celebration"
"Sport".........."F1"....................."McLarren Pit Stop"
"Exhibition"....."F1"....................."McLarren Promotion"
"Exhibition"....."Science Museum".."James Bond"
"Exhibition"....."British Council"......"innovation with Industry"
"Exhibition"....."British Council"......"innovation in Action"

So, I would like the user to be able to see in the 1st drop down with sport and exhibition listed once then in the second drop down to show F1 and Football again listed only once. Thus further filtering the choice. Submitting on Sport and F1 would result in just two records on the results page.

I've been at this for nearly 12 hours today and my little brain is about to crash can't suss this one out. Anyway, all that I have done so far is with DRW and I have used the diet macro to clean up the code.

any help with this will be greatly appreciated.

Many thanks in advance.

Rob.



BeTheBall -> RE: Multiple Drop-Down list box (1/1/2004 20:28:29)

Welcome to the forum, Rob.

Here is some code I am currently using on one of my pages to do the same thing. Essentially, what you do is create a second form for the second field you want to use. In the form, you insert a dropdown created by the DRW. You then change the SQL for the dropdown to something like:

SELECT DISTINCT Sub_Category FROM YourTable WHERE Category='::Category::'

This code includes a little snippet that hides the second dropdown until the user selects an option from the first dropdown. Assuming your results are on a separate page, the first Form should submit to the page the form is on. The second form will submit to the page your results are on. You must name each form because you will be using the ONCHANGE event which requires that the form have a name. I have modified the code below to show your fields, etc. as best I could. If you are really brave, you may be able to paste the code below into Notepad and then from Notepad to a new page in FP HTML view between the <body> and </body> tags. Then change all the variables that begin "Your. . ." to your actual data and it may just work. Good luck and post again if you get stuck.

<form method="POST" name="Form1" action="YourPage1.asp">

<p><nobr><!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT DISTINCT Category FROM YourTable"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="YourDBConnection"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Category"
fp_sMenuValue="Category"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<select NAME="Category" SIZE="1" ONCHANGE=Form1.submit()>
<option selected><%=Request.Form("Category")%></option>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<option><%=FP_FieldHTML(fp_rs,"Category")%></option>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</select>
</nobr></p>
</form>

<form method="POST" name="Form2" action="YourPage2.asp">

<p><nobr><!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT DISTINCT Sub_Category FROM YourTable WHERE Category = '::Category::'"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="YourDBConnection"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Sub_Category"
fp_sMenuValue="Sub_Category"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<%
IF Request.Form("Category") = "" Then
Else
%>
<select NAME="Sub_Category" SIZE="1" ONCHANGE=Form2.submit()>
<option selected><%=Request.Form("Sub_Category")%></option>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<option><%=FP_FieldHTML(fp_rs,"Sub_Category")%></option>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</select>
</nobr></p>
<input type="hidden" name="Category" value="<% = Request.Form("Category") %>">
</form>
<%End IF%>



Spooky -> RE: Multiple Drop-Down list box (1/1/2004 20:31:39)

Welcome Rob!

Youve made the original form page that shows the filtered drop down list - thats great. Keep that same logic in mind for the following pages.

Form1 POSTS to page 2 (Another DRW page)

Page 2 contains a DRW that expects a criteria (at step 3 more options)
The criteria is the NAME of the dropdown list from page 1 . (It may be called 'categories' - depends what you called it)
Page 2 will use this criteria to filter the dropdown list (again using distinct) to show the subcategories.
This drop down list for example will be NAMED 'SubCat'

This page will in turn post to page 3 to display the next group of records based on the NAME of the list from page 2 (SubCat)



RobM_01 -> RE: Multiple Drop-Down list box (1/2/2004 5:27:55)

Hi Betheball, thank you very much for your reply.

I have now created 3 asp pages, to which I have called
s_cat
s_subcat
s_portfolio

Using the code that you supplied, I assume that half went in s_cat and the rest in s_subcat.

The first file s_cat produced the drop down list with the categories. After selecting a category it loaded the next page s_subcat with an empty dropdown. I'm not sure if I followed your instructions correctly.

the following is the code for each file:

s_cat

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>s_cat</title>
</head>

<body>
<form method="POST" name="Form1" action="s_subcat.asp">

<p><nobr><!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT DISTINCT Category FROM Image_records"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Category"
fp_sMenuValue="Category"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

<select NAME="Category" SIZE="1" ONCHANGE=Form1.submit()>
<option selected><%=Request.Form("Category")%></option>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<option><%=FP_FieldHTML(fp_rs,"Category")%></option>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</select>
</nobr></p>
</form>

</body>

</html>

_______________________________________________


s_subcat

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>s_subcat</title>
</head>

<body><form method="POST" name="Form2" action="s_portfolio.asp">

<p><nobr><!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT DISTINCT Sub_Category FROM Image_records WHERE Category = '::Category::'"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Sub_Category"
fp_sMenuValue="Sub_Category"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<%
IF Request.Form("Category") = "" Then
Else
%>
<select NAME="Sub_Category" SIZE="1" ONCHANGE=Form2.submit()>
<option selected><%=Request.Form("Sub_Category")%></option>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<option><%=FP_FieldHTML(fp_rs,"Sub_Category")%></option>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</select>
</nobr></p>
<input type="hidden" name="Category" value="<% = Request.Form("Category") %>">
</form>
<%End IF%>

</body>

</html>

_______________________________________________


s_portfolio

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>s_portfolio</title>
</head>

<body>

<form BOTID="0" METHOD="POST" action="s_portfolio.asp">
<input type="hidden" name="fpdbr_0_PagingMove" value=" |< ">
</form>
<table width="100%">
<thead>
<tr>
<th ALIGN="LEFT"><b>Category</b></th>
<th ALIGN="LEFT"><b>Sub_Category</b></th>
<th ALIGN="LEFT"><b>Description</b></th>
<th ALIGN="LEFT"><b>Image_File_Name</b></th>
<th ALIGN="LEFT"><b>Photographer_Name</b></th>
</tr>
</thead>
<tbody>
<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Image_records WHERE (Category = '::Category::') ORDER BY Category ASC,Sub_Category ASC"
fp_sDefault="Category="
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=5
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&Category=202&Sub_Category=202&Description=202&Image_File_Name=202&Photographer_Name=202&Photographer_Pic_File_Name=202&Remote_computer_name=202&User_name=202&Browser_type=202&Timestamp=135&"
fp_iDisplayCols=5
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<%=FP_FieldVal(fp_rs,"Category")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Sub_Category")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Description")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Image_File_Name")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Photographer_Name")%></td>
</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>

</body>

</html>

_______________________________________________

Cheers Rob.



BeTheBall -> RE: Multiple Drop-Down list box (1/2/2004 11:52:35)

Did you click the drop-down on page 2? The code would make the first option be blank but if you click it so it drops down, the other options should be there. I actually anticipated that the two dropdowns would be on the same page and then the results on a second page but it should work either way.



RobM_01 -> RE: Multiple Drop-Down list box (1/2/2004 14:24:05)

this time I put all the code into just one page as you have stated s_cat.asp which produces a drop down with the categories, which is fine.
I click on an item from here and a second box appears as it did with the two page version, and same again with nothing in it, no sub categories to choose from. The first form does not appear to populate the second drop down.

Cheers Rob.



RobM_01 -> RE: Multiple Drop-Down list box (1/2/2004 15:40:58)

Hi Spooky,

I tried your method and got it working in 10 mins, brilliant! thanks

Cheers Rob.



BeTheBall -> RE: Multiple Drop-Down list box (1/2/2004 15:51:36)

Post your most recent code and, if you can, a link to the page. I've gone over the code you pasted again and for some reason cannot see a problem with it.



RobM_01 -> RE: Multiple Drop-Down list box (1/2/2004 16:15:24)

I have tried to incorporate some of the code you sent whilst using Spooky's idea as I quite like the way it does not have a submit button and the second drop down appears after selecting an item in the first box, it sort of works but not quite. I'll send a copy of the one that does work but with submit buttons and the one without the buttons that nearly works, if you get what I mean..anyway here goes.


this one now puts the subcats into the second box, but does not display on the results page, which is a bit further..

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Search Category</title>
</head>

<body>

<form BOTID="0" METHOD="POST" action="s_cat.asp" name="Form1">
<p><nobr>
<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT DISTINCT Category FROM Image_records ORDER BY Category"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Category"
fp_sMenuValue="Category"
fp_sColTypes="&Category=202&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<select name="Category" size="1" onchange="Form1.submit()">
<option selected><%=Request.Form("Category")%></option>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<option><%=FP_FieldHTML(fp_rs,"Category")%></option>
<!--#include file="_fpclass/fpdbrgn2.inc"--></select> </nobr></p>
</form>
<form BOTID="0" method="POST" name="Form2" action="s_portfolio.asp">
<p><nobr>
<!--#include file="_fpclass/fpdblib.inc"--><%
fp_sQry="SELECT DISTINCT Sub_Category FROM Image_records WHERE Category = '::Category::'"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Sub_Category"
fp_sMenuValue="Sub_Category"
fp_sColTypes="&Category=202&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%> <%
IF Request.Form("Category") = "" Then
Else
%> <select name="Sub_Category" size="1" onchange="Form2.submit()">
<option selected><%=Request.Form("Sub_Category")%></option>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<option><%=FP_FieldHTML(fp_rs,"Sub_Category")%></option>
<!--#include file="_fpclass/fpdbrgn2.inc"--></select> </nobr></p>
<input type="hidden" name="Category" value="<% = Request.Form("Sub_Category") %>">
</form>
<%End IF%>

</body>

</html>


---------------------------------------------------------------------------------------

This one does works perfectly but with submit buttons


<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>search1</title>
</head>

<body>

<form BOTID="0" METHOD="POST" action="search1.asp" name="Form1">
<p><nobr>
<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT DISTINCT Category FROM Image_records ORDER BY Category"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Category"
fp_sMenuValue="Category"
fp_sColTypes="&Category=202&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<select NAME="Category" SIZE="1">
<option selected value="choose a category">choose a category</option>
<!--#include file="_fpclass/fpdbrgn1.inc"-->

<option><%=FP_FieldHTML(fp_rs,"Category")%></option>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</select></nobr><input TYPE="Submit"></p></form>
<form BOTID="0" METHOD="POST" action="search3.asp" name="Form2">
<p><nobr>
<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT DISTINCT Sub_Category FROM Image_records WHERE Category = '::Category::'"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="Database1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="Sub_Category"
fp_sMenuValue="Sub_Category"
fp_sColTypes="&Category=202&"
fp_iDisplayCols=1
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<select NAME="Sub_Category" SIZE="1">
<option selected value="choose a Sub_Category">choose a sub category</option>
<!--#include file="_fpclass/fpdbrgn1.inc"-->

<option><%=FP_FieldHTML(fp_rs,"Sub_Category")%></option>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</select></nobr><input TYPE="Submit"></p></form>


</body>

</html>

unfortunately this is not on the web just yet, so I cant give you a link to the site / pages to test it.

Cheers Rob.



RobM_01 -> RE: Multiple Drop-Down list box (1/2/2004 17:54:04)

I have tested both files and it appears to be something not quite right with the snipet that does the last bit to submit the query to the results page.

Cheers Rob.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1088867