Note:  In these databases, I am using the sample database connection (Northwind).

DELETE

This example demonstrates the correct way of implementing the DELETE and UPDATE statements based on data passed as parameters. For example, you have a record that is displayed on a page. Next to each record you have a hyperlink that will allow users to delete or update that specific record. This is a graphic image that shows what the table might look like: 

To do this, follow these steps:

1. Create a database results page that will display selected records on a page, and save the page.

2. Add two additional blank cells to the database results table. For example:

3. Type DELETE in one of the cells, and type UPDATE in the other.

4. Click to select DELETE. On the Insert menu, click Hyperlink .

5. In the Hyperlink dialog box, select the page that was saved in step one.  For example, if you saved
    the page as default.asp, select default.asp.

6. Click Parameters.

7. On the Hyperlink Parameters dialog box, click Add .

8. In the Name list, select the field name that you would like
    to use.

9. Click OK twice.

Now, you are ready to add the DELETE statement that will perform the action that you just created.  To do this on the same page that contains the database results, place the insertion point after the database results region.

10. On the Insert menu, point to Database, and click Results.

11. In step one of the Database Results Wizard dialog box, select the appropriate  
    database connection, and click Next.

12. In step two of the Database Results Wizard dialog box, select Custom Query, and click 
    Edit.

13. Type the following DELETE SQL statement in Custom Query dialog box, and click OK.

            DELETE * FROM Table-Name
            WHERE (Field-Name = '%%Field-Name%%')

Note: Table-Name is the name of the table used in your database containing the information, and Field-Name is the filed name that you choose in step eight.  In this example, the Table name is Customers and the field is CompanyName.

NOTE:  To set any default values for other parameters, click Insert Parameter and set the parameter. If you click Verify Query, your SQL statement will be verified.

14. Between step three and step five of the Database Results Wizard dialog box, modify the 
    options as desired, and click Finish in step 5.

15. Save the page again.

UPDATE

This example will demonstrate the proper way of implementing the UPDATE statements based on data passed as parameters.

1. Create a new page. On the Insert menu, point to Database, and click Results.

2. In step one of the Database Results Wizard dialog box, select the appropriate  
    database connection, and click Next.

3. In step two of the Database Results Wizard dialog box, select Custom Query, and click 
    Edit.

4. Type the following UPDATE SQL statement in Custom Query dialog box, and click OK.

            UPDATE  CUSTOMERS  SET CompanyName='%% COMPANYNAME%%' 
            WHERE CompanyName='"CompanyName"' "

5. Between steps three and five of the Database Results Wizard dialog box, modify the 
    options as desired, and click Finish in step five.

    NOTE: It is important that you select the check box
    to add search form to this page..

6. Save the page with .asp extension.

7. Open the results page in Page view. Select UPDATE. On the Insert menu, click
    Hyperlink .

8. In the Hyperlink dialog box, select the page that you saved in step six.  For example, if you saved
    the page as update.asp, select update.asp.

9. Click Parameters .

10. In the Hyperlink Parameters dialog box, click Add .

11. In the Name list, select the field name that you would like
    to use.

12. Click OK twice.

------------------------UNDERSTANDING What You're Doing----------------------

The DELETE statement removes selected rows of data from a single table. 

 DELETE values FROM table-name WHERE condition

For example, you have a database that has a table named CLIENTS and the table has two fields named FirstName and LastName . If you want to delete the names that match the name that you type in a text box named FirstNameField, use the following SQL code:.

DELETE FirstName, LastName FROM CLIENTS 
WHERE (FirstName = '%%FirstNameField%%')

Understanding the UPDATE Statement

The UPDATE statement is used to modify or change the value of a field or fields in a database.  For example, you would use the Update statement to change the first name of a client from Pat to Patricia updates selected rows of data on a single table. 

To implement the UPDATE statement, use the following Structured Query Language (SQL) code:

UPDATE table-name SET column-name = expression WHERE condition 

For example, you have a database that has a table named CUSTOMERS, and the table has two fields named FirstName . If you want to update the name that match with the name that you type in a text box named NewNameField, use the following SQL code:.

UPDATE CUSTOMERS SET FirstName='%% NewNameField%%'
 WHERE FirstName='"OldFirstName"' "

Implementing the DELETE statement using Database Result Wizard

In this example, you have a table named CUSTOMERS in a database with two fields named FirstName and LastName. If the information that is submitted matches the information in the FirstName field, the row will be deleted.  To do this, follow these steps:

1. On the Insert menu, point to Database, and click Results.

2. In step one of the Database Results Wizard dialog box, select the appropriate 
    database connection, and click Next.

3. In step two of the Database Results Wizard dialog box, select Custom Query, and click 
    Edit.

4. Type the following DELETE SQL statement in Custom Query dialog box, and click OK.

            DELETE FirstName, LastName FROM CUSTOMERS
 WHERE (FirstName = '%%FirstNameField%%')

NOTE:  To set any default values for other parameters, click Insert Parameter, and set the parameter. If you click Verify Query, your SQL statement will be verified.

5. Between steps three and five of the Database Results Wizard dialog box, modify the 
    options as desired, and click Finish in step 5.

6. Save the page with .asp extension.

7. Create a new page.

8. On the Insert menu, point to Form, and click One-Line Text Box.

9. Double-click the text box to open the Properties dialog box. Change the name to match 
    the name you typed in the DELETE SQL statement.  In this case, the name is
    FirstNameField.  Click OK.

10. Right-click anywhere within the form, and click Form Properties.

11. In the Form Properties dialog box, click to select the Send to Others option. Click to select 
      Custom ISAPI, NSAPI, CGI, or ASP Script from the list..

12. Click Options and type the name of the .asp file that you created in the Action box.  For example,
       if you saved the .asp file as delete.asp, type delete.asp in the Action box. The Method field is set to Post.

13. Click OK, and save the page.

Implementing the UPDATE statement using Database Result Wizard

In this example, you have a table named CUSTOMERS in a database with two fields named FirstName and LastName. If the information that is submitted matches the information in the FirstName field, the row will be updated with some values. To do this, follow these steps:

1. On the Insert menu, point to Database, and click Results.

2. In step one of the Database Results Wizard dialog box, select the appropriate  
    database connection, and click Next.

3. In step two of the Database Results Wizard dialog box, select Custom Query, and click 
    Edit.

4. Type the following UPDATE SQL statement in Custom Query dialog box, and click OK.

            UPDATE  CUSTOMERS  SET FirstName='%% NewNameField%%' 
            WHERE FirstName='"OldFirstName"'

NOTE:  To set any default values for other parameters, click Insert Parameter and set the parameter. If you click Verify Query, your SQL statement will be verified.

5. Between steps three and five of the Database Results Wizard dialog box, modify the 
    options as desired, and click Finish .

6. Save the page with .asp extension.

7. Create a new page.

8. On the Insert menu, point to Form, and click One-Line Text Box.

9. Double-click the text box to open the Properties dialog box. Change the name to match 
    the name you typed in the DELETE SQL statement.  In this case, the name is
    NewNameField.  Click OK.

10. Right-click anywhere within the form, and click Form Properties.

11. In the Form Properties dialog box, click to select the Send to Others option. Click to select 
      Custom ISAPI, NSAPI, CGI, or ASP Script from the list..

12. Click Options and type the name of the .asp file that you created in the Action box.  For example,
       if you saved the .asp file as update.asp, type update.asp in the Action box. The Method field is
       by default set to Post.

13. Click OK, and save this page.

**Thanks and a tip of the hat to Microsoft and their fantastic white paper program.