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

Using FrontPage's DRW to Delete Records From A Database

This tutorial will give you a simple way to delete a record from a database using a hyperlink in any web page. 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 delete one of those records?

The process involves several steps

1. Create the DeleteRecord.asp Page
2. Create the DeleteConfirm.asp Page
3. Create A hyperlink to the DeleteConfirm.asp page

Step 1 - Create DeleteRecord.asp

Let's begin with the code that will actually delete the database record.

Open a blank page. Now we'll create a Database Results Wizard (DRW) that will delete a record in the current database. For this example, we'll use the Northwind Database that comes included with Microsoft Access.

Again, for this exercise, let's assume you want to delete a record from your product table. To do this, you' need an accurate way to identify a specific record so that ONLY that single record is deleted. This is done by selecting the record based on the unique key that you've already set up in your database table. Since no two records can have the same key, your key will be unique for each record. In the Product table, the ProductID is the key field that should be used to select the record to be deleted.

On a blank line, select Insert-Database-Results...

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

On this screen, select the "Custom query" radio button and click "Edit". Type the following into the Custom Query screen.

DELETE FROM Products
WHERE ProductID=::ProductID::

For your situation, change Products to the name of the table that you wish to delete a record from. Also, change ProductID to the name of your unique key for that table. The "Custom Query" screen should appear as follows:

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:

Click OK to return to the Custom Query Screen, and OK again to return to the DRW Step 2 Screen. Click next to move to step 3 of the DRW. It will look like the following:

Click the "More Options" button. This will open the following screen:

On this screen, change the "Message to display..." to something relevant like "Your Record Has Been Deleted". Then click the "Defaults" button. to open the following screen:

Click ProductID and then "Edit". Then, in the following screenshot, enter a default value.

 If you are using an autonumber key or a product ID that is a numeric value, use zero (0) as your default. This will not be a number that would generally be used as a real value for this field, so were this field to be accidentally be passed to the delete code you will not accidentally delete the wrong  record.

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

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

Now click "OK" to return to the Defaults screen, and "OK" again to return to the "More Options" screen. One more "OK" will bring you back to Step 3 of the DRW. Now click next twice to progress to stage 5 and the following screen:

Be sure to select "Display all records together" and deselect "Add search form".

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

Step 2 - Create DeleteConfirm.asp

The previous exercise WILL delete a record in a table without adding this step. However, it is not advised to delete a record without asking whether the user is absolutely sure that is what they want.

By adding an intermediate stage, we can display the single record that will be deleted, ask them if they are sure, and then (and only then) delete the record.

Create a new page and call it DeleteConfirm.asp. On this page, type a message such as:

"You have chosen to delete the following record. If you are REALLY sure that you want to delete it, click the hyperlink labeled "delete". Your record will be deleted. Once you complete this operation, you will have no way to reverse the operation later."

Now insert a DRW similar to the one on the previous exercise. The only main difference though is that we need some way to isolate a single record that has been selected to be deleted. In step 3, we'll create a hyperlink to select a record. This hyperlink will pass the ProductID parameter to this page that can display a single record prior to issuing the delete code we created in step 1.

When you create this DRW, you'll need to request the ProductID in the criteria screen so that it only displays the single record that has been selected for deletion. In this case, create the DRW exactly as you did in
step 1. There is only one difference, in Step 3, click on the "More Options" Button and then the "Criteria" button. This will bring up the following screen:

Click the "Add" button and bring up the Add Criteria screen.

Select ProductID from the Field Name drop down. Make sure the "Comparison Field" says Equals and the "Value" field indicates ProductID. These values should automatically be entered. Click OK. Your Criteria screen should look like the following:

Click OK.

Continue clicking next on each screen until you get to the final screen of the DRW. Click Finish to close the DRW and generate the code.

Step 3 - Create a Hyperlink to DeleteConfirm.asp

Insert a column to the right of the others. This will be used to insert a hyperlink to pass the ProductID parameter to DeleteRecord.asp page.

All this hyperlink does is pass the parameter. It doesn't actually delete the record. That is done on DeleteRecord.asp. In this example, we want to delete a record in the Products table with a specific ProductID. Use the DRW to create a table that shows the Product ID, and Product Name. For the purpose of this tutorial, limit the list to displaying 10 records only. Sort the table by ProductID.

Now insert a third column and type  the word "Delete" in the third column beside the <<ProductName>> field.

On the menu, select Insert-Hyperlink. The Edit Hyperlink screen opens.

If you created DeleteConfirm.asp, than type this page name into the "Address" field. Otherwise, type DeleteRecord.asp.

Now click the "Parameters" button to open the Hyperlink Parameters screen:

Click Add to see the Add Parameter Screen.

In the Name Drop Down, select ProductID. The "Value" field will be filled in automatically. Do not change this. Click Ok to return to the Hyperlink Parameters Screen. It should now look like the following:

Click Ok to return to the Edit Hyperlink Window and OK again to leave the hyperlink screen. Your screen should now look like the following:

You have now created a link that will take you to DeleteConfirm.asp. This page will give the site user the chance to delete the record, but it will show them exactly what they are deleting and give them the chance to back out. Try clicking one of the delete links in the preceding table.