Sending Form Results to a Database

In situations in which you want to send your form results straight to a database, the Send To Database form handler is a great option. One of the best things about this component is that you can decide what information you want to collect and design your form the way you want it, and FrontPage can automatically create a database tailor-made to your form results.

Let's reconfigure the form on comment.htm to store results in a database instead of a file:

1. Open comment.htm.

2. Right-click the Submit button and select Form Properties.

3. Select the Send To Database option (see Figure 18.4 shown previously).

4. Click Options to access the Options for Saving Results to Database dialog box as shown in Figure 18.24.

   Figure 18.24. The Options for Saving Results to Database dialog box.

   

If the Send To Database option is not available, see "Send To Database Not Available" in the "Troubleshooting" section of this chapter.

The Database Connection to Use dropdown is currently empty because there are no database connections in the current Web site. You can click the Add Connection dialog box to create a new database connection, but in this case, you want FrontPage to create a database for you so that it will contain all the necessary database fields to store results of your form. Click the Create Database button, and FrontPage will generate a new database for your form and save it to the fpdb folder in your Web site, as indicated by the message box in Figure 18.25.

Figure 18.25. FrontPage creates a database customized for your form.

For more information on database connections in FrontPage, see "Creating a Database Connection," p. 706.

FrontPage has now populated part of the Option for Saving Results to Database dialog box for you as shown in Figure 18.26. Note that the Database Connection to Use dropdown now has comment selected. This is the name of the database connection that FrontPage created for you. The Table to Hold Form Results dropdown has also been populated with Results, the table name in the database that FrontPage created for you. If your database contained more than one table, you would need to select the correct table in the Table to Hold Form Results dropdown.

Figure 18.26. The Options for Saving Results to Database dialog is configured with the newly created database.

TIP

As with the Send to File and E-mail handler, you can configure a custom confirmation page and a custom error page. However, the confirmation page for the Send To Database handler must be an ASP page. If you would like to use confirm.htm that you created earlier, you will need to open it and resave it as an ASP page first. No other changes are necessary.

Click the Saved Fields tab. As shown in Figure 18.27, both of your form fields appear in the Form Fields to Save list and they are mapped to the database columns by the same name.

Figure 18.27. The Saved Fields tab is where you can map form fields to database columns. FrontPage mapped these for you.

When FrontPage created your database, it created a database column for each of your form fields. It mapped the form fields to their respective database column.

Click the Additional Fields tab to configure any additional fields you would like to save. As shown in Figure 18.28, four additional fields are being saved. These are the same additional fields available to you in the Save to File or E-mail component, and these are the only additional fields available.

Figure 18.28. You have the option to include one or more of the additional fields provided by FrontPage.

TIP

If you cancel out of the Options for Saving Results to Database dialog box, you will have to remap all fields when you go back in to it.

Click OK in the Options for Saving Results to Database dialog box and then OK again in the Form Properties dialog box. FrontPage will inform you that you need to save this page as an ASP page, as seen in Figure 18.29. Save the page as comment.asp.

Figure 18.29. Send to Database pages must be saved as ASP pages.

Now preview the page in your browser, enter some information, and then click Submit. Your information is written in to the comment.mdb Access database in the fpdb folder of your Web site. If you have a copy of Microsoft Access, you can open the database and see your data. If you don't have a copy of Microsoft Access, you can create a Database Results Region to display the information in the database. A Database Results Region is a FrontPage component that can display data from a data source such as a Microsoft Access database on a Web page.

If you submit the form without errors, but no information is written to the database, see "Form Results Don't Appear in Database" in the "Troubleshooting" section of this chapter.

For more information on using the Database Results Region, see "Using the Database Results Wizard," p. 709.

NOTE

The Database Results Wizard pages you create with FrontPage do not require the FrontPage Server Extensions to be installed when users are browsing to them. The Send to Database form handler requires the FrontPage Server Extensions 2000 or later to be on the server, or it will not work.

Adding New Fields

Now let's say that you have decided to also collect the email address of everyone filling out your form. FrontPage has already created a database based on what is on the form already, but that's not a problem. The Send to Database form handler has the capability to dynamically add fields to your database as your form evolves.

1. Open comment.asp. Make sure that you open the ASP page and not the comment.htm page.

2. Add a new text box form field between the txtName field and the txtComments field. For details on inserting a new form field, see "Creating a Basic Form" earlier in this chapter.

3. Name the new text box form field txtEmail.

4. Add any validation you would like to the new text box.

5. Right-click the Submit button and select Form Properties.

6. Click the Options button to display the Options for Saving Results to Database dialog box.

7. Click the Update Database button to update the database with your new form field.

FrontPage will add a field to the database called txtEmail and will display a confirmation as shown in Figure 18.30.

Figure 18.30. FrontPage can dynamically update your database when you add new form fields.