Database Wizardry with FrontPage 2000

By William F. Slater III

Web-database applications are important for many reasons. Besides the ease of updating and querying your data, they've become the foundation of most e-commerce applications. Unfortunately, for many of today's Web professionals, creating sophisticated databases is still a challenge.

With each new release of Microsoft FrontPage, there are more and more features to help nonprogrammers develop Web sites. One of the more interesting features of the Office 2000 suite lets you easily build a Web-based database application without coding. You can now develop a simple database with Microsoft Access, use a wizard to easily integrate it into your Web site, and then use FrontPage to create a Web site that can use this database. The feature is called the Database Results Wizard. This article will describe how to create such an application using Access, FrontPage, ODBC, Personal Web Server with ASP middleware configured, a browser, and a few very basic SQL statements. I'll also describe the process for getting such an application installed at your favorite ISP.

This is a Web-based name- and email-list collection application. When users view the index.htm file in the root directory they're presented with two hyperlinks that let them either enter a record or view existing records. If they choose to enter a record with their name and email address, they click on that hyperlink, then enter that information from a form. Submitting the form will invoke a routine written in VBScript with SQL that will store the data into a database table. Users can also view a list of all who have entered their names into your database. It's a simple application, but it's also a great way to learn about Web-based database development and take advantage of some of the capabilities that MS Office 2000 provides.

Follow the Yellow-Brick Roadmap

Figure 1 outlines the steps in creating this application. We'll be using FrontPage 2000 to start a new Web project, Access 2000 to create the database, and then FrontPage 2000 again to develop the application. The entire process is wizard driven, but creates some VBScript behind the scenes. The only coding you'll need to do is to enter a SQL statement into one of the wizard's dialog boxes.

First we open FrontPage, and create a simple one-page "Web" -- Microsoft's term for a Web project -- that we'll call Webdb. Next, we'll use Access 2000 to create a new database called Webdb.mdb. The table within the database will be called names, as shown in Table 1. Be sure to note the folder in which you create and save this file. You'll need to know where it is when you get ready to import it into your new Web project. Table 2 is a complete list of the files you'll create along with the function each one performs in this application.

Import the Database and Create the Database Connection

FrontPage 2000 works with your ODBC settings in the background to create your ODBC database connection information. This happens when you import the Access database file into your Web project using the following commands from the FrontPage 2000 menu. From the menu, click on the File | Import option, which will bring up the Import File dialog box. Click the Add File... button to locate the database file to import into your Web page.

As FrontPage adds the Access database to your Web, it pops up a dialog box that asks if you want to add a database connection. Click on the Yes button and you'll automatically add an ODBC data source name to your ODBC settings. This simplifies database access for your Web application.

At the end of this process, a message box pops up to inquire if you want to have your newly imported database stored in a newly created directory named \fpdb, which will be located in your Web. I usually click on the Yes button.

Next, start creating the pages in the same order as shown on the flow chart in Figure 1. The first page is named index.htm. The "Enter Data" hyperlink links to the Enter_Data.htm page and the "Retrieve All Records" hyperlink links to the Retrieve_data.asp page directly.

The Enter_Data.htm page is the data-entry form of your application; this form, when submitted at run time, invokes the ASP file (Enter_Database_Insert.asp), which contains your SQL Insert statement. This form and the ASP file are linked at design time by means of the Form Fields Properties dialog box.

When you create this form, you have to right-click on each field to invoke this dialog box. Be sure to name each of your fields after the corresponding column name in the Names table. This is necessary to help make the SQL Insert statement work properly.

The Form Properties dialog box is displayed in two parts. The first part is where you link the form to the ASP file that contains the code to process records from the form directly into the Names table of the database. Type the name "Enter_Database_Insert.asp" in the form name text box and click on the Options... button.

Clicking on the Options... button brings up the second part of the Form Properties dialog box. Type the name "Enter_Database_Insert.asp" and click on the OK button, and then OK again.

The Enter_Database_Insert.asp page is displayed after the Database Results file gets executed by clicking on the Submit button on the data-entry form above. The VBScript in this file is generated by FrontPage 2000 when you create the page. (Remember that this is the page that stores your SQL Insert statement.)

When you add a database-results area for each of the ASP files (for record inserts and for data retrieval), each time you create the page and select Insert | Database you'll be stepped through the following sequence with the Database Results Wizard.

The Database Results Wizard

This wizard is a facility that FrontPage 2000 uses to gather information from you about the data you intend to access for a form. The output of a Data Results Wizard is an ASP file, with VBScript and a SQL statement in it. The ASP.dll layer works with IIS to execute this code and send the results back to the user's browser.

Generally speaking, these types of database accesses are going to either be data retrieval or data modification. To use this wizard, you'll need to remember the name of your Data Source Name (Webdb) and the record source (table) you're accessing from within the database.

In the first example -- the SQL Insert statement in which you actually work with the database to insert a record into the Names table -- you must click on the Custom Query radio button and click on the Edit button, which will open a Custom Query dialog box. Type in the SQL statement over the highlighted Select statement. Here's your SQL statement for the record Insert operation:

Insert into Names (First_Name, Last_Name, EMail)

VALUES

('%%First_Name%%',

'%%Last_Name%%', '%%EMail%%')

It's also shown being typed into the dialog box in Figure 2. Next click on the OK button. At this point, you can click on the Verify Query button to see if your SQL Insert statement is syntactically correct, and if it makes sense against the current database connection you've designated.

After you click on OK, you'll see the "Step 2 of 5" dialog box. It doesn't mean you've made a mistake. It's just the Database Results Wizard's strange way of moving you along the path to get on to Step 3. Click the Next button to continue to Step 3.

The wording in this dialog box would suggest that you're retrieving records from the database, but since you're inserting records into the database, ignore the message and click on the Next button to continue to Step 4.

No records are returned from an Insert query, so click on the Next button to continue to Step 5, the final step. Ignore the message about displaying all records together, and close the Database Results Wizard by clicking on the Finish button.

When you complete the Database Results Wizard's steps, you'll see a Database Results area form. When you close this file, you'll be prompted to save it. Use the name "Enter_Database_Insert.asp." At this point, FrontPage 2000 has accessed a VBScript template for accessing an ODBC-compliant database, and -- using the SQL statement you've inserted -- has created a database-access routine in an ASP file that processes the data entered into the data-entry form.

Retrieve_Data.asp is the page that's displayed after the Database Results file is executed. The VBScript code in this file is generated by FrontPage 2000 when you create the page. This page stores your SQL Select * statement. Figure 3 shows the results of the execution of the SQL statement. Notice that all the records get fetched from the table.

To create the Database Results area for the data retrieval page, you perform almost exactly the same steps you used to create the Database Results area shown in the previous example for inserting records, except that the SQL statement is different: Select * from Names.

Testing the Application Locally

If you did everything correctly, you should be able to test your application by launching your browser and typing localhost/webdb into the address field. (If you know the name of your computer, typing that in place of "localhost" should also work). When the first page comes up, you'll have a choice to Enter Data or Retrieve All Records. Click on the Enter Data hyperlink to enter a record. After you've filled out the form and clicked on the Submit button, a new record will be added to the Names table of the Access database you created. To view this and any other records you add, click on Retrieve All Records on the main page. When you're satisfied that you can insert and retrieve records successfully, you have completed the test of this application.

Installing Your Application on Real Hardware

If your PC isn't directly connected to the Internet as a Web server, you still have one last step to take care of -- getting your Web site and database installed at an ISP or hosting company. Herein lies the trick: When you publish your Web-based database application, the ISP must take care to set the ODBC connection on its Web-server platform to point to the Access 2000 MDB file for your Web site (see the box titled "What to Ask Your ISP"). The ODBC Administration applet in the Control Panel (this is true for both NT and Windows 9.x) is where these settings are configured. Also, if you "upsize" your Access 2000 database to a SQL Server database or any other SQL-compliant database, your ISP will have to ensure that the ODBC drivers for that database are present on the server, as well as getting the ODBC DSN and physical disk location settings correct.

Conclusion

As you can see, using the steps outlined, a person can quickly build Web-based database applications without coding. But the process does require some knowledge of Web development, Microsoft tools, and the patience to walk through the Database Results Wizard screens. These screens aren't always the easiest to understand, and it can be confusing when they return you to the same screen after you've filled out multiple forms in a substep, giving no indication that you've completed the step.

Although I applaud Microsoft's effort to provide simple database development tools, I wish they had made them more straightforward. (And keep in mind that these comments come from a person who has hooked his career to Microsoft technologies since back in the mid-1980s.) However, if you can put up with the quirks, Microsoft has created a powerful user development environment for those who want to avoid the dirty details of database programming.

---

William works at American Digital Corp. in Chicago, doing network engineering, particularly with Microsoft technologies, Web development, database-management systems, teaching, mentoring, and writing. Contact him at [email protected], or visit billslater.com or slatertech.com. Check out his 14-node network at billslater.com/wfs_lan.jpg.