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.