White Paper

Table of Contents

• Introduction
• Databases
• Getting Started
• Global.asa
• ASP
• Database Connections
• Forms and Databases
• Creating a Database Within Your Web
• Sending Form Results to an Existing Database
• Connecting Your Form to an External Database
• Sending Form Results Via Email or to a Text File
• Once You Are Connected
• Displaying Form Results
• Inserting Database Results with the Database Results Wizard
• Summary

Introduction to FrontPage Database Features

White Paper

December 2000

For the latest information, please see the FrontPage Web site

You can use Microsoft FrontPage® 2000 to easily integrate database power into your Web site like a true database expert. New features built into FrontPage 2000 enable your site to collect information and enter it directly into your database. You can create reports from that data on the fly with custom ASP pages designed specifically for you. You don't need database experience when integrating with FrontPage 2000: it does most of the work for you. With FrontPage 2000, a Web server, and just a few clicks you can:

• Configure a Web page to send form results to a database.
• Add a table or list to your Web page that will retrieve and display database content.
• Create an Access database inside your FrontPage web without opening Access.
• Connect to external databases.
• Import an existing Access database into your web.

What does this mean? You now have the ability to gather dynamic Web site content and organize this information into a solid, powerful database using the interface you already know.

You can think of databases as files of information organized into fields, which may be selectively sorted and filtered as needed. Examples of a database include a customer list with names and contact numbers, or a parts inventory with product descriptions and codes. The strength of a database over a traditional list of information is the ability to quickly and concisely performs computations with large quantities of information. Alphabetizing by last name, sorting by part number, or grouping information are each functions of a database.

Databases are stored on a computer system as files. A database program or a database management system manipulates the information as needed and reports on it accordingly. Microsoft Access® is the database program that comes with the Office Suite and is one of the most popular in the world today. Although FrontPage 2000 works with many types of databases, its tight integration with Access is one of the many benefits of the Office package.

FrontPage 2000 can be used to provide both an interface for users to enter data into your database as well as provide an interface for presenting database information. An example of providing an interface for users to enter data would be a Web site that enables your users to directly enter their contact information into your contacts database for later follow up or mailing. An example of providing an interface for presenting database information would be a Web site that enables your users to examine your inventory database to see if the product they need is in stock.

You can start database integration into your FrontPage 2000 site today. You don't need any additional software. You don't even have to buy a database program. All you need to create a Web site with database connectivity is FrontPage 2000 and a Web server that is running the FrontPage Server Extensions and ASP (Active Server Pages: see more on this below). Once your FrontPage-based Web site has been published to a server, the pages with database content can be viewed as any other Web page using any Web browser like Internet Explorer or Netscape. No plug-ins or extra software is required.

The database capabilities built into FrontPage 2000 use a technology called Active Server Pages, or ASP. The great thing about FrontPage 2000 is that you don't need to know a lot of (or, in most cases, any) ASP scripting to create Web sites with basic database features. You will need to make sure that your Web server can support ASP pages. If you don't know, a simple call to them or a quick check of their Web site will provide you with an answer. To find an ISP that supports ASP, check out our list of Web Presence Providers (WPPs). From this page, you can do a specific search of WPPs that provide hosting options for Active Server Pages, or you can browse directly to a list of Web site hosting companies that are members of the Web Presence Provider program for FrontPage and have passed testing for their support of FrontPage 2000 database features.

Next, let's get familiar with some terms and technology to help you understand how FrontPage database features work.

Global.asa

If your Web site supports ASP you may have noticed a file called global.asa in your directory. This file is vital to database integration in your site - it tells both the Web server and browser how to handle the database information. You won't want to touch it. Don't worry about what to do with the file: FrontPage 2000 handles manipulation of it for you.

ASP

FrontPage 2000 works with ASP (Active Server Pages), which are scripts to provide the necessary interaction with the server and to allow visitors to the Web page to see updated data every time they visit. All pages at the site that interact with the database will have to be saved as ASP pages with the .asp file extension. FrontPage 2000 uses this format automatically, so you do not have to worry about being able to create or edit ASP pages; FrontPage takes care of that for you. Attempting to change the filename extensions will result in your pages not working correctly, so be sure to leave files created as ASP pages (filename.asp) with the .asp extension.

Some more experienced Web developers may want to edit the ASP code for their own purposes, writing additional ASP code to work in their Web site. Because FrontPage 2000 can do much of the "grunt work" for you -- which you can then update and embellish as you so choose -- many developers have opted to use FrontPage 2000 for the initial steps in the development process. Because FrontPage 2000 supports preservation of whatever code you might write without changing or rearranging it, it is possible to use FrontPage 2000 to edit ASP code. (NOTE: Changes you make to ASP code that FrontPage generates automatically may not be preserved.) If you want to learn more about customizing FrontPage database capabilities with ASP, a great tutorial about Active Server Pages can be found at the MSDN®developer program Online Workshop.

Database Connections

In order to connect to a database, FrontPage 2000 must store some information about that database. This information is stored in global.asa as a database connection. FrontPage 2000 handles the difficult issues of database connectivity and requires your intervention with specific database information only if you wish to access a database outside of your existing Web site.

Scenario

Suppose you want to create a feedback form for your e-commerce Web site Bob's Ship of Tools that will allow customers to suggest products and services they would like to see offered in the future. Using a FrontPage 2000 Form Wizard, you can follow the steps below to create the feedback form and configure it to save results to a database.

Steps

To create a new page with a feedback form:

1. On the File menu, point to New, and then click Page. The NewNew Page icon on the FrontPage 2000 desktop.
2. Click the General tab, select Feedback Form.
3. Click OK in the lower right-hand corner of the New dialog box.
4. Edit the form to represent your specific needs. You can see FrontPage Help (F1) for more information on how to edit and customize forms.
5. On the File menu, click Save.
6. Name the page feedback.asp and click Save.

The following is an example of a sample form created for the imaginary Bob's Ship of Tools with the Feedback Form Wizard. Note that FrontPage 2000 makes it easy to change the layout, add color and graphics and customize the form to reflect the look and feel of your Web site.

By default, when you create a form using this Wizard, the form results will be saved to a text file. The next section will explain how to save the form results to a database instead.

Note  You don't have to use the Form Wizard to create a form. You can create a form a piece at a time with form elements inserted by selecting Insert and Form, and then adding form fields to suit your needs.

Once the customer feedback form is created, you can then use FrontPage to either save the results to an existing database or create a new one. Although the examples here use an Access database, this same concept could be applied to any other database that can connect via ODBC, the Open Database Connectivity standard. It is important to note that the only type of database FrontPage 2000 can create is an Access database.

Creating a Database Within Your Web

It is easy to create a database from scratch using FrontPage 2000. Once you have completed your form, follow these simple steps to automatically create a database in which to store your form information:

1. Right click inside the form boundary (the dotted line).
2. Select Form Properties. This will open up the Form Properties dialog box.
3. Select the Send to database button.
4. Click the Options button in the lower left-hand corner of the Form Properties dialog box. This will open the Options for Saving Results to Database dialog box.
5. Click on Database Results tab.
6. Click the Create Database button. FrontPage will create a new Access database, as well as the database connection in global.asa that you can use for later reference to that database. An alert will pop up to let you know when FrontPage 2000 has finished this process.
7. Click OK to dismiss the alert.
8. Click the OK button in the lower right-hand corner of the Options for Saving Results to Database dialog.
9. Click the OK button in the lower right-hand corner of the Form Properties dialog box.
10. Save the page (make sure that it is saved with an .asp extension).

    FrontPage 2000 will name the database based on the title of the page you created the form on and will place the database in a new folder in your Web site entitled FPDB. This prevents others from browsing to your Web site and downloading your database as a file.

    A Results table will be created in the database and will contain one column for each form field as well as some additional columns (Remote Computer Name, User Name, Browser Type and Timestamp) that you might find useful.

    You can test the form by publishing your Web site to your Web server and then viewing the page you've just created in the browser. Go ahead and complete the form and then click the Submit button. You can verify that the form results were saved to your database by creating a Database Results Region described below in Displaying Form Results. That's it! Just a few steps and FrontPage 2000 will create a database for your site and write all of the ASP code required to save form results to the database.

Sending Form Results to an Existing Database

Part of the power of database integration with FrontPage 2000 is that you don't have to start from scratch. You may already have an existing Access database that you would like to integrate with your Web site. To do this, you must first import the database into your Web site and create a connection to it. This too, is easy, and you have two options to help carry it out.

The first option is to simply import that database by on the File menu, click Import from FrontPage 2000. This will open an Import dialog box that will let you add any file to your Web site. You can add your database to your existing Web using this method quickly and easily.

The second option entails taking advantage of the drag and drop features provided by FrontPage 2000. Again, the power of being integrated with a larger Office Suite becomes clear if you follow this path:

1. On the View menu, click Folder List.
2. With FrontPage still open, launch Windows Explorer.
3. Navigate to the files you wish to copy to your web.
4. Drag and drop these files into your FrontPage web.

   In both of the above methods, FrontPage will recognize the .mdb file extension of the Access database and prompt you to create a new connection for that database. Go ahead and enter the database name and click OK.

   You can then connect any form to that database using the methods described here.

Connecting Your Form to an External Database

You might find yourself in a situation where your database is hosted outside of your Web site. Because of the power of the Internet and the ease of use offered by FrontPage 2000, you can connect to any database on the Internet from your Web as long as it is available on the Web and is ODBC compliant.

Before you connect to the database, you will need to determine the server name and passwords required to access your database. The person in charge of the database will be able to provide you with the information necessary to make a connection. An example of when this approach might make sense is when your site becomes very popular and its requirements outgrow the capabilities that Access offers. So you might ask your Web site hosting company to set up a SQL Server database for you to use to store your important Web data. When they do this, they will supply you with the server name, user name, and password necessary to connect to it from your forms in FrontPage.

Once you have figured out the path, login and password to the external database, connecting is simple:

1. Right click inside the form boundary (the dotted line) on the Web page with the form you wish to connect to the external database.
3. Select Form Properties. This will open up the Form Properties dialog box.
4. Select the Send to database button.
5. Click the Options button in the lower left-hand corner of the Form Properties dialog. This will open the Options for Saving Results to Database dialog.
6. Click on Database Results tab.
7. Click the Add Connection button. The Database tab of the Web Settings Dialog box will open.
8. Click the Add button to open up the New Database Connection dialog box seen below.

   

9. Clicking the Advanced button on the New Database Connection dialog box will open the Advanced Connection Properties dialog box (see below). Enter the Username and Password given to you by the database administrator and enter any additional parameters required through the dialog box interface.
   
   

   

Sending Form Results Via Email or to a Text File

If you don't have or need database connectivity, you can send the information from your form to an email address or to a text file on your Web site. For more information, see the article Collecting Sales Leads Online with Microsoft FrontPage 2000.

Once You Are Connected

Congratulations! You now know how to add a feedback form to your Web, save it as an ASP file, and configure the form to send the data it collects to a database that FrontPage 2000 either created or set up the connection for. You can use these same steps to send the data collected by any form in a FrontPage Web to a database.

At this point, you now have a database at your site able to receive user input. This might satisfy your needs. If not, you probably are looking for a way to present database information in your Web site. In this section, you will learn how to use FrontPage to display the results of a database table in your browser.

Displaying form results is a little more detailed than sending form results to a database, but is still accomplished quickly and easily using the FrontPage 2000 interface.

Inserting Database Results with the Database Results Wizard

One of the new features provided by FrontPage 2000 is the powerful Database Results Wizard. This Wizard lets you pull information from any ODBC compliant database, and present it on your Web site in 5 simple steps. The Wizard is started by on the Insert menu, point to Database, and then click Results from the Web page you would like to display the results on. Like most Wizards, the process of using it will provide a standard looking output that can be customized.

Step 1: Selecting the Database

The first obvious option you are given is to select the database you would like to get the results from. The dialog box offers three choices.

The first choice is to connect to Northwind, the sample database provided by FrontPage. Use this feature if you wish to experiment with database results without having to worry about database creation.

The second option is to use an existing database connection. FrontPage 2000 remembers all of the database connections you've ever worked with in the Web site you are editing. Your choice is probably in the drop down menu. If this is the case, pick the database you want to connect to and click Next.

The final choice requires you to set up a connection that previously didn't exist. If you select that option and click Create, you will be given access to the Database tab of the Web settings dialog box. From here, clicking the Add will open the New Database Connection dialog box (pictured below) that will let you set up a connection to either a File or folder in the current Web, a System data source on web server, a network connection to database server, or a custom definition. Select the options suited for the task and your connection is established.

Step 2: Record Source

Because databases can contain great amounts of information, it is important to choose which parts of the database information you would like to display. The second step asks for the Record Source, which is the table you'll be pulling your data from.

If you built the database from FrontPage 2000, you will only have one choice. If you are connected to a database with multiple tables, you will need to choose the one you wish to connect to.

Another option here is to create a custom query. If you know SQL (the Structured Query Language - not to be confused with SQL Server™, the popular Microsoft Database Product) you can enter the appropriate SQL code and choose exactly what you want to view. For more information about Microsoft SQL Server and the SQL language, see the SQL Server Web site.

Step 3: Selecting Fields

Once you have selected the Record Source (table) you wish to get your information from, it is time to choose the fields that you wish displayed in your Web page. If you want to show all of the fields, click next. If you wish to show the information in only certain fields, simply click Edit List and choose accordingly (see below).

You will notice that there is a More Options button in the Step 3 dialog box. This button will take you to another dialog box enabling you to choose more specific criteria for ordering the data. As seen in the screen below, you can both set up a filter to match specific criteria as well as set the ordering structure for the results.

Step 4: Formatting

Now that you know the database, table and fields you wish to show, the goal now is to determine how you would like to displayed the data. The Database Wizard gives you the capability to either present your data in a Table or Column option.

The three check boxes in step 4 enable you to decide if you want to use a table border, expand the table to the width of the page, and include a header row with the column labels.

Step 5: Grouping the Results

Finally it is a simple choice of grouping your results. Results generated form databases can be long and overwhelming. Luckily, FrontPage 2000 has a solution for that issue as well. Step 5 gives you the opportunity to split the results into a fixed number of records per page. The default is 5 records, but the choice is yours to make.

That's It

Once you've gone through these five simple steps, the appropriate ASP code is written and your Web site reports accordingly. You need to save your page as an ASP page (filename.asp). You can view the Web pages from any browser, and every time your page is visited, an updated snapshot of the database contents is presented on the page. You have integrated a database results with your FrontPage 2000 Web site.

So there you have it. It really is that simple. The new database integration features found in FrontPage 2000 give you both the power to integrate database content into your Web site as well as program dynamic content to your site without spending weeks in a database class or learning complicated programming skills. You have taken the first steps towards moving from the position of a developer of static Web sites to that of an interactive programmer, all without having to know programming.

Now that you understand database connectivity options and how they integrate easily and simply with FrontPage 2000, consider the options available to you: You can publish an interactive address book for members of a club you belong to, you can enable your potential customers to populate your contacts database without you having to enter a thing, you can make inventory information made available online or create the first part of an e-commerce engine that takes orders directly from the customer.

Databases are exciting. The power provided by FrontPage 2000 makes database integration simple. The possibilities are endless, and they are yours to explore.