Display real-time database information on your site

September 2002

By Jeannine Gailey

This column discusses the benefits of being able to display real-time data from a database on your Web site, and it shows how to use the Microsoft FrontPage Web site creation and management tool to quickly and easily add a data source to your site.

Introduction

At the outset of the Internet craze, when everyone from the local butcher to your teenage son was publishing a Web site, a site could get by on avant-garde visual design alone. Many sites went over the top, with animations that danced and MIDI-music files that blared. It seemed that no one remembered the days when the Internet was all about raw information. Now, as the Internet begins to mature, sites that provide reliable and useful data for their customers are the ones that thrive.

Benefits of publishing live data

Imagine going to a sports-oriented Web site and not being able to find the latest basketball scores, or going to a news site and finding that the headlines are out-of-date. Customers will return to your site only if you give them a reason to, and providing up-to-date content is the optimal way to lure visitors to return.

If you operate an intranet or a business extranet employing security measures, live data can be a great way to communicate with members of your team. Wouldn’t it be great if an employee could go to your Web site and find out who is out of the office or who the top salesperson was this week?

The Microsoft FrontPage Web site creation and management tool makes it easy to publish data from spreadsheets and databases on your site, which enables you to give your customers fresh information. An added efficiency is that you can utilize existing data sources without worrying about manually updating both your Web site and your internal data files.

Using FrontPage to publish data to your site

With FrontPage, you can publish data on your Web site from a Microsoft Excel spreadsheet, a Microsoft Access database, or even a text file. You can also publish data stored in an enterprise database product such as Microsoft SQL Server™. To publish data using FrontPage, the source of the data must be registered with FrontPage.

To add a data source to your site

1. Create an ASP page.
2. Prepare the data source.
3. Run the FrontPage Database Results Wizard.

In the following example, we will walk through each of these steps as we add an Access database named ProductDatabase.mdb to a page that displays product inventory data. This data is displayed in a table with two columns, titled "Product" and "Quantity in Stock."

Create an ASP page

Unlike regular HTML pages, which are static, Active Server Pages (ASP) use a server-side scripting technology that allows you to include dynamic content in a Web page. These pages are generated as HTML at the time of request, so the requester sees the data displayed in his or her browser without seeing any of the code or connection information that resides on your Web server. Fortunately, FrontPage makes it reasonably simple to create ASP pages.

To make an ASP page using Microsoft FrontPage

1. Create a new Web page, or open an existing page.
2. On the File menu, click Save As.
3. From the Save as Type field, select Active Server Pages.

Prepare the data source

With FrontPage, you can publish data from a variety of data sources; the data source that you use will depend on your own business requirements. If your data resides in simple read-only files, you can keep the data in a text file. If you need more control over the data, including the ability to make changes, you’ll find that a Excel spreadsheet or Access database works best. If your site is one where a lot of transactions take place or that uses a Web application that makes frequent changes to the data, you should consider a Microsoft SQL Server–based solution. The data preparation step will vary depending on the type of data being published.

Excel spreadsheet or Access database

If your data resides in this type of file, all that you need to do is place a copy of the data file in the virtual directory for your Web site. We will investigate how to accomplish this in the following section.

Text file or file-based source

If your data resides in this type of file, you will first need to register the data file as an Open Database Connectivity (ODBC) data source. We will not review how to do this in this article, so please refer to the Microsoft Windows online Help documentation for more information on ODBC data sources.

SQL Server database

You can easily publish data from a SQL Server database using the FrontPage Web site creation and management tool. Although we will not review how to do this in this article, the process is very similar to the one that we will investigate.

Run the Database Results Wizard

When publishing data with FrontPage, you don't have to worry about writing the scripts that connect to the data source, execute a SQL query, and write query results to an ASP page. This is all done automatically by FrontPage, and all that you have to do is to step through the Database Results Wizard. This wizard configures the database connection that the FrontPage-generated scripts use when displaying the data.

To run the Database Results Wizard

1. On the Insert menu, click Database, and then click Results. This displays the first step of the Database Results Wizard – Step 1 of 5 dialog box.

2. Click Use a new database connection, and then click Create.

3. The Web Settings dialog box shows the list of database connections available to FrontPage. If a connection is broken or in question, a broken link icon or question mark icon is displayed. To add a new connection, click Add. This displays the New Database Connection dialog box.

4. In this dialog box, you can select the type of data source to which you will be connecting. If you were connecting to SQL Server, you would click the Network connection to database server option button; if you were connecting to text files, you would click the Custom definition option button and specify the appropriate ODBC data source.

We are connecting to an Access file that is saved in your Web, so click the File or folder in current Web option button. Click Advanced to provide any additional connection information, such as username, password, and any additional parameters, if they are required when accessing your file. Type the name that you want for your connection in the Name text box, and then click Browse. This displays a screen that allows you to choose your Access or Excel data file.

 Note    When publishing data to the Internet, it is always a good idea to password-enable your data source or make it read-only. For information on security and setting up usernames/passwords, see FrontPage Security Best Practices.

5. Navigate to the desired data file, which is ProductDatabase.mdb in this example, and then click OK. This returns you to the New Database Connection dialog box. Click OK again. This returns you to the Web Settings screen.
6. In the Web Settings dialog box, click Verify to test whether or not FrontPage is able to access this new data source.
7. If the verification is successful, click OK. This takes you back to the wizard. If the verification test fails, make sure that you have supplied any needed username and password information. You can edit an existing database connection from the Web Settings dialog box by selecting the connection and clicking Modify.
8. Once the connection is verified and you have returned to the Database Results Wizard – Step 1 of 5 dialog box, click Use an existing database connection, and then, in the Use an existing database connection box, select the name of the new connection from the list. Then click Next to move to the next step in the wizard.

9. The second step in the Database Results Wizard – Step 2 of 5 dialog box allows you to select the specific set of data that you want to publish. This can be expressed as a data table, query, or other named data region, which is a requirement when using an Excel spreadsheet as a data source. You can also write a custom SQL-based query that allows you to have even more control over what data is displayed. In the Record source list box, select ProductData, and then click Next to move to the next step.

10. If you are unfamiliar with writing SQL queries, the third step in the Database Results Wizard – Step 3 of 5 dialog box provides an alternate way to restrict the data that is displayed. If you don't want to publish all of the columns in the table, click Edit List and choose the columns that you want to publish. You can also specify custom filtering options and sort order for data rows by clicking More Options. When you are finished, click Next to move to step four.

11. In the fourth step in the Database Results Wizard – Step 4 of 5 dialog box, you are asked to specify the layout of the data on the page. You can choose to publish the data in tabular form, as a list that is grouped by row, or in a drop-down list that is useful for online forms. When you are finished, click Next.

12. In the fifth, and final, step, you can choose whether to limit the number of rows returned to a single page. Often, it’s easier and faster to display a large number of returned data rows a few at a time. In this example, we limited it to 20 rows per page. Click Finish in the Database Results Wizard – Step 5 of 5.

When you have completed the wizard, FrontPage displays a representation of what your data will look like on the page. If you have followed the example, you’ll see table headers, buttons, and a single empty data row. Because the data can be viewed in real time only by requesting the ASP page from a Web server, you need to launch the page in a Web browser to see the final result. In the case of our ProductDatabase.mdb file, the results look like this:

Conclusion

Publishing live data gives visitors a compelling reason to return to your site and keeps your Web site from getting stale. FrontPage makes it easy to add data from an existing file to your site, without the fuss of complex coding. Even though our sample was fairly simple, by following the same few steps, you can use FrontPage to publish even your most intricate data.

---

Jeannine Gailey, owner of Webbish6.com, author, and Web consultant

---