How to filter a database query using two drop down lists
SUMMARY
FrontPage® 2000 and 2002 enable you to create a database query that
results in a list that displays all of the records in a particular
field. This article describes the steps you can use to limit the results
of another database query.
MORE INFORMATION
This article assumes that you have already imported your Access database
into your web, and that FrontPage has created the fpdb folder, put the
database in that folder, and created the global.asa file and the
database connection. Please review How to create a FrontPage Database Connection for more information. For this tutorial, we will be using a database
called autos.mdb that you can download from this site. We are going to name the Database connection "myautos". We
are going to use the "cars" table.
To filter a database query using 2 dropdown lists, follow these
steps:
- Create a new page and save it as drop1.asp.
- On the Insert menu, point to Form, and select Form.
- Delete the Reset button.
- Position your cursor within the form, to the left of the Submit
button.
- On the Insert menu, point to Database, and select Results.
- In step 1 of the wizard, select your database connection
(myautos). Click Next.
- In step 2 of the wizard, select your record source (cars). Click
Next.
- In step 3 of the wizard, select Edit List and remove all of the
fields, except the first field that will be used to query the
database. We are going to use "company" for the first
query. Click OK. Click Next.
- In step 4 of the wizard, click to select Drop-Down List from the
list of formatting options. Click Next.
- In step 5 of the wizard, click Finish.
- Save the page and Preview in Browser. You will notice that each
"company" is displayed in the dropdown list more than
once. To eliminate the duplicates, in FrontPage, switch to HTML
view, and find the SQL for the dropdown, in the gray-colored code.
It should look like this: s-sql="SELECT * FROM cars"
and change it s-sql="SELECT DISTINCT company FROM cars".
Save the changes while still in HTML view. You will notice that once
you've saved your changes they are also made to the maroon colored
code - that is by design. The maroon colored code should never be
touched; it's generated by the gray code at save time.
- Preview in Browser again and now you will see each company only
appear once.
- In FrontPage, in Normal View, right-click on the form, select Form
Properties, Send to other: Custom ISAPI, NSAPI, CGI, or ASP Script.
Click on Options, and set the Action to: drop2.asp. Click OK twice.
You have now set the form in drop1 to post to drop2 when the user
hits the Submit button.
- Save the page.
- Save the page (Save As) as drop2.asp.
- With drop2.asp open, in Normal View, position your cursor in
between the Database Results Region (see Exhibit 1) and the Submit
button.
- On the Insert menu, point to Database, and select Results.
- In step 1 of the wizard, select your database connection
(myautos). Click Next.
- In step 2 of the wizard, select your record source (cars). Click
Next.
- In step 3 of the wizard, select Edit List and remove all of the
fields, except the field that will be used to query the database in
the second dropdown. We are going to use "model" for the
second query. Click OK. Click Next.
- In step 4 of the wizard, click to select Drop-Down List from the
list of formatting options. Click Next.
- In step 5 of the wizard, click Finish.
- Right-click on the form, select Form Properties, Send to other:
Custom ISAPI, NSAPI, CGI, or ASP Script. Click on Options, and set
the Action to: drop3.asp. Click OK. You have now set the form in
drop2 to post to drop3 when the user hits the Submit button.
- Switch to HTML view and find the first SQL statement in the gray
colored code - it will look like this: s-sql="SELECT
DISTINCT company FROM cars" change it to s-sql="SELECT
DISTINCT company FROM cars WHERE (company = '::company::')".
- While still in HTML view, find the second SQL statement in the
gray colored code - it will look like this: s-sql="SELECT *
FROM cars" change it to s-sql="SELECT DISTINCT
model FROM cars WHERE (company = '::company::')"
- Save the changes while still in HTML view. Close drop2.asp
- Create a new page and save it as drop3.asp
- On the Insert menu, point to Database, and select Results.
- In step 1 of the wizard, select your database connection
(myautos). Click Next.
- In step 2 of the wizard, select your record source (cars). Click
Next.
- In step 3 of the wizard, click More Options, Criteria, and click
Add.
- Verify that the Field Names (company and model) and the Value
entries are the same as the ones you are using for your query. Make
sure the "Use this search form field" check box is
selected. Click OK twice.
- In the More Options dialog box, click Defaults. Click Edit. Add
default values for both fields that match the field type. In this
case, because the fields are text fields, you can enter xxxxx. For
number fields, the default value could be all 0's (zeroes) or all
9's.
- Click OK three times.
- In step 4, choose to return the result to a table.
- In step 5, make sure the Add Search Form check box is not
selected. Click Finish.
- Save the page.
- Open drop1.asp and Preview in Browser.