MS FrontPage —

The Database Results Wizard

Now you know how to gather data with a Web page and save it to a database. But what if you've already got a database and your mission is to share it with the world? FrontPage can help you display parts of a database on your Web pages.

Say a group of scientists you work with have been collecting beetle profiles for years, and you're responsible for posting this information on your museum's intranet site. What with the new boll weevil collection, they're up to 11,678 specimens. Are you really going to create a Web page for each one?

Probably not. Since you're dealing with scientists (who are usually smart, rational, and efficient), they've probably stored all that beetle profile information in a database. If you get your hands on that database, you can save yourself a ton of work. FrontPage can help you create Web pages that will display whatever parts of the database you want visitors to see.

Sounds complicated, but FrontPage makes it easy with its Database Results Wizard. This tool lets you query a database for information and display whatever records you specify on a Web page.

The Database Results Wizard works only on a Web server loaded with FrontPage Sever Extensions. This feature doesn't work with SharePoint.

  1. Create a new blank page.

  2. Initiate the Database Results Wizard.

    Select Insert » Database » Results. The Database Results Wizard displays its first dialog box, which offers the following options:

    • FrontPage has detected that your page will display best using. In this section, you tell FrontPage what program to use to display your database information. FrontPage pretends to take a guess here, but it actually "detects" nothing. You must select either the ASP or ASP.NET radio button. Make sure the one you choose is available on your Web server. ASP is the more common and readily available of the two.

    • Use a sample database. If you're testing out the capabilities of the Database Results Wizard, you can use the sample Northwind database (Working with Databases) that comes with FrontPage. The program creates the database connection for you.

    • Use an existing database. Select this option if you have a database whose results you'd like to displayand you've already created a connection to it in FrontPage.

    • Use a new database connection. Click this if you need to create the connection to the database you want to display. (See the previous section for details on creating a database connection.)

    Once you've made your selections, click Next.

  3. Select a data source.

    The second dialog box that the wizard presents offers you two options for selecting the data that you'll display on the Web page:

    • Record Source. FrontPage presents the list of tables in the database you chose in the previous step. Select the table you want from the list. You can customize what parts of this table you'll show visitors in the next step, but they'll only see data from this one table. If you want to show data from multiple tables, you'll need to enter a Custom Query.

    • Custom Query. This option lets you display any information you want by entering a SQL query string. SQL (Structured Query Language) is a programming language you can use to view and edit information in a variety of databases. If you need to draw data from multiple tables in your database, you'll need to create a custom SQL query here to do so. To enter a query, select this option and click Edit. Type or paste your query in the box provided. To use this feature, you'll need to know SQL. (Where can you learn more about SQL? Check out the "Learning More About Data and the Web" box later this tutorial on Filtering Database Results.)

    If you want to set query parameters in one table only, select it in record source and proceed onto the next step. You'll have an opportunity to set specific parameters for which records should display (see discussion of the More Options dialog box in the next step), and you won't need any knowledge of the SQL query language to do so.

    After you make your selection, click Next.

  4. Edit Fields to display.

    The third screen that the wizard presents lets you select specific fields to display and set their sort order. Click Edit List to open the Displayed Fields dialog box where you can remove and order fields (see Figure 16-5).

    Say you want to do more than just display the fields in the database table. If you want to set certain parameterslike employee records that display only if the department is Marketingthen you'll need to set up a query. To do so, forego Edit Fields and click More Options instead. One nice advantage to the More Options dialog box is that you can set up complex display parameters without knowing anything about how to compose SQL query statements. The More Options dialog box lets you edit the options in the list shown next.

    Figure 16-5. In the Displayed Fields dialog box, FrontPage has all fields set to display (listed in the pane on the right). To remove a field from this list, select it, and then click Remove. To reorder the list, select a field and click Move Up or Move Down. The order you set here is the order in which FrontPage lists results in the table it creates for display on the Web page.

    • Criteria. If you want to display only certain records in a table, click the Criteria button to display the Criteria dialog box. Here you can set very specific parameters for exactly what data you want to display. To do so, click Add. Within the Add Criteria dialog box that opens, select a field name, comparison, and value (see Figure 16-6). For example, if you're creating a page that lists only Managers, you might select Title in the field name, Equals from the Comparison list, and type Manager in the Value field. Use and/or settings to combine multiple criteria. If you select And, data must meet both criteria. If you select Or, data can meet either criteria.

      Figure 16-6. You can also use the Add Criteria dialog box to create a search box that visitors can use to find a record. To do so, select the field. Then, turn on the "Use this search form field" checkbox. In this example, a visitor would be able to type a last name in the search field to find a specific record.

      After you make choices in the Add Criteria dialog box and click OK, your parameters display in the Criteria dialog box (Figure 16-7).

      Figure 16-7. In this example, only employees in the Boston or Providence offices will appear on the final page. To edit a criterion, select it and click Modify. To delete a criterion, select it, and then click Remove.

    • Ordering. If you want to set a sort order for all the records that you'll display, click Ordering within the More Options dialog box. The Ordering dialog box appears (see Figure 16-8). Select the field you want to base the order on, and then click Add. FrontPage automatically makes the field sort in ascending order. To change it to descending, select the field (in the pane on the right) and click Change Sort.

    Figure 16-8. The Ordering dialog box lets you sort records based on multiple fields. In this example, records will appear in ascending alphabetical order by last name. Where the last name is the same, they'll appear in ascending alphabetical order by first name.

    Click OK to save settings in either the Criteria or Ordering dialog box, and then click OK to close the More Options dialog box. Then click Next to proceed to the next Database Results Wizard screen.

    If you get to the More Options dialog box to find the Criteria and Ordering buttons grayed out, this means you've entered a custom query in the previous Database Results Wizard dialog box. FrontPage grays out these choices so you don't enter parameters that conflict with the SQL query statement you entered within the Custom Query dialog box (explained back in step 3).

  5. Format results.

    The next wizard screen lets you set the appearance of the table or list that will appear on your Web page to display data from the database. Select one of the following from the drop-down list:

    • Tableone record per row. FrontPage creates an HTML table to hold results. Each row is a record and each column is a field.

    • Listone field per item. FrontPage displays each record on a separate line. (This option isn't available if you chose ASP.NET in step 2.)

    • Drop-Down listone record per item. This selection is for authors creating a drop-down search box. FrontPage lists the content of one of the database table's fields as items in a drop-down list. A visitor can make a selection from this drop-down list and submit it, as he would any HTML form. Specify the field that you want to display in the drop-down list (see Figure 16-9). (This option isn't available if you chose ASP.NET in step 2.) You can use this feature to create a drop-down list to filter results of a separate Data Results Wizard on the same page. (See the tutorial that follows this section on Filtering Database Results to learn how.)

    Figure 16-9. When you're creating a drop-down list on a form, you can select one value to display (like an employee name that visitors would recognize) but another to submit (like the employee's ID number).

    After you make your selections, click Next to move to the final wizard screen.

  6. Set the number of records to display per page.

    You can display all your results on one page by selecting "Display all records together." However, if you've got a large number of records, you'll want to split them up. To do so, select "Split records into groups" and enter the number of records you want to display on each page. FrontPage then creates forward and back buttons for each page and indicates to visitors how many pages of records are available.

If you turned on the "Use this search form field" checkbox in step 4, you can turn on the "Add search form" checkbox in this final wizard screen to add search capabilities to your data results.

If you add a search field, no results will display on your page until a visitor enters a value in the search box and clicks submit query. Then, only records that meet the search criteria exactly will appear.

Click Finish, and FrontPage creates the page that will display your database information (see Figure 16-10).

Figure 16-10. The page you create with the Database Results Wizard displays in FrontPage without any data in it. Data appears only when a browser requests the page.

Learning More About Data and the Web

To find out more about working with databases and related technologies, check out the resources listed here:

What about ASP? Get your feet wet with a visit to www.learnasp.com or www.asp101.com.

Learning SQL will give you a lot of control over what data you display. If you'll need to display records from multiple tables at once, there's no other way.