Data Management

Publishing databases with Microsoft FrontPage 2000

FrontPage 2000 offers an easy way to publish databases to the Web. Easier than programming them, anyway. In this Daily Drill Down, Rick Dobson presents a tutorial to help you get started.

Publishing databases can be tricky for those who do not regularly work with databases on the Web. For IT pros using Microsoft Internet Information Server and FrontPage 2000, Microsoft has drastically simplified what it takes to publish from databases to the Web. This means that Webmasters who are not expert at databases and database administrators who only occasionally do Web work can both publish databases easily.

The trick to making database publishing on the Web easier is to use the FrontPage Database Wizard. This wizard can help make new database collections and manage a collection of connections for reuse. You can create search forms and drop-down controls, as well as publish datasheets dynamically. While the wizard does not directly automate the use of a drop-down control to specify a search, a simple extension makes this possible. This Daily Drill Down gives you a quick overview of the wizard and four examples that demonstrate its potential. All the examples use FrontPage 2000 on a computer running Windows 2000 Professional, with Internet Information Server version 5 as the Web server. The example discussion assumes a working knowledge of FrontPage for setting up a site and saving pages. The Daily Drill Down assumes that the SQL Server pubs database is available and that you have a DSN that points to it, but the samples can be readily adapted for a DSN pointing at any other database.

Overview of the FrontPage Database Wizard
To use the FrontPage Database Wizard, you must have the FrontPage Extensions installed on the computer running the Web server. You can install these extensions from the Office 2000 installation CDs or the NT 4 Option Pack. Some ISPs disable the FrontPage Database Wizard. Microsoft reports that you can publish pages locally with the wizard and then upload your pages to your Web site at the ISP. The knowledge necessary to perform this task, however, requires a level of Web and database knowledge that is beyond simply using the wizard interface.

You can start to invoke the Database Wizard from a blank page in a FrontPage Web site. With your cursor in the page to which you want to publish, choose Insert | Database | Results. This starts the first of five major steps. Within each of the five steps, you can optionally invoke several sub-steps. If you are working with a previously created database connection, you only need to make a selection from the dialog box for one of the five steps. The sub-steps conveniently allow you to drill down on optional database design and Web page layout issues.

Step 1 is for making a new database connection or choosing an existing one to reuse. You can select a sample database that ships with FrontPage, create a new database connection to another database, or select a previous connection for reuse. The sample database that ships with FrontPage is a scaled-down version of the Access Northwind database file. Choosing the sample database installs it at the Web site in the fpdb folder that the wizard automatically creates the first time you use it. If you already specified a database connection with which you want to work, you can select its name from a drop-down control.

When you create a new database connection, you have three options for specifying a new data source. First, you can use any other Access database file that you copy to the Web server. It is convenient, but not essential, to copy these databases to the fpdb folder. Second, you can use a system DSN or a file DSN on the Web server that points at a database. This approach allows you to reference data sources specified with the ODBC Data Source Administrator. This administrator is available directly through Control Panel in Windows 9x and Windows NT, but you access it indirectly from the Control Panel with Windows 2000 by clicking the Administrative Tools icon. Third, you can explicitly specify a network connection from the Web server to the database server.

In Step 2, you designate a precise data source within a database from which to publish in either of two ways. First, you can use a drop-down control to select a previously defined database object, such as a table. Second, you can enter a Structured Query Language (SQL) string for a new custom query. Use SQL syntax compatible with whatever database server you are using.

Step 3 lets you control the data that shows in a database publication. For example, you can specify a subset of the columns for a table. You can also specify criteria that restrict the rows that appear when you publish from a data source. In addition, this step lets you graphically declare sort keys for the way data appears on a page. Finally, you can create a search form that lets end users input a criterion value on the Web page and obtain a datasheet consistent with the run-time specified value.

With Step 4, you can choose one of three layouts for database contents on the Web page. If you choose table, your data appears as a datasheet with one record per row. If you choose list, each field value in every row occupies a separate line on the Web page. This is similar to the columnar format for an Access form or report. You can also choose to display your records in a drop-down control (similar to a combo box). When using this option, make sure that there are no repeated values in the drop-down list, because end users will not be able to distinguish between identical items.

Step 5 presents more formatting options. For example, the wizard automatically groups records in a tabular display with five records per page. You can designate the number of records that should appear per page or turn the paging feature off altogether.

Publishing the Authors table from the Pubs database
To show how easy it is to publish a datasheet, here are the steps for publishing the Authors table from the Pubs database that ships with SQL Server. The instructions below do not assume a connection to the Pubs database already exists. The steps are as follows:
  1. With your cursor resting in a blank page, choose Insert | Database | Results to start the Database Results Wizard.
  2. In Step 1, select the Use New Database Connection radio button and click Create. Then, click Add in the Web Settings dialog box. Enter a new name for the database connection, such as Pubs, or accept the default name in the New Database Connection dialog box. You can then select the radio button labeled System Data Source On Web Server. Then, click Browse. This opens a list of DSNs defined on the Web server. Highlight the one for the database to which your Web page must connect and click three OK buttons to return to the main Step 1 dialog box.
  3. Click Next to move to the Step 2 dialog box (see Figure A). Ensure the drop-down control in the dialog box shows the Authors table. Since the Authors table appears first, it is selected by default.
  4. Click Next three times and Finish in the Step 5 dialog box.

Figure A
In Step 2 of the Database Results Wizard, select Authors as your record source.

To view the page in a browser, you must first save it. Click Save on the FrontPage toolbar and then give it any name you want, such as authors_datasheet. Notice that FrontPage automatically assigns it an Active Server Pages file type. Click Save and then Preview In Browser on the toolbar to view the page in a browser.

Figure B displays the second page of the Authors table. Notice that the page contains five records. Text to the right of the page navigation controls indicates that the second of five pages shows. The cursor rests on the Next control. Clicking this control advances the display from the second of five pages to the third of five pages. If the contents of the Authors table change, the Web page will show the revision the next time a user refreshes the page in a browser. While the Web page shows database changes, users cannot revise the database from the Web page.

Figure B
Users can view a database in a Web browser. Updates to the database will show; however, users can’t modify the database through the Web page.

Publishing a search form for Authors
The output in Figure B makes available all authors from the Authors table (even if it only shows up to five records on any one page of the table). Sometimes your applications need to return just a subset of the total number of records from a data source. One popular way to designate a subset of records is to allow end users to specify the criteria for returning records at run time. The FrontPage Database Wizard supports this kind of task with its capability to create a search form.

I demonstrate the wizard's approach to this kind of task by covering the steps to create a search form that creates a datasheet for just the authors from a particular state. The form contains a text box in which a user can enter a state abbreviation, such as CA for California. When a user clicks Submit on the form, the Web page then displays a datasheet with just the authors from CA (or whatever state a user enters in the text box).

The instructions below assume that we create this search form after having first created the Authors datasheet described in the preceding section. Completing that example creates a database connection, such as the one named Pubs, on the Web site that we can reuse.
  1. With your cursor resting in a blank page, choose Insert | Database | Results to start the Database Wizard.
  2. In the Step 1 dialog box, select the Use An Existing Database Connection radio button and pick Pubs (or whatever you named the connection from the prior example) from the drop-down control. Click Next to exit the dialog box for Step 1.
  3. Click Next again in Step 2 to select automatically the Authors table as the data source for the search form.
  4. On the Step 3 dialog box, click More Options. In the More Options dialog box, click Criteria and then click Add in the Criteria dialog box. In the Add Criteria dialog box, select State from the drop-down Field Name control and click OK three times to return to the main Step 3 dialog box.
  5. Complete the search form steps by clicking Next in the dialog boxes for the third and fourth steps. Then, click Finish in the Step 5 dialog box.

As with the preceding example, you need to save the page before you can test it in a browser. Give the page a name, such as authors_search_form. FrontPage will automatically assign an .asp extension. After saving the Web page, you can open it in your browser and test your search form. Figure C shows CA entered as a criterion. Notice that all the records on the second page of the Authors datasheet are from the state of California. Contrast this with Figure B, which shows a datasheet for all the authors. Notice that its second page starts with an author from Kansas.

Figure C
A custom Web search form allows users to enter criteria you select in the Database Wizard.

Creating a drop-down box to select an author by state
The FrontPage Database Wizard is also handy for creating a drop-down control on the Web page. As with the datasheet and search form, there is no need for programming. Furthermore, you can overwrite the text box for a criterion on a search form by cutting and pasting the drop-down control created with the wizard over the text box. This enables the specification of a criterion on a search form with a drop-down control.

When creating a drop-down control, it is important to make sure that your data source contains just one item for each possible entry. In the Authors table, more than one author can come from the same state. Therefore, state values can repeat. We need a custom SQL statement to eliminate these duplicate state values. The following steps describe the process for creating a drop-down control that lists the states in which authors reside:
  1. With your cursor resting in a blank page, choose Insert | Database | Results to start the Database Wizard.
  2. In the Step 1 dialog box, select the Use An Existing Database Connection radio button and pick Pubs (or whatever you named the connection from the first example) from the drop-down control below the button.
  3. Click Next to enter the dialog box for Step 2. Then, select the Custom Query radio button and click Edit. Enter a SQL string like the one in Figure D in the Custom Query dialog box. The SELECT statement lists states for rows from the Authors table. The DISTINCT keyword with the SELECT statement eliminates the duplicates for authors from the same state.
  4. Click OK and then click Next twice to advance to the dialog box for Step 4. From the drop-down control in the dialog box, select Drop-Down List‑One Record Per Item.
  5. Close the wizard by choosing Next and Finish.

Figure D
You can create a custom query in a drop-down box without the need for programming.

As with the preceding examples, you need to save the page before you can view or reuse the drop-down control in another context, such as with a search form. Creating a drop-down control on the Web page has little value because nothing happens as a result of making a selection from the drop-down control.

One useful application for a drop-down control is to put it on a search form. The wizard does not allow you to do this directly, however. A workaround is to copy the drop-down control over the text box on a standard search form. After saving the page with the drop-down control, you can perform the steps for creating a search form just like in the first search form example. Before saving this second search form, copy the combo box control over the text box. This creates a search form that lets end users select the authors from a state with a drop-down control. Then, save the Web page with a name, such as authors_search_form_with_drop_down. Figure E shows the new search form with a drop-down control replacing the text box shown in Figure C. This redesigned search form has merit for those situations where end users prefer a drop-down control instead of a text box for specifying the search criterion.

Figure E
With a few tricks, you can enter a drop-down box in a Web search form.

Caveats for using the FrontPage Database Wizard
There are several "problems" that you can encounter when you try to use the FrontPage Database Wizard. First, it requires the installation of the FrontPage Extensions on the computer from which it runs. Since the extensions do not install by default with FrontPage or Office Premium, you need to explicitly select the extensions when you install FrontPage. If you do not install the FrontPage Extensions, FrontPage does not enable the Insert | Database | Results command.

I tested all the examples in this Daily Drill Down with Internet Information Server (IIS) version 5 running on a Windows 2000 Professional computer. The samples will also run with IIS 4 on an NT 4 server. However, the FrontPage Database Wizard does not work reliably on Personal Web Server (PWS). This is not to say that it cannot work on PWS with Windows 95 or Windows 98, but it can also readily fail with PWS. If you want to use the full feature set of IIS on a PWS computer reliably, I urge you to consider using IIS as your Web server. The Microsoft Knowledge Base article Q184572 provides some background on the cause of the difficulty with PWS and a potential workaround.

You may have noticed that the instructions for all the examples start "with the cursor resting in a blank page." This is by design. The Insert | Database | Results command is not enabled unless your cursor is on a page. While this instruction may seem trivial, it is amazing how easy it is to become confused about why the command is not enabled. Remember, your cursor must be resting on a page. It does not necessarily have to be blank. For example, you can publish two different datasheets to the same page from different databases. In this situation, your cursor would not be resting on a "blank" page for the second datasheet. You must have the cursor on the area of a page where you want your second datasheet, however.

As you can see from the example for the drop-down control, you can publish from a database based on a custom SQL string instead of using a previously existing database object, such as a table. However, there is at least one limitation. The WHERE clause for a custom query string cannot include unequal signs, such as >, <, or <>. The FrontPage Database query interpreter misreads these query strings as HTML tag delimiters. All other query strings, such as those with an equal sign, work correctly.

The FrontPage Database Wizard can drastically simplify publishing from databases to the Web page. Since the wizard publishes dynamically, changes to a database automatically appear on published datasheets the next time a user refreshes or opens a new copy of the Web page with the revision. This Daily Drill Down gives you a feel for the general kinds of functionality that you can tap with the wizard. You also learned how easy it is to invoke the wizard's capabilities. While this Daily Drill Down's examples use the Pubs SQL Server database, the wizard can work with any ODBC data source. There's much more to the wizard than this Daily Drill Down covers. However, if you master the sample techniques and recall the caveats described in this Daily Drill Down, you will have no difficulty moving on to additional formatting and performance features.
The authors and editors have taken care in preparation of the content contained herein but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

Editor's Picks