Saving the Active Server Page

Before you can use the formatted results page, you must save it as an Active Server Page, which is a file with an .asp extension.

On the FrontPage Editor’s File menu, choose Save As. In the Save As dialog box, type the file name for this page is the URL field, but be sure to retain the .asp file extension. For this example, name the page “results.asp.” In the Title field, enter a descriptive page title, such as “Database Query Results,” then click OK to save the page.

You must also enable the folder containing the results page to run queries and Active Server Page code when the page is fetched. To do this, select the Folders View in the FrontPage Explorer, then click the folder that contains the “results.asp” page. On the FrontPage Explorer’s Edit menu, choose Properties. In the folder’s Properties dialog box, select the Allow scripts to be run or Allow scripts or programs to be run option and click OK.

Note Allowing programs to be run inside your FrontPage web may make your Web server less secure. Check with your server administrator or Internet Service Provider before allowing programs to be run.

Preparing for Criteria

You will now modify the query to accept search criteria. To filter the query by specific criteria, each database field value must be entered into the query’s SQL. For example, the Sales by Category query used in the Northwind Traders example includes all products. It is more useful to be able to view only those products in the category the user is interested in seeing, such as a list of products with the classification “Beverages.” In order to achieve this, a criteria parameter is added to the category name.

SQL queries support criteria by using WHERE statements to restrict what database records are returned. You must edit the SQL text to contain a WHERE statement that references the name of a database field in an HTML form. This form will be used to enter a parameter value, which will be sent to the query.

In this example, we will define the form field to be the product category. Entering a category into the form field will return product name and product sales information for all products in the specified category.

Û To Add a Parameter Form Field for the Product Category

  1. On the results page, right-click the placeholder icon for the database region and choose Database Region Properties from the shortcut menu.

  2. Click Next to proceed to the Enter the SQL String for the Query input field, then locate the WHERE statement in the SQL text.

    If you followed the previous examples by pasting the SQL query from Microsoft Access, the text should read:

    WHERE (((Orders.OrderDate) Between #1/1/95# And #12/31/95#))
  3. Add an additional criteria value by manually editing the WHERE statement, as shown below. Be sure to include the single quote where shown:
    WHERE ((Categories.CategoryName = '((Orders.OrderDate) Between #1/1/95# And #12/31/95#))
  4. Next, position the insertion point after the single quote you just typed and click the Insert Form Field Parameter button.

  5. Type a name for the form field that you are going to create.

    To avoid confusion, it is helpful to use a form field name that is similar or the same as the database field name. For this example, type in CategoryName. Make a note of the name that you have chosen since you will use it later.

  6. Click OK.

    The SQL text will be automatically modified to include the field name:

    WHERE ((Categories.CategoryName = '%%CategoryName%% ((Orders.OrderDate) Between #1/1/95# And #12/31/95#))
  7. Complete the criteria by typing a closing single quote and an AND operator after the parameter that was added in the previous step:
    WHERE ((Categories.CategoryName = '%%CategoryName%%') AND ((Orders.OrderDate) Between #1/1/95# And #12/31/95#))
  8. Click Finish to apply the changes.

When you are finished modifying the query, save your changes to the page. Remember to save the page as an Active Server Page by retaining the .asp file extension.

Note Some database management tools may generate SQL text with double quotes. If you are pasting SQL text containing double quotes into the Database Region Wizard, you will need to manually change them to single quotes before closing the wizard.