How to add an All Values item to a database query by using the FrontPage 2003 Database Results Wizard

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

The Database Results Wizard uses Active Server Pages (ASP) on a Web server that is 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 if you want to search a text field or a numeric field, this article presents both techniques in separate examples.

Note The following procedures are for use with a FrontPage Server Extensions-based Web server. The steps to perform these procedures with a Web site based on Microsoft Windows SharePoint Services (WSS) are beyond the scope of this article. Web sites that are located on servers that are running on Windows SharePoint Services use a different set of features to display data.

For more information about Windows SharePoint Services, see the Microsoft Windows SharePoint Services 2.0 Administrator's Guide.

The following file is available for download from the Microsoft Download Center:
Download the Windows SharePoint Services Administrator's Guide package now. For more information about how to download Microsoft support files, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help prevent any unauthorized changes to the file.
For more details about Windows SharePoint Services, visit the following Microsoft Web site:

Add 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 that is running IIS and create a new page named Alltextvalues.asp.
2. On the Insert menu, point to Database, and then click Results.
   
   Note If the Database option is not available on the Insert menu, verify that the authoring computer is not behind a firewall or using an encrypted channel or VPN that may interfere with the communication to the server.
   
   For more information about how to troubleshoot this problem, click Microsoft FrontPage Help on the Help menu, type Troubleshoot database integration with ASP or ASP.NET in the Answer Wizard, and then click Search to view the topic.
   
   In the wizard, follow these steps:
   1. Click Use a sample database connection (Northwind), and then click Next.
   2. Click Record Source.
      
      In the Record Source list, click Products, and then 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 that is next to ProductName, and then press DELETE.
4. Add a drop-down box with a list of search values. To do this, use either of the following methods.
   
   Create a search list manually
   1. On the Insert menu, point to Form, and then click Drop-Down box.
   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 Box Properties dialog box.
   Create a search list dynamically
   1. On the Insert menu, point to Database, and then click Results. In the wizard, follow these steps:
      1. Click Use a sample database connection (Northwind), and then click Next.
      2. Click Record Source.
         
         In the Record Source list, click Products, and then 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 Box Properties dialog box, click the All Values entry, and then click the Move Up button to move the selection to the top of the list.
   5. Click OK to close the Drop-Down Box Properties dialog box.
5. Save the page as Alltextvalues.asp.
6. Preview the page in a Web browser by using HTTP.
7. In the ProductName list, click Chai, and then click Submit Query.
   
   All the items in the database that contain Chai in the product name will appear.
8. To search for all the products, click All Values, and then click Submit Query.
   
   All the product names in the database will appear.

Add 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. However, you can use a logic operation to get 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 that is running IIS and create a new page that is named Allnumericvalues.asp.
2. On the Insert menu, point to Database, and then click Results. In the wizard, follow these steps:
   1. Click Use a sample database connection (Northwind), and then click Next.
   2. Click Record Source.
      
      In the Record Source list, click Products, and then 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. (By default, this check box is selected.)
      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 then 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 then press DELETE.
4. Add a drop down box with a list of search values. To do this, use either of the following methods.
   
   Create a search list manually
   1. On the Insert menu, point to Form, and then click Drop-Down box.
   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 select 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 Box Properties dialog box.
   Create a search list dynamically
   1. On the Insert menu, point to Database, and then click Results. In the wizard, follow these steps:
      1. Click Use a sample database connection (Northwind), and then click Next.
      2. Click Record Source.
         
         In the Record Source list, click Products, and then click Next.
      3. Click Edit List.
         
         In the Displayed Fields list, select all fields except ProductID and ProductName, and then click Remove. Click OK, and then 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, and then 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 Box Properties dialog box, select the All Values entry, and then click the Move Up button to move the selection to the top of the list.
   5. Click OK to close the Drop-Down Box 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 the items in the database that contain the number one in their Product ID will appear.
8. To search for all products, click All Values, and then click Submit Query.
   
   All the product IDs in the database will appear.

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.