Home

Database Power with Microsoft FrontPage version 2003

Table of Contents

Introduction

Databases

An Introduction to Databases and FrontPage 2002

The Global.asa File

ASP

Complete Database Solutions with the FrontPage 2002 Database Interface Wizard

Using the Database Interface Wizard to Build a Entire Web Site

Maintaining Your Newly Created Web Site

Individual Database Elements in FrontPage 2002

Steps to Creating a Simple Form in FrontPage 2002

Managing 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

Inserting Database Results with the Database Results Wizard

Summary

 


Introduction

You can use Microsoft® FrontPage® 2002 to easily integrate database power into your Web site like a true database expert.  With FrontPage 2002 you have multiple ways to perform key database tasks such as taking form information from your Web site and entering it directly into a database, displaying database content on a Web page, or adding, editing and deleting database records directly from a Web interface.   You can also use FrontPage 2002 to create reports from that data with custom ASP pages designed specifically for you.

For those who need FrontPage to handle all aspects of their Web database, the new Database Interface Wizard provides a powerful solution.  For those familiar with database functionality and need FrontPage to perform specific tasks, the database tools included with FrontPage 2002 will help you get the results you are looking for.

You don’t need database experience when integrating with FrontPage 2002 -- it does most of the work for you.  With FrontPage 2002, a Web server and just a few clicks you can:

·         Configure a Web page to send content from user forms directly to a database.

·         Add a table or list to your Web page that will retrieve and display database content.

·         Create an Access 2002 database inside your FrontPage web without opening Access 2002.

·         Connect to external databases.

·         Import an existing Access database into your FrontPage Web.

·         Edit database content from a Web interface created by FrontPage 2002.

 

What does this mean? You now have the ability to gather dynamic Web site content and organize this information into a solid, powerful database all by using one application, FrontPage. 

Databases

You can think of databases as files of information organized into separate 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 perform computations with large quantities of information. Alphabetizing by last name, sorting by part number, or grouping information are possible 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 Office XP and is one of the most popular in the world today.  Although FrontPage 2002 works with many types of databases, its tight integration with Access is one of the many benefits of the Office XP package.

FrontPage 2002 can be used to provide both a Web interface for users to enter data into your database as well as provide a Web 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.

An Introduction to Databases and FrontPage 2002

You can start database integration into your FrontPage 2002 site today.  You don’t even have to buy an additional database program.  All you need to create a Web site with database connectivity is FrontPage 2002 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.  Without FrontPage Extensions and ASP capabilities, it is impossible to utilize any of the database features discussed in this paper.

The database capabilities built into FrontPage 2002 use a technology called Active Server Pages, or ASP.  The great thing about FrontPage 2002 is that you don’t need to know a lot of (or, in most cases, any) ASP to create Web sites with database features.  You will, however, 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) online at http://www.microsoftwpp.com/wppsearch2000/default.htm. From this site, you can do a specific search of WPPs that provide hosting options for Active Server Pages, or you can browse directly to http://www.microsoftwpp.com/dbproviders/   to see 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 2002 database features. 

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

The Global.asa File

If your Web site supports ASP you may have noticed a file called global.asa in your root 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 2002 manages it for you.  

ASP

FrontPage 2002 works with ASP, or 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 2002 uses this format automatically.  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.

Editing ASP Files

Some users might want to edit ASP script using FrontPage 2002.  While it is possible to do so in FrontPage 2002, it is not necessary to get any of the database functionality described in this paper. 

By being able to edit your ASP from within FrontPage 2002, you are able to extend your ASP pages in such a way as to provide additional functionality that might not be part of your existing site.  For example, an e-commerce site that sells plants might have geographic restrictions of where it is able to physically mail plants.  An addition to the site ASP script to check zip codes of customers to make sure they are in compliance would be a vital addition to an e-commerce solution.

Some more-experienced Web developers may want to edit the ASP code for purposes such as these.  Because FrontPage 2002 supports advanced preservation of whatever code you might write without changing or rearranging it, it is possible to use FrontPage 2002 to edit the ASP code in your Web site. 

FrontPage 2002 contains a wealth of powerful features that are designed precisely to support the needs of the most advanced Web developers.  There are additional features available with FrontPage 2002 that can help develop and refine the most complex of Web pages.  If desired, advanced developers can write and debug scripts from within FrontPage, manipulate HTML code at a very granular level, and test the pages right in the same interface. To find out more, go to:

http://www.microsoftfrontpage.com/content/articles/html_tools.html

Complete Database Solutions with the FrontPage 2002 Database Interface Wizard

Some users will need FrontPage 2002 to handle all aspects of their database.  This is best handled through the Database Interface Wizard discussed in this section.  For those wishing to use FrontPage 2002 to interface with specific aspects of a Web site, the database components discussed in the next section will be ideal.

New to FrontPage 2002 is the Database Interface Wizard, a database solution that produces an entire database-focused Web site complete with Web-based administration pages.  The Database Interface Wizard is invoked the same way any new Web Site Wizard is opened: by selecting File, New, Page or Web from within FrontPage 2002.  This will open the New Page or Web task pane.  Select Web Site Templates in the New from Template option.  This will open the Web Site Templates dialog box seen below.  You can either create a new Web site or append the newly created Web site to your existing site by selecting appropriately from within the interface.

 

Using the Database Interface Wizard to Build a Entire Web Site

The first screen of the Database Interface Wizard asks you if you would like to create a new database, connect to an existing database, or use the sample (Northwind) database.  Select create a new database if you would like FrontPage 2002 to create the database for you. 

 

 

The next screen asks you to enter the name for your database connection.  Note that this is for the name of the connection, not the database itself.

After you have chosen the name for your connection, FrontPage 2002 asks you to enter the database column and input fields for the database it is about to create.  When entering the information make sure to think of all of the elements that might be used by your database as it is harder to update your Web site later.

After you choose the elements of your Web site, it will create the database and set up the connection for you.  You don’t have to do a thing.  Once the database connection has been established, you will need to select the Next button to continue.

The Database Interface Wizard then asks you what kind of database interface pages you would like the Wizard to create for you.  They are as follows:

Results Page - Produces content from your database based on the information requested.  The Results page presents the queried data in a number of different combinations that can all be edited through the FrontPage interface.

Submission Form - Produces a page where users can submit information directly to your database.  This is the main interface between your users and your site’s database.

Database Editor - This section enables you to edit your database through a Web interface instead of a traditional database client.  The Database Editor Interface page provides a complete Web interface to edit your database content including login and password security.

After you have selected the database interface pages to be created by FrontPage 2002, the program is completed and creates all of the Web content for you. 

Maintaining Your Newly Created Web Site

Once you have created a Web site through the Database Interface Wizard, it is possible to maintain all of the data in your site through the Web interface built by FrontPage 2002. You can enter data for the Web site through the Database Editor Web interface or through the Submission form that you set up for the site.  You can find results of your database through the Results page.

If you do desire to edit or change any elements of your site, the next section will provide helpful information on how to do just that.

Individual Database Elements in FrontPage 2002

You might not want FrontPage 2002 to create an entire Web-based interface for your database needs.  You might instead require database elements to be integrated into your existing Web site.  The rest of this paper will explain how to do just that.

Steps to Creating a Simple Form in FrontPage 2002

To create a new page with a feedback form:

·         Select File | New | Page or Web.  This will open the New Page or Web task pane.  Select Page Templates from the task pane.  The Page Templates Dialog Box will appear.

·         In the “General” tab, select “Feedback Form.”

·         Click “OK” in the lower right-hand corner of the “New” dialog box.

·         Edit the form to represent your specific needs.  You can see FrontPage Help (F1) for more information on how to edit and customize forms.

·         Select File | Save.

·         Name the page “feedback.asp” and click “Save”.

Note: You don’t have to use the Form Wizard to create forms in FrontPage 2002; it is one tool that might make form creation easier.  You can create any kind of form one piece at a time with individual form elements, by selecting Insert | Form, and then adding form fields to suit your needs. 

Managing Forms and Databases

Once the customer feedback form is created, you can then use FrontPage 2002 to either save the results to an existing database or create a new database to save results to.  FrontPage 2002 will connect to any database that can connect via ODBC, the Open Database Connectivity standard.  It is important to note that the only type of database FrontPage 2002 can create is an Access database.

Creating a Database Within Your Web

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

·         Right click inside the form boundary (the dotted line).

·         Select “Form Properties”.  This will open up the “Form Properties” dialog box.

·         Select the “Send to database” radio button.

·         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.

·         Click on “Database Results” tab. 

·         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 2002 has finished this process.

·         Click “OK” to dismiss the alert.

·         Click the “OK” button in the lower right-hand corner of the “Options for Saving Results to Database” dialog.

·         Click the “OK” button in the lower right-hand corner of the “Form Properties” dialog.

·         Save the page (make sure that it is saved with an .asp extension).

FrontPage 2002 will name the database based on the title of the page you created the form on.  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 2002 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 2002 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 selecting File | Import from FrontPage 2002.  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 2002.  Again, the power of being integrated with a larger Office Suite becomes clear if you follow this path:

·         Open the folder list view in FrontPage (View | Folder List)

·         With FrontPage still open, launch Windows® Explorer.

·         Navigate to the files you wish to copy to your web. 

·         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 2002, 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:

·         Right click inside the form boundary (the dotted line) on the Web page with the form you wish to connect to the external database.

·         Select “Form Properties”.  This will open up the “Form Properties” dialog box.

·         Select the “Send to database” radio button.

·         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.

·         Click on “Database Results” tab. 

·         Click the “Add Connection…” button.  The Database tab of the Web Settings Dialog box will open.  Click the “Add” button to open up the “New Database Connection” dialog box seen below.

·         Clicking the “Advanced” button on the “New Database Connection” dialog box will open the “Advanced Connection Properties” dialog box (seen 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.  An article on this process can be found online on the FrontPage 2002 Article entitled, “Collecting Sales Leads Online with Microsoft FrontPage 2002.”  The URL for this article is: http://office.microsoft.com/assistance/2000/fpSalesLeads.aspx.

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 2002 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.

Inserting Database Results with the Database Results Wizard

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 2002 interface.

To insert database results into your Web site using the Database Results Wizard, select Insert | Database | Results into the page you wish to add the results to.  Remember that all pages that interact with a database will need to be saved with the .ASP extension.

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 2002 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 select the Next button.

The final choice requires you to set up a connection that previously didn’t exist.  If you select that option and push the Create button, you will be given access to the Database tab of the Web settings dialog box.  From here, pushing the Add button 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 2002, 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.  Information about Microsoft SQL Server and the SQL language can be found online at http://www.microsoft.com/sql/ .

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, select the next button.  If you wish to show the information in only certain fields, simply select the Edit List button and choose accordingly as seen 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 for 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 display 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 2002 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 2002 Web site.

Summary

The new database integration features found in FrontPage 2002 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.  If you need to implement a complete database solution into your Web site, the new Database Interface Wizard can help you do just that.  If you need to implement elements of a previously existing database into your Web site, the tools provided by FrontPage 2002 can help you to do that as well.

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 2002, 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.  You can also set up an entire Web-based database management solution without ever having to purchase a single database product. 

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


The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Due to the nature of ongoing development work and because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only.  MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.

© 2001 Microsoft Corporation. All rights reserved.