FP2000: How to Add an All Values Item to a Database Query (244968)


The information in this article applies to:

  • Microsoft FrontPage 2000
This article was previously published under Q244968

For a Microsoft FrontPage 2002 version of this article, see 293169.

SUMMARY

This article describes how to add an "All Values" item to a database query by using the FrontPage Database Results Wizard.

The Database Results Wizard uses Active Server Pages (ASP) on a Web server running Microsoft Internet Information Services (IIS) to search a database. This article describes how to create an All Values search item for querying a database. Because the process is slightly different depending on whether you want to search a text field or a numeric field, this article presents both techniques in separate examples.

MORE INFORMATION

Adding an All Values Item to a Text Field Query

You can perform a search of all text fields in a database by using a single wildcard character (%). From FrontPage, you can add an All Values option to a drop-down list of query values. To add an All Values item to a text field query, follow these steps:
  1. Open a Web on a Web server running IIS and create a new page.
  2. On the Insert menu, point to Database, and then click Results. In the wizard, perform the following steps:
    1. Click Use a sample database connection (Northwind) and then click Next.
    2. Click Record Source. In the Record Source list, click Products. Click Next.
    3. Click More Options. In the More Options dialog box, click Criteria.
    4. In the Criteria dialog box, click Add.
    5. In the Add Criteria dialog box, make the following selections:
      1. In the Field Name list, click ProductName.
      2. In the Comparison list, click Contains.
      3. Click to select the Use this search form field check box.
      4. Click OK.
    6. Click OK two more times to return to step 3 in the Database Results Wizard.
    7. Click Next two times, and then click Finish.
  3. Select the text box next to ProductName and press DELETE.
  4. Add a drop-down box with a list of search values. To do this, use either of the following methods:

    To create a search list manually:

    1. On the Insert menu, point to Form, and then click Drop-Down Menu.
    2. Double-click the drop-down box. In the Name box, type ProductName.
    3. Click Add. In the Add Choice dialog box, follow these steps:
      1. In the Choice box, type All Values.
      2. Click to select the Specify Value check box.
      3. In the Specify Value box, type %.
      4. Under Initial State, click Selected.
      5. Click OK.
    4. Repeat step c to add the following additional choices, values, and states to the drop-down list:

      Choice             Value            Initial State
-------------------------------------------------
Chai               Chai             Not Selected 
Chang              Chang            Not Selected 
Aniseed Syrup      Aniseed Syrup    Not Selected

    5. Click OK to close the Drop-Down Menu Properties dialog box.
    To create a search list dynamically:
    1. On the Insert menu, point to Database, and then click Results. In the wizard, perform the following steps:
      1. Click Use a sample database connection (Northwind) and then click Next.
      2. Click Record Source. In the Record Source list, click Products. Click Next.
      3. Click Edit List. In the Displayed Fields list, select all fields except ProductName, and then click Remove. Click OK.
      4. Click Next.
      5. In the Choose formatting options for the records returned by the query list, click Drop-down list - one record per item, and then click Next.
      6. Click Finish.
    2. Double-click the drop-down box. In the Name box, type ProductName.
    3. Click Add. In the Add Choice dialog box, follow these steps:
      1. In the Choice box, type All Values.
      2. Click to select the Specify Value check box.
      3. In the Specify Value box, type %.
      4. Under Initial State, click Selected.
      5. Click OK.
    4. In the Drop-Down Menu Properties dialog box, select All Values entry and click the Move Up button to move the selection to the top of the list.
    5. Click OK to close the Drop-Down Menu Properties dialog box.
  5. Save the page as Alltextvalues.asp.
  6. Preview the page in a Web browser using HTTP.
  7. In the ProductName list, click Chai, and then click Submit Query. All of the items in the database containing Chai in the product name will be displayed.
  8. To search for all of the products, select All Values, and then click Submit Query. All of the product names in the database will be displayed.

Adding an All Values Item to a Numeric Field Query

If you want to search all numeric fields in a database, you cannot use wildcard characters. You can, however, use a logic operation to achieve the same results. To add an All Values item to a numeric field query, follow these steps:
  1. Open a Web on a Web server running IIS and create a new page.
  2. On the Insert menu, point to Database, and then click Results. In the wizard, perform the following steps:
    1. Click Use a sample database connection (Northwind) and then click Next.
    2. Click Record Source. In the Record Source list, click Products. Click Next.
    3. Click More Options. In the More Options dialog box, click Criteria.
    4. In the Criteria dialog box, click Add.
    5. In the Add Criteria dialog box, make the following selections:
      1. In the Field Name list, click ProductID.
      2. In the Comparison list, click Equals.
      3. Click to select the Use this search form field check box. (The check box is selected by default.)
      4. Click OK.
    6. Click OK.
    7. In the More Options dialog box, click Defaults. In the Input Parameters list, click ProductID, and then click Edit.
    8. In the Value box, type 1 OR 1 and click OK.
    9. Click OK two more times to return to Step 3 of the Database Results Wizard.
    10. Click Next two times, and then click Finish.
  3. Select the form field text box next to ProductID and press DELETE.
  4. Add a drop down box with a list of search values. To do this, use either of the following methods.

    To create a search list manually:

    1. On the Insert menu, point to Form, and then click Drop-Down Menu.
    2. Double-click the drop-down box. In the Name box, type ProductID.
    3. Click Add. In the Add Choice dialog box, make the following selections:
      1. In the Choice box, type All Values.
      2. Click to selct the Specify Value check box.
      3. In the Specify Value box, type 1 OR 1.
      4. In the Initial State section, click Selected.
      5. Click OK.
    4. Repeat step c to add the following additional choices, values, and states to the drop-down list:
      Choice            Specify Value         Initial State 
-----------------------------------------------------
Chai               1                    Not Selected 
Chang              2                    Not Selected 
Aniseed Syrup      3                    Not Selected
    5. Click OK to close the Drop-Down Menu Properties dialog box.
    To create a search list dynamically:
    1. On the Insert menu, point to Database, and then click Results. In the wizard, perform the following steps:
      1. Click Use a sample database connection (Northwind) and then click Next.
      2. Click Record Source. In the Record Source list, click Products. Click Next.
      3. Click Edit List. In the Displayed Fields list, select all fields except ProductID and ProductName, and click Remove. Click OK. Click Next.
      4. In the Choose formatting options for the records returned by the query list, click Drop-down list - one record per item. In the Display values from this field list, click ProductName. In the Submit values from this field list, click ProductID. Click Next.
      5. Click Finish.
    2. Double-click the drop-down box. In the Name box, type ProductID.
    3. Click Add. In the Add Choice dialog box, follow these steps:
      1. In the Choice box, type All Values.
      2. Click to select the Specify Value check box.
      3. In the Specify Value box, type 1 OR 1.
      4. Under Initial State, click Selected.
      5. Click OK.
    4. In the Drop-Down Menu Properties dialog box, select the All Values entry and click the Move Up button to move the selection to the top of the list.
    5. Click OK to close the Drop-Down Menu Properties dialog box.
  5. Save the page as Allnumericvalues.asp.
  6. Preview the page in a Web browser.
  7. In the ProductID list, click Product One, and then click Submit Query. All of the items in the database containing the number one in their Product ID will be displayed.
  8. To search for all products, select All Values, and then click Submit Query. All of the product IDs in the database will be displayed.

REFERENCES

For more information about working with databases, click Microsoft FrontPage Help on the Help menu, type databases in the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:7/19/2001
Keywords:kbinfo KB244968
©2002 Microsoft Corporation. All rights reserved.