How to update information in an Access database by using ASP in FrontPage 2000

This article describes how to update information in a Microsoft Access database by using FrontPage 2000 and Active Server Pages (ASP). Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Use ASP features in FrontPage

Before you can use the ASP features in FrontPage 2000, install the components listed in the following Microsoft Knowledge Base article:

To update information in a Microsoft Access database by using FrontPage 2000, you need to create a custom query and enter that into the FrontPage Database Results Wizard.

Create a Web and a database to contain the information

1. Start FrontPage 2000. On the File menu, point to New, and then click Web.
2. In the Specify the location of the new web box, type the following:
   http://localhost/aspsample
   Use the One Page Web Wizard.
3. Open a new page. Create four one-line text boxes. To do this, follow these steps:
   1. On the Insert menu, point to Form, and then click One-Line Text Box.
   2. Use the ARROW keys to position the pointer between the one-line text box and the Submit button.
   3. Press ENTER twice. Press the DOWN ARROW key to move the pointer below the text box.
   4. Repeat step a to add three more one-line text boxes.
4. Insert the following information for the form fields 1 through 3:
   1. Double-click the first one-line text box. The Text Box Properties dialog box appears. In the Name box, type FirstName.
   2. Double-click the second one-line text box, and type LastName in the Name box.
   3. Double-click the third one-line text box, and type Phone in the Name box.
   4. Double-click the fourth one-line text box, and type Email in the Name box.
5. Type the name of the form field just to the left of each one-line text box. Save the page as ASPsample.asp.
6. Right-click the form, and click Form Properties on the menu that appears. In the Form Properties dialog box, click Send to database, and then click Options.
7. In the Options for Saving Results to Database dialog box, follow these steps:
   1. Click Create Database.
   2. Click OK twice to get back to the page, and then save the page.

Note Preview the page in a Web browser and add some records to the database. For example, open the page in the browser, fill in a record, and then click Submit three times.

Create the Update Page

1. Switch to FrontPage and create a new page. (On the File menu, point to New, click Page, and click Normal Page.)
2. On the Insert menu, point to Database, and click Results. In Step 1, click Use an existing database connection and select ASPsample. Click Next.
3. In Step 2, click Custom query and then click Edit. In the SQL statement window, type the following SQL code:

   UPDATE results
   SET Phone='%%Phone%%',Email='%%Email%%'
   WHERE FirstName='%%FirstName%%' and LastName='%%LastName%%'
   						

   Click OK and then click Next.
4. In Step 3, click Next.
5. In Step 4, click Next.
6. In Step 5, make sure Add Search Form is unavailable (dimmed) or is not selected. Click Finish and then save the page as Update.asp.

Note The field names FirstName, LastName, Phone, and Email represent the fields used in the Aspsamp.mdb file. The fields %%FirstName%%, %%LastName%%, %%Phone%%, and %%Email%% represent the one-line text box form fields.

Create the Update Form

1. Create a new page. On the Insert menu, point to Database, and click Results.
2. In Step 1, click Use an existing database connection and select ASPsample. Click Next.
3. In Step 2, click Results as the Record Source, and then click Next.
4. In Step 3, click Edit and remove all fields except FirstName, LastName, Phone, and Email. Click OK and then click Next.
5. In Step 4, select List - one field per item in the Formatting list and Text fields in the List options list, and then click Next.
6. In Step 5, select Split records into groups, and type 1 in the Records per group box. Click Finish.
7. Double-click Submit and change the Value/Label field to Update.
8. Right-click inside the form and then click Form Properties on the menu that appears.
9. In the Form Properties dialog box, select Send to other, and then click Options.
10. In the Action box, type Update.asp. Click OK twice and save the page as Update_Form.asp.

Note You can make changes only to the Phone and Email fields. You cannot make changes to the FirstName and LastName fields, because they do not equal what is in the database. To update FirstName or LastName, you must create your own custom SQL statement.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

ASP Web Wizard 2000

Microsoft Product Support Services has released ASP Web Wizard 2000 for you to use with FrontPage 2000. This add-in is designed to guide you through the database features of FrontPage 2000. The wizard guides you through the following topics:

• Create a database with a form.
• Set up a database connection using ODBC.
• Set up a database connection using ADO.
• Use a form to post information to a database.
• Use a form to retrieve information from a database.
• Use a hyperlink with parameters to retrieve information from a database.
• Create an active hyperlink using a field in a database.
• Display images using data from a database.

For more information about the ASP Web Wizard, click the following article number to view the article in the Microsoft Knowledge Base: