Other Advanced Database Examples

You can perform some advanced queries with the Database Results Wizard and
some custom query using SQL (Structured Query Language). The following are examples to illustrate what you 
can do with Active Server Pages.

Update a Record

In this example, you need to create the OldName and NewName fields. The query that is used will change the last name of an employee based on these two fields.

Create Your Form

1. Create a new page, and name it "adv_forms1.htm" (without the quotation marks).
   
   
2. Create a form similar to the following. (Or you can copy and paste this form into your new page. If you do this, make sure you paste the dashed box surrounding the form fields.)
   
   

   Old Name:     
   
   New Name:     

Create the Page to Update a Record

In this example you will use SQL to update the last name of an employee. To do this, follow these steps:

1. Right-click your form, and click Form Properties.
   
   
2. Click to select the Send to Other option, and click Options.
   
   
3. In the Action box, type "updaterecord.asp" (without the quotation marks). You will create this page in the next few steps.
   
   
4. Save the page with the form.
   
   
5. Create a new page, and name it updaterecord.asp.
   
   
6. On the Insert menu, point to Database, and click Results.
   
   
7. In Step 1 of the Database Results Wizard, click to select the Use an existing database connection, and click to select Volcano2 from the list. Click Next.
   
   
8. Click to select the Custom Query option, and click Edit.
   
   
9. In the SQL Statement box, type the following SQL statement:
   
   UPDATE DISTINCTROW
   Employees 
   SET LastName='::NewName::'
   WHERE LastName='::OldName::' 
   
   
10. Click OK. Click Next until you can click Finish, then click Finish.

Add New Record

Create Your Form

1. Create a new page, and name it "adv_forms2.htm" (without the quotation marks).
   
   
2. Create a form similar to the following (or you can copy and paste this form into your new page, if you do this, make sure you paste the dashed box surrounding the form fields):
   
   

   First Name:     Email Address:
   
   Last Name:     Phone Number:

   Title:

   City: State:

Create the Page to Add New Record

To make this form add a new record to your database, follow these steps:

1. Right-click your form, and click Form Properties.
   
   
2. Click to select the Send to Other option, and click Options.
   
   
3. In the Action box, type "addrecord.asp" (without the quotation marks). You will create this page in the next few steps.
   
   
4. Save the page with the form.
   
   
5. Create a new page, and name it addrecord.asp.
   
   
6. On the Insert menu, point to Database, and click Results.
   
   
7. In Step 1 of the Database Results Wizard, click to select the Use an Existing Database Connection, and click to select Volcano2 from the list. Click Next.
   
   
8. Click to select the Custom Query option, and click Edit.
   
   
9. In the SQL Statement box, type the following SQL:
   
           INSERT INTO Employees (LastName, FirstName, Email, Phone, Title, City, State)     
           VALUES ('::LastName::', '::FirstName::', '::Email::', '::Phone::', '::Title::', '::City::', '::State::')
   
   
10. Click OK. Click Next until you can click Finish, then click Finish.
    

Delete a Record

In this example, you need only to create the FirstName and LastName fields. The SQL statement that is used will delete a 
record based on these two fields.

Create Your Form

1. Create a new page, and name it "adv_forms3.htm" (without the quotation marks).
   
   
2. Create a form similar to the following. (Or you can copy and paste this form into your new page. If you do this, make sure 
   you paste the dashed box surrounding the form fields.)
   
   

   First Name:     
   
   Last Name:     

Modify Form to Delete a Record

To make this form delete a specific record, follow these steps:

1. Right-click your form, and click Form Properties.
   
   
2. Click to select the Send to Other option, and click Options.
   
   
3. In the Action box, type "deleterecord.asp" (without the quotation marks). You will create this page in the next few steps.
   
   
4. Save the page with the form.
   
   
5. Create a new page, and name it deleterecord.asp.
   
   
6. On the Insert menu, point to Database, and click Results.
   
   
7. In Step 1 of the Database Results Wizard, click to select the Use an existing database connection, and click to select Volcano2 from the list. Click Next.
   
   
8. Click to select the Custom Query option, and click Edit.
   
   
9. In the SQL Statement box, type the following SQL statement:
   
          DELETE DISTINCTROW FirstName
           FROM Employees
           WHERE (FirstName='::FirstName::')
           AND (LastName='::LastName::')
   
   
10. Click OK. Click Next until you can click Finish, then click Finish.

Top