MS FrontPage —

Tutorial: Filtering Database Results

After reading the steps in the last section, you know how to do a bunch of things, including how to create a search box for the data you're displaying using the Database Results Wizard. If you create this search box, a viewer can enter a value she'd like to find in a specific field within the table that appears on your site. But that option isn't particularly friendly, since you're forcing your visitors to type an exact match in the search box in order to get a result. Maybe you'd like to give your visitors a bit more help. For instance, instead of making them guess, you can provide a list of categories or options from which they can choose.

In this tutorial, you'll work with the Northwind sample database that comes with FrontPage and learn how to display a list of products that a viewer can sort by supplier.

Creating the Drop-Down Search Form

First, you'll create the drop-down list of suppliers. To do so:

  1. Create a blank page and insert a form on it.

    Select Insert » Form » Form.

  2. Insert a Database Results Wizard inside the form.

    Select Insert » Database » Results.

  3. In the first Database Results Wizard screen, select ASP and "Use a sample database." Then click Next.

  4. Select the Suppliers table as your data source and click Next.

  5. Edit the fields to display so only CompanyName and SupplierID display. Then click Next.

    You'll use these two fields to create the drop-down list and to query the database. Remove all other fields by clicking Edit Fields, selecting each one, and then clicking Remove. Then click OK.

  6. In the next screen, select "Dropdown list."

  7. In the "Display values" drop-down list, select CompanyName, and in the "Submit values" drop-down list, select SupplierID.

    Doing so ensures that visitors see the CompanyName values, which makes sense to them, while the form submits the SupplierID number, which makes sense to the database.

  8. Click Next, then click Finish.

Creating a Table to Display Database Results

Next, you'll create a second Database Results Wizard, which will display the drop-down list's search results.

  1. Insert a Database Results Wizard on the same page, outside the form you just created.

    Select Insert » Database » Results.

  2. In the first wizard dialog box, select ASP and "Use a sample database." Then click Next.

  3. Select the Products table as your data source and click Next.

  4. Click More Options and click Criteria.

  5. Click Add and select the Supplier ID field.

  6. Turn on the "Use this search form field" checkbox.

  7. Click OK twice to close the open dialog boxes and save your settings.

  8. Within the More Options dialog box, click Defaults, select the SupplierID field, and then click Edit.

  9. Enter a default value of 0 (zero) and click OK three times.

    If you don't specify a default value, the first time your list appears, it'll contain an error message. To avoid this, just enter a value here that doesn't appear as a SupplierID value in the Suppliers table. Zero usually does the trick.

  10. Click Next, choose to display results in a table, and click Next again.

  11. Turn off the "Add search form" checkbox and click Finish.

  12. Save your page and test it in a browser (see Figure 16-11).

Figure 16-11. When a visitor selects a supplier from the drop-down list and clicks Submit, the table below shows all products provided by that company.