FrontPage Tips by Ward Cameron Enterprises
Take Your Web Site to New Levels

Using FrontPage's DRW to Edit Records in A Database

This tutorial will give you a simple way to edit a record in a database using a web form. FrontPage is an excellent tool for creating forms specifically designed to add data to a web site, but what do you do when you want to edit one of those records?

The process involves several steps

1. Create a way to select the record to edit

2. Create the database change form ProductEdit.asp

3. Create ProductUpdate.asp

4. Make It Look Nice

Drop-down Boxes

Boolean Blunders

5. Putting It All Together

6. Troubleshooting

The process takes three steps. The first is to create a pick list to list multiple records so that you can select one to be edited. We'll show you how to create this pick list and then link to a second page called ProductEdit.asp. This page will contain a form that will allow you to make changes to your record. This page does not actually make the changes though. It sends your changes to a third page called ProductUpdate.asp. This page contains the code that will update the appropriate record in the database.

So let's get started

Step 1 - Select the Record

If you want to edit a record, you need to have a way to select a single record. In our lesson - Passing Parameters, we showed you several ways to key out a single record. In this case, you need to add an edit link to your Database Results Wizard Results page. Here is a simple example.

Insert a Database Results Wizard that will list all of the products in your catalog. Then add an additional column with Edit Record as the heading.

Now in the column, beside the product name field, enter the word Edit. Highlight it and select Insert Hyperlink. Point the link to ProductEdit.asp and select Parameter. Insert the parameter ProductID = ProductID. Now clicking on this link will take you to the ProductEdit form with the data for this value already loaded.

Step 2 - Create the Database Change Form

Step 3 - Create ProductUpdate.asp

The final step will take the form changes and update the actual database record.

Open a blank page. Now we'll create a Database Results Wizard (DRW) that will update a record in the current database.

Select the Northwind connection from the list of database connections and click "Next".

Select Custom Query and then Edit. Type the following into the Edit window. You must type it exactly as it appears below:

UPDATE Products
SET
ProductName='::ProductName::',
SupplierID='::SupplierID::',
CategoryID='::CategoryID::',
QuantityPerUnit='::QuantityPerUnit::',
UnitPrice='::UnitPrice::',
UnitsInStock='::UnitsInStock::',
UnitsOnOrder='::UnitsOnOrder::',
ReorderLevel='::ReorderLevel::'
WHERE ProductID=::ProductID::

Don't forget that the end of the last field name (in this case ReorderLevel) you DO NOT put a comma at the end of the line. Also, be sure there is no comma on the final line with the WHERE statement.

Here are a few rules about formatting the various types of fields so that they update correctly.

*Boolean fields are those fields that use a yes/no true/false or on/off value. They include most radio and check boxes.

For more information on the correct SQL for various field types and situations, check out this Microsoft Support Document:

http://support.microsoft.com/default.aspx?scid=kb;[LN];306430

As you can see, different types of field need different formatting. Be sure to match your format with the correct data type.

Your Custom Query screen should look like this:

Once you have typed in the custom SQL code, click the "Verify Query" button to make sure the code can be verified. This will help ensure that you have typed the code correctly and that you have not misspelled your field names. You should see the following screen shot:

If you get a message indicating that it cannot verify the query, delete all the field values except for the first one. See if it can be verified. Then add the second field. Add each field one by one until you get the error message, this will tell you where the problem lies. Once all of your SQL has been correctly entered, you should be able to verify the query.

Click OK, and then More Options

Whenever using an edit script, it's critical to have default values entered into every field. They don't need to be relevant, but your form needs data to input if you leave a field blank. The update script cannot deal with blank data.

Always enter a default value to prevent getting error messages in your browser. If You DO get an error, the message may resemble the following.

or even more frightening:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers

The above message simply means that the DRW expected to receive a parameter, but did not get one. By entering a default, you will always make sure it gets a parameter, even if it does not correspond to a record in the database.

In the Defaults screen, double-click on Product Name to open the Default Value screen.

For each field, type a default value. For the Product Name Field, input None. Now enter a value for each field in the form.

Your Defaults screen should now look like the following, with a value entered for each field.

How do you keep that date from showing up in your FrontPage. You do that using the iif statement as described in this tutorial.

In this case, in a blank column in your query use the following syntax: 

Date: IIf(IsNull([DateField]) Or [DateField]=#1/1/1950#,””,[DateField]) 

Assuming the date field in your form is called DateField, then this will take that data and create a new field called Date. If the date field is null OR if the date is equal to 1/1/1950 then it will simply display nothing (signified by two double brackets side by side).

This should solve your problem. Don’t forget to include the date value in # rather then “.

Click OK to return to the More Options screen and OK again to return to Step 3 of the DRW. Click Next to move to Step 4. Make the selections shown in the following screen:

Click next. In Step 5 select Display all records together and deselect Add Search Form. Finally click Finish.

Finally, click finish and you'll see a DRW that seems to contain nothing. Do not delete it. It is ready to go. Save this page as ProductUpdate.asp.

Now let's make it look nice

One of the challenges of using the DRW to create your edit forms is that it creates really ugly forms. These basic forms also take away some of the interactivity that is available when you create your forms from scratch.

Don't despair. You can recreate the interactivity and visual appeal of your original forms with another touch of FrontPage Magic.

Before you read on, pause for a moment. DO NOT move beyond this point unless you have update forms that are currently working. This section is for improving the cosmetics only - not the functionality of your forms.

The easiest way to recreate the look of your original form is to copy the various fields, one at a time, and copy them into your FrontPage DRW generated form.

To understand the steps, lets take another look at our original form.

Is that ugly or what? The secret lies in replacing the individual fields with fields that are truly relevant for each field. If the field is best displayed as a text field, than you don't need to do anything else.

Now we can delete the ProductID field and send that as a hidden variable as we did in Step 2, so we don't need to worry about reformatting that field. Now let's look at how we can replace the text fields with other field types.

Text Area Fields (Memo Fields)

If you want to replace one of the text fields generated by FrontPage with a text area field, than place your cursor insertion point beside the field you would like to replace. On the program menu, On the program menu select Insert-Form-Text Area. This will place a text area box beside the field to be replaced. For the sake of example, let's assume that you want to replace the ProductName field with a text area box.

Your screen will look like the following:

Here you can see a blank text area box inserted into the same cell as the text field automatically generated by FrontPage. The trick now lies in replacing one field (the text field) with the new text area box.

Notice the text that is currently displayed in the ProductName text box. This text code tells the field what data to display. The syntax is as follows:

<%=FP_FieldHTML(fp_rs,"ProductName")%>

To have the text area box take over this role, all you need to is:

1. Double click the ProductName field to open the Text Box Properties dialog and copy the text in the Initial Value area to the clipboard.
2. Double click on the new Text Area Box to open its properties and paste the code into the Initial Value area. In the field name, type ProductName (the spelling must exactly match the current database field that you are replacing).
3. Delete the original ProductName field. You have now replaced the FrontPage generated field with a text area box that will display the date from the selected record.

Here is what the TextArea Box Properties of the original field looked like:

Drop-Down Boxes (Combo Boxes)

What if you want to use drop-down boxes to make your editing selections. This is almost as easy as the previous example where we replaced the text box with the text area box.

1. Insert a drop-down box field beside the field you want to replace. Don't delete the FrontPage Generated field yet.
2. Double click the FrontPage Generated field and copy the Initial Value to the clipboard.
3. Double click the drop-down box to open the drop-down box properties and add a new value. Paste the field code into the Choice field and choose Selected under Initial State.
4. Add any additional values to the drop-down box.
5. Rename the field to match the field name you are replacing, and delete the FrontPage generated field. In this case, let's replace the ReorderLevel field.

Essentially what you have done is added a drop-down box that will have as its first value the data currently stored in the database record.

Beneath this will be the optional choices that you have added to the drop-down box.

The graphic on the left shows what the Modify Choice dialog looks like when you add the default drop-down box value. In this case, the ReorderLevel field.

Also, notice that the Initial State has been set to "Selected".

Click here to see an improved version of the form

Boolean Blunders

If ever there was a place for a drop-down list box in an edit form, this is it. Boolean fields, those that have values of yes/no, on/off or true/false, can be some of the most stubborn to get working.

Regardless of how your access database is set up, you will need to send the value for your boolean field as true/false. Your database will convert the value to the format it prefers, whether that is yes/no, true/false or on/off.

In terms of the ProductEdit.asp and ProductUpdate.asp forms, leave your boolean fields for last. Only add them once you have everything else working as they are usually the ones that cause the most problems.

Drop-down lists allow you to add a value for both true and false, as well as a place to display the value currently stored in the database record. In most cases, your site visitors will be more familiar with the choices yes or no for boolean fields, so set up the drop-down list in the following way:

1. Let's assume we are adding a drop-down box to replace the Discontinued field. Double click the Discontinued field that was created by the FrontPage Database Results Wizard. Copy the text from the Initial Value.
2. Place your cursor insertion point beside the FrontPage field you are planning to replace with the drop-down list. From the menu, select Insert-Form-Drop-down Box.
3. Double click the drop-down box to open the drop-down box properties (Figure 1). Click the add button (Figure2).. Under Choice, paste the value that your copied from the previous field. Also, be sure to choose "Selected". This will make sure that the value currently stored in your database will be selected automatically in your drop-down box.
4. Add two more options, one for yes with "True" as the specified value, and another for no with "False" as the specified value.

Figure 1 - Drop-down Box Properties

Figure 2. Modify Choices of your Drop-down Box

Here is how your final drop-down box will look:

True Yes No

The first (top) value will be the value currently stored in your database for that record. In this case "True". Below it will be your yes/no options with each set to send the data as true/false so that it will be correctly interpreted by FrontPage.

Click here to see an improved version of the form with this boolean example.

This should help solve the challenge of boolean fields. Remember, different systems seem to show different default formats for boolean data. It's much easier to give it the data in the format it wishes than to try to force it to bend to your whims.

Putting it all Together

Now you've created a pick list to select a record to be edited. This linked to ProductEdit.asp where you were able to edit your product information. Finally, submitting this form, sends the page to ProductUpdate.asp which does the actual updating of the database.

The last step was to make it look nicer.

Now it's time to test it all. Open your pick list, and select a record to be edited. This should open up the ProductEdit.asp form with the correct data loaded. Make some minor changes to EVERY field on the form, and then submit the form. If you get the message "Your record has been updated", than FrontPage believes that it has successfully made the changes to the Access file.

Don't relax yet. Click the back button on your browser to return to the edit form. Now click the refresh button on your browser. If the changes that you just finished making are loaded when the page re-loads, than you HAVE successfully completed the project. If the old values reload, than you will need to head back to the drawing board.

Trouble Shooting

1. Check that your ProductEdit.asp form passes the ProductID field to the ProductUpdate.asp page one time only - either as a row in the form OR as a hidden field - not both.
2. Double check the Custom SQL on your ProductUpdate.asp page. Different field types have different syntax for creating updateable fields in the SQL statement.