Using the Database Results Wizard

The FrontPage Database Results Wizard has been around since FrontPage 98 and has largely remained unchanged since FrontPage 2000. FrontPage 2003 adds the ability to generate ASP.NET pages with the Database Results Wizard, as well as ASP pages.

The Database Results Wizard can be used to display data from many different data sources:

• Microsoft Access database

• Delimited text files

• Microsoft Excel worksheet

• Microsoft dBase files

• Microsoft Paradox database

• Microsoft Visual FoxPro

• System data source (DSN) on the Web server

• Microsoft SQL Server

• Oracle (using Microsoft's ODBC provider)

• Other data sources using a .dsn file, .udl file, or a custom connection string

The first step in inserting database results with the Database Results Wizard is to select either ASP or ASP.NET for your Web page. Before you make this choice, make sure that your Web host supports the technology you plan to use.

TIP

The Database Results Wizard dialog box indicates that FrontPage detects whether your Web server is best suited to using ASP or ASP.NET. In fact, FrontPage makes no attempt to detect which technology to choose. The choice is purely your own.

For more information on the requirements of ASP, see "Understanding Active Server Pages," p. 680.

For more information on the requirements for ASP.NET, see "Using ASP.NET," p. 684.

Whether you choose ASP or ASP.NET, there is almost no difference in the steps taken to insert database results with the Database Results Wizard. ASP pages allow you to select from a wider array of options for presenting your data, but ASP.NET allows greater control over the appearance of your data. When deciding between ASP and ASP.NET, it is best to first carefully evaluate the purpose of the data and how you want to display it.

If you simply want your data displayed in a grid, ASP.NET might be your best option because it allows you to easily configure the appearance of data displayed in a grid. If you need to display your data in list format or in a dropdown box, ASP is the option you should choose. We'll look at the differences between using ASP and using ASP.NET after we've finished configuring and inserting some database results.

Configuring the Database Results Wizard

You're probably eager to see the Database Results Wizard in action at this point, so let's insert some database results and see some of this powerful tool's functionality. You will need to either create a new Web site on a server capable of running ASP pages, or publish your Web site to an ASP-enabled Web server before testing the ASP pages.

Starting the Database Results Wizard

Create a new one-page Web site and create a new document. Select Insert, Database, Results to display the Database Results Wizard as shown in Figure 37.5. Choose ASP as your technology and choose the option to use a sample database connection. Click Next: FrontPage will create a new folder in your Web site called fpdb, and a Microsoft Access database called fpnwind.mdb will be created in it. Click Next to go to step 2 of the Database Results Wizard.

Figure 37.5. The first step in the Database Results Wizard allows you to choose whether to use ASP or ASP.NET.

NOTE

You will also notice that FrontPage has created a file global.asa in the root folder of your Web site. This is a special file used in ASP applications. FrontPage uses the global.asa file for storage of database connection information as well as for special functions that the database features rely on. For more information on the use of the global.asa file, check out Active Server Pages 3.0 By Example by Que Publishing.

Choosing a Record Source

In step 2, you select a record source for your database results. FrontPage provides two options for you, as shown in Figure 37.6. The first option is the Record Source dropdown, which contains an entry for each table in the sample database that FrontPage created for you. The dropdown will also contain any queries that have been saved in Microsoft Access, but the fpnwind.mdb file doesn't contain any queries.

Figure 37.6. Choosing a record source in step 2 of the Database Results Wizard.

The second available option is to use a custom SQL statement. Using a custom SQL statement, you can easily customize the data that is returned by the Database Results Wizard, but it requires a knowledge of SQL syntax. Select Employees from the Record Source dropdown and click Next to move to step 3.

Configuring Your Data

Even though the dialog box in step 3 appears to be very simple at first glance(see Figure 37.7), much of the advanced functionality of the Database Results Wizard is configured here. In the top half of the dialog box, you can configure which fields are displayed in your database results. By default, all the fields in the table or query you select are displayed. If you'd like to remove some of those fields or change the display order of those fields, you can do so by clicking the Edit List button.

Figure 37.7. Step 3 of the Database Results Wizard provides access to advanced features.

NOTE

Even though you might change the fields that display in your database results via the Edit List button, FrontPage will still generate ASP code that returns all the fields from the database. Whether you display all those fields is your choice.

Displaying all the fields in the Employees table is going to be a little unwieldy, so click the Edit List button and select all the fields in the Displayed Fields list except for EmployeeID, LastName, FirstName, Title, and HireDate. After you've selected the fields, click the Remove button to remove them from the list.

Move the HireDate field so that it will be displayed in the second column of your database results by selecting the field and then clicking the Move Up button three times so that it appears under the EmployeeID field, as seen in Figure 37.8.

Figure 37.8. The Displayed Fields dialog box configured so that only specific fields are displayed.

TIP

You can easily select multiple fields in the Displayed Fields list by holding the Ctrl key while clicking noncontiguous fields or clicking the first field then clicking the last field while holding the Shift key to select contiguous fields.

Clicking the More Options button displays the More Options dialog box (see Figure 37.9), where you can configure many of the advanced features of the Database Results Wizard.

• Criteria button Allows you to filter your database results based on whatever criteria you choose. As you will see later in this chapter, you can filter based on a fixed value or a value entered in a form field on a Web page.

• Ordering button Allows you to change the order of the items in your database results.

• Defaults button Allows you to set default values for any form fields that are used to filter results. If a user does not enter a value for a field, FrontPage will use the default value entered here.

• Limit Number of Records Returned Allows you to control how many records are returned by your database results. This enables you to prevent long-running queries that might take longer than desired.

• Message to Display if No Records Are Returned Indicates what message should be displayed to the user if the Database Results Wizard returns no results.

Figure 37.9. The More Options dialog box provides access to some of the advanced features of the Database Results Wizard.

To order the list of results by LastName and then by FirstName, click the More Options button and then click the Ordering button. Select the LastName field in the Available Fields list and then click the Add button to add the LastName field to the Sort Order list. Click the Add button again to add the FirstName field to the Sort Order list. The Ordering dialog box should now appear as shown in Figure 37.10. When the Database Results Wizard displays the results, they will be ordered by the LastName field and then the FirstName field in ascending order.

Figure 37.10. The Ordering dialog box makes it easy to sort your results.

Click OK to dismiss the Ordering dialog box and then click OK again to dismiss the More Options dialog box. Click the Next button to move to step 4 of the Database Results Wizard, where you will choose the formatting options for your database results.

Formatting Your Data

When using ASP to display your database results, you have three options for formatting your results. The first option is to use a table with one record per row. When you choose this option, you are given three check boxes to configure the appearance of the table.

• Use Table Border When checked, the table will have a border around each cell.

• Expand Table to Width of Page When checked, the table will expand to 100% of the page's width. If left unchecked, the table will expand as necessary to account for the data contained in it.

• Include Header Row with Column Labels Displays a header row at the top of the table containing the name of the field contained in each column.

In addition to the option of displaying the results in a table, you can also choose to display the results in list format. You have the option to display labels for all field values and to include a horizontal separator bar between each record. The List Options dropdown provides for a high level of customization of the list.

• Paragraphs Displays the database results as a single paragraph for each field using <p> tags. This has the effect of making the list double-spaced.

• Line breaks Displays the results in list format with a <br> tag separating each field. This has the effect of making the list single-spaced.

• Bullet list Displays the results in list format as a bulleted list with each field represented by a bulleted item.

• Numbered list Displays the results in list format as a numbered list with each field representing a numbered item. Numbering restarts for each record returned.

• Definition list Displays the results in list format as a definition list using <dt> and <dd> tags.

• Table Displays the results in table format with each record in its own table.

• Formatted Displays the results as preformatted text using the <pre> tag.

• Text fields Displays the results in text input fields in a <form> tag, one <form> tag per record. This option is best used when your results return one record and you want to perform a search based on that record.

• Scrolling text fields Displays results in scrolling text fields using <textarea> tags. As with the Text Fields option, this is best used when performing a search based on a single returned record.

Your last formatting option is to display the results in a dropdown list. This option displays your results in a dropdown inside of a <form> tag so that a user of your page can select a value from your results and then submit the form. When you select this formatting option, you are presented with two dropdowns, as shown in Figure 37.11. The first dropdown specifies the database field used to populate the text that users of your Web page will see in the dropdown. The second dropdown specifies the database field that is submitted when the form is submitted.

Figure 37.11. The dropdown list option requires you to select the value to display and the value to submit.

Select the Table option in the formatting dropdown, and then click Next to go to step 5.

Choosing Display Options

In step 5, you have the option of displaying all records that are returned or splitting the records into groups (see Figure 37.12). Splitting records into groups is a good idea if your database results page returns a large number of records. Returning a large number of records can take a long time to load. If you split the records into groups, the number of records that you specify in step 5 will be returned and the user can then click a button to select the next group of records. The Add Search Form check box is disabled at this point because you haven't chosen any form fields in previous options. You will use that option later.

Figure 37.12. Step 5 of the Database Results Wizard allows you to break large results up in to groups.

Click Finish to insert your database results. Your page in FrontPage should be similar to Figure 37.13. Save your page as database.asp and preview it in your browser. You should see a list of five employees and buttons to allow you to go to the next page to see the remaining employees, as shown in Figure 37.14. All this information is being dynamically pulled from the Microsoft Access database in the fpdb folder of your Web site.

Figure 37.13. The inserted database results ready to be previewed in your browser.

Figure 37.14. The information on this page is dynamically populated from your database.

Using ASP.NET to Display Data

The page you created in the previous section uses ASP to display the data from your database. You can also choose to use ASP.NET to display your results instead of using ASP. Doing so gives you easier control over the appearance of your data, but it limits you to one formatting option the ASP.NET DataGrid. The DataGrid is a very powerful control, but to get the maximum benefit from it, you either need to have a solid understanding of how to hand-code the DataGrid in HTML view, or you need to use a tool such as Visual Studio .NET that has a user interface for editing the properties of the DataGrid. Displaying your database results using ASP.NET still has some unique benefits.

CAUTION

If you attempt to switch an ASP database results page to ASP.NET or vice versa, FrontPage will warn you that the page may not display correctly or may not display at all. Heed that warning. A page switched from ASP to ASP.NET or ASP.NET to ASP will almost always not display. To be safe, always create a new page before inserting new database results.

To create a new page using ASP.NET to display database results, follow these steps:

1. Close your browser and switch back to FrontPage.

2. Create a new page and select Insert, Database, Results.

3. In the first step, select ASP.NET and then click Next.

4. Select Employees as the record source and click Next.

5. Click the Edit List button and select the Notes field in the Displayed Fields list. Click Remove to remove the Notes field from your results and click OK.

6. Click Next to move to step 4.

In step 4, you will notice that the three formatting options available to you for ASP pages have been replaced by one formatting option, the DataGrid. The DataGrid is rendered by ASP.NET using a <table>, so it's very similar to the Table formatting option when configuring an ASP page. However, when using an ASP.NET DataGrid, you have much more control over the appearance of the table.

As shown in Figure 37.15, three options are available to you when using an ASP.NET DataGrid:

• Use Table Border As with the ASP table, selecting this option places a border around each cell of the DataGrid. This is configurable after the DataGrid has been inserted, as you will see later.

• Use Alternating Row Colors Selecting this option causes every other row in the DataGrid to be highlighted with a different color. The color used can be changed easily after the DataGrid has been inserted.

• Include Header Row with Column Headers Selecting this option makes the first row in the DataGrid into a header row. This is also configurable after the DataGrid has been inserted.

Figure 37.15. When using ASP.NET, the only formatting option available is the DataGrid, but it's highly customizable.

For now, leave all three options selected, click Next, and then click Finish to insert the DataGrid. Your page should now resemble the page shown in Figure 37.16. Save your page as employeeid.aspx (the default name FrontPage will give it) and preview it in your browser to see the result as shown in Figure 37.17. The page is almost identical to the ASP page except that alternating rows of the table are in a different color.

Figure 37.16. This page uses the ASP.NET DataGrid to display database results.

Figure 37.17. This ASP.NET page shows the DataGrid with alternating row colors.

TIP

Remember that you must have the Microsoft .NET Framework installed for ASP.NET pages to display correctly, and you must be using Internet Information Services (IIS) 5.0 or later on Windows 2000 or later.

If the ASP.NET page shows up but the DataGrid doesn't display, see "DataGrid in ASP.NET Page Doesn't Show" in the "Troubleshooting" section of this chapter.

One of the major benefits of using the DataGrid (and really the only benefit if you don't know how to hand-code ASP.NET) is that you can very easily configure the appearance of the DataGrid right within the FrontPage interface. Right-click on the DataGrid in FrontPage and select Format Results Region from the menu to display the Format Results Region dialog box as shown in Figure 37.18.

Figure 37.18. The Format Results Region dialog box makes formatting the DataGrid in an ASP.NET page simple.

Using the Format Results Region dialog box, you can change the color scheme of the DataGrid, change the formatting of the border, and set the formatting for a header and a footer. By clicking the Edit Columns button, you can configure the header and the footer text for each column, as shown in Figure 37.19. Figure 37.20 shows the database results after the results region has been formatted with custom colors and fonts using the Format Results Region dialog box.

Figure 37.19. The Edit Columns dialog box allows you to customize the text displayed in the header and footer columns.

Figure 37.20. The database results are much more visually pleasing with a few formatting changes.

TIP

After making formatting changes using the Format Results Region dialog box, the DataGrid displays differently in Design view in FrontPage. Instead of seeing the DataGrid, you will only see a single line that says ASP.NET Control: DataGrid. This is normal, and the page will display correctly in the browser.

Drilling into Data with the Database Results Wizard

It's impressive that you can insert a dynamic data-driven Web page with no coding at all, but these pages are not very useful by themselves. There really isn't any interaction going on with the user. For example, suppose that you wanted to be able to get detailed information on an employee when you click her name in the list? This kind of interaction would be pretty difficult to code by hand, but with the Database Results Wizard, it's easy.

Creating a Drill-Down Page

Close your browser, switch back to FrontPage, and open the database.asp page. Double-click on one of the yellow bars surrounding your database results to display the Database Results Wizard. In order to provide for a data drill-down page, you will reconfigure this page so that the employee's last name is a hyperlink to another page. When that link is clicked, it will display details on the employee whose name was clicked.

1. Click Next twice to move to step 3 of the Database Results Wizard.

2. Click Edit List and remove the EmployeeID field.

3. Select the HireDate field and click Move Down to move it just above the Title field.

4. Click OK.

5. Click Next twice and Finish to regenerate the Database Results Region.

6. FrontPage will ask if you want to regenerate the Database Results Region. When it does, click Yes.

Creating the Details Page

Now you will need to create a new ASP page to display the details on the employee whose name is clicked.

1. Create a new page and select Insert, Database, Results.

2. Make sure that ASP is selected and that you are using the existing Sample connection and click Next.

3. Select Employees as the record source and click Next.

4. Click the More Options button and click Criteria.

5. In the Criteria dialog box, click Add.

6. In the Field Name dropdown, make sure that EmployeeID is selected.

7. In the Comparison dropdown, make sure that Equals is selected.

8. In the Value dropdown, make sure that EmployeeID is selected.

9. Make sure that the Use This Search Form Field check box is checked. This tells FrontPage that EmployeeID in the Value dropdown is a value that will be passed from a search form and not an explicit value.

10. Click OK three times to return to the Database Results Wizard and click Next.

11. Choose the List One Field per Item formatting option and click Next.

12. Uncheck the Add Search Form check box. If this is checked, FrontPage will add a form at the top of your database results so that users can enter an employee ID to search on. In this example, you are passing the employee ID from another page, so you don't want a search form.

13. Click Finish to insert the database results.

TIP

When using a form field to search on as you are doing here, it is a good idea to enter default values in the More Options dialog box. However, in this case, no one will be browsing directly to the details.asp page, so no default values were entered.

Save this page as details.asp. This page is now configured to display details on whatever employee ID is passed to it. You now need to configure the database.asp page so that it passes the employee ID to details.asp.

Connecting the Pages

Switch back to the database.asp page. In your database results, right-click on <<LastName>> and select Database Column Value Properties from the menu to display the Database Column Value dialog box. Place a check mark in the Display as Hyperlink checkbox and click the Hyperlink Parameters button to display the Hyperlink Parameters dialog box.

The Hyperlink Parameters dialog box is used to configure the URL that the database column links to. It also enables you to configure query string values. A query string value is a name/value pair that is passed in a hyperlink. In this case, you want to pass the employee ID for the employee whose last name you click. You will pass that employee ID in the hyperlink URL as a query string.

Click the Add button to display the Add Parameter dialog box. In the Name dropdown, choose EmployeeID. FrontPage will automatically populate the Value dropdown with the ASP code necessary to pass the employee ID for the employee you click, as shown in Figure 37.21. Click OK to insert the parameter and then click OK again to dismiss the Hyperlink Parameters dialog box. The Column to Display dropdown now contains a hyperlink to details.asp, and the query string value is added to it as shown in Figure 37.22.

Figure 37.21. The ASP code necessary to populate the employee ID is entered automatically.

Figure 37.22. The Database Column Value dialog box now contains a URL for the details.asp page and the employee ID query string.

Click OK in the Database Column Value dialog box. Note that the LastName column is now configured as a hyperlink. However, it is now configured to display the EmployeeID field and not the LastName field. Because you want to make the employee's last name a hyperlink to the details page, double-click on <<EmployeeID>> and select LastName in the Column to Display dropdown. Click OK to commit that change. If you now hover over the <<LastName>> column value, you will notice that the status bar in FrontPage displays the hyperlink to the details.asp page.

TIP

The fact that FrontPage changed the column to display the EmployeeID field is a quirk of the Database Results Wizard. If you configure a database column as a hyperlink, you can still reconfigure the column to display the desired database column value by double-clicking the column value and changing the column that it displays. Your hyperlink settings will be maintained.

Save the database.asp page and preview it in your browser. Each employee's last name should be a hyperlink, and when it is clicked, you will be taken to details.asp and the complete details on that employee should be displayed.

NOTE

Microsoft FrontPage MVP (Most Valuable Professional) Kathleen Anderson has an excellent Web page with great resources and some of her own tips and tutorials on the Database Results Wizard at http://dbrw.frontpagelink.com. This is a highly recommended resource for getting the most out of the Database Results Wizard in FrontPage.

The FrontPage Database Results Wizard, like any wizard, is limited in what it can do. To really get full database functionality, consider learning how to code your own ASP. For resources on learning ASP, see "ASP and .NET Resources," p. 916.