Dynamic Datasheets

Publish your datasheets with FrontPage 2000

Microsoft FrontPage 2000's Database Wizard makes publishing dynamic datasheets based on SQL Server data sources a snap. (A datasheet is a tabular display of a table's contents or the return set from a view or stored procedure.) The Database Wizard is a popular web-authoring tool that makes SQL Server data more easily available across the Web. (According to the Microsoft Press Computer Dictionary, a web is "a set of interlinked documents in a hypertext system," as distinguished from the World Wide Web.) Workstations using the wizard don't require SQL Server client management tools (as is the case for the built-in SQL Server Enterprise Manager Web Assistant user interface). Thus the wizard is easy to use with Microsoft Data Engine (MSDE), which doesn't ship with client management tools. You can use the FrontPage 2000 Database Wizard to dynamically publish datasheets based on SQL Server data sources. In contrast, the Web Assistant Wizard publishes static HTML pages. When browsers open a static page, they view only a snapshot of a data source taken at the time the wizard created the page.

Creating a FrontPage Subweb

I wrote the examples in this article with FrontPage 2000 running on Microsoft IIS 5.0, but the same techniques work on IIS 4.0 and Personal Web Server (PWS) 4.0. PWS is a great tool for developers who want to experiment with web work. Free and easy to administer, PWS runs on Windows 9x and is available for download at http://www.microsoft.com/windows/ie/pws/. You can also run the samples with Peer Web Services on a Windows NT Workstation computer or with Personal Web Manager on a Windows 2000 Professional computer. In all cases, your web server must have FrontPage 2000 Server Extensions installed. (FrontPage 2000 Server Extensions install automatically with IIS 5.0, but you need to manually install them on earlier IIS versions and the other web servers. They're available with NT 4.0 Option Pack and with Microsoft Office 2000 Premium and Office 2000 Developer editions.) The FrontPage Database features require the installation of FrontPage Server Extensions and the capability to process Active Server Pages (ASP). All the web servers named in this paragraph can handle ASP.

To publish a set of datasheets based on SQL Server data, you first need to create a new web or subweb. Open FrontPage to its root web or any other parent web you prefer. Select File, New, Web. Figure 1 shows a new subweb called SQLMagDBWiz1, which I created on the CAB2000 web server. When FrontPage completes the task, the new site will have one default.htm page with no content as the site's home page. You can add content by typing text (such as "This is my home page") on the page from the Normal tab in FrontPage. Save the page. To view the text, use a browser to navigate to your home page, such as http://cab2000/SQLMagDBWiz1.

Because FrontPage publishes its datasheets as ASP web pages, you need to enable any folders holding these web pages to run ASP scripts. Begin by right-clicking the folder in the Folder List window. (If your web pages are in the web site's root folder, right-click that folder.) Then, select Properties. Select the Allows scripts to be run check box on the General tab of the Properties dialog box.

If you're publishing sensitive information in your datasheets, be sure to take advantage of subweb security features, which let you set different permissions for different subwebs. Although this feature is beyond the scope of this article, you can read the white paper "Beyond Permissions: Securing Your FrontPage 2000-Based Web," at http://officeupdate.microsoft.com/2000 /focus/articles/fpsecurity.htm.

Adding the First Datasheet to a Site

The FrontPage Database Wizard offers a five-step process for publishing datasheets. In the first step, you'll define a database connection, which can be newly or previously defined. You always have to define a connection the first time you use the wizard at a site.

Start with a blank web. Click the New Page icon on the toolbar. Select the Normal tab and position your cursor on the blank page. Then, launch the wizard by selecting Insert, Database, Results, and open the Step 1 of 5 dialog box. (The dialog boxes are labeled "Step 1 of 5," "Step 2 of 5," and so on.) If you're creating a new database connection, select Use a new database connection, then click Create to open the Web Settings dialog box to the Database tab. Click Add to open the New Database Connection dialog box with four options, as the left window in Figure 2 shows. Those developers publishing datasheets based on SQL Server or MSDE will use either the System data source on web server option, or the Network connection to a database server option. If you select the System data source on web server option and click Browse, FrontPage opens a dialog box that lets you select a previously defined Data Source Name (DSN) on the web server. If you select Network connection and click Browse, the Network Database Connection dialog box appears, as the right window in Figure 2 shows. Enter the name of the SQL Server machine and the database (Northwind in our example) that you want to make the source for published datasheets. Click OK to return to the New Database Connection dialog box, and click Advanced. Use the resulting dialog box to specify a username and password for the database, then click OK three times to return to the wizard's Step 1 of 5 dialog box. You now have a database source for publishing your datasheet.

Click Next to open the Step 2 of 5 dialog box, which provides two options to select a source for your published datasheet. You can choose an existing table or view, or you can enter a custom T-SQL statement that generates a return set for your datasheet. In Figure 3, the developer has selected the Shippers table from the Northwind database. After opening the drop-down list, highlight and click the data source.

If you just want to publish the selected data source, click Next in the dialog boxes for steps 2, 3, and 4, then click Finish in the Step 5 of 5 dialog box. You won't be able to view the datasheet in FrontPage because the wizard publishes an ASP page with an .asp extension. This file type requires the web server to compose a page dynamically when the browser requests it. Save the page by selecting File, Save As. Make sure the file type is .asp (this setting is the default for a new page that the Database wizard generates). In this example, I saved a datasheet based on the Shippers table as shippers.asp. You can view the page by clicking the Preview in Browser control on the toolbar.

Users with access to the CAB2000 web server can view the Shippers datasheet by browsing to http://cab2000/SQLMag DBWiz1/shippers.asp. Figure 4 shows the published datasheet inside a browser. Although formatted by default, the page is dynamic, and if the table changes, the datasheet contents will automatically change.

Typically, you won't publish from the SQLMagDBWiz1 web on the CAB2000 web server. Replace these with your web server name and the path to the published datasheet. In addition, assign a file name to your web page. If you're saving the page to the currently connected web, follow the previous instructions for saving the page. If you're saving to a web site that you're not currently connected to, save the page locally. You can publish the saved local page later to the other web, using whatever means the web server administrator recommends. FrontPage supports a File, Publish Web command for publishing material from a local web to a remote web.

Adding a Second Datasheet to a Site

The task gets easier the next time you publish a datasheet from the same web site. Start by creating a new blank page and positioning the cursor on the page, as in the first example. Then, choose Insert, Database, Results. The first dialog box automatically selects your first database connection as the source for your published datasheet. You can create a new database connection by using the steps I describe above, or you can click a drop-down box to open a list of previously defined database connections. The Database Wizard names these previous connections Database1, Database2, and so on. Select the connection you prefer for your new datasheet, and click Next to advance to step 2.

Recall that you choose or define a datasource for your published datasheet in the Step 2 of 5 dialog box. To publish the second datasheet, highlight the Customers table. Although this table contains many fields, the second datasheet will provide only customer telephone and fax numbers. When you need a subset of fields from a data source, use the Step 3 of 5 dialog box to remove any fields you don't want to publish. Click Edit List in the dialog box, then use the Displayed Fields dialog box to retain the fields you want in the datasheet. In Figure 5, the developer has selected several columns from the Customers table for removal, and the Country column will enter the group removed from the published datasheet. Click More Options to graphically specify selection criteria for rows the published datasheet will include from its underlying source. The More Options button also lets you set sort criteria for the datasheet.

After completing your selections from the Step 3 of 5 dialog box, click Next in the dialog boxes for steps 3 and 4, and click Finish on the Step 5 of 5 dialog box. Remember that you must save the datasheet file before you can view it.

FrontPage 2000 publishes the page with the default paging controls, which appear in the lower-left corner of the table, as Figure 6 shows. By default, FrontPage groups records five to a page. (Figure 6 depicts page 2 of 19.) With the paging controls, browsers can move through the records within a datasheet.

Wizard to Web

The FrontPage 2000 Database Wizard is a simple but powerful tool for dynamically publishing SQL Server data to a web. Although the wizard offers five dialog boxes for controlling the data you publish, sometimes you need to select options from only one or two of them. The Database Wizard lets Office developers publish MSDE and SQL Server data sources, regardless of whether their workstations have SQL Server client-management tools.