FP2000: How to Delete a Record from a Microsoft SQL Server 7.0 Database (256868)

MORE INFORMATION

Prepare to Use the ASP Features in FrontPage 2000

Before you can use the ASP features in FrontPage 2000, you must install the following components:

• A 32-bit ODBC driver
• An ASP-compatible server, such as one of the following servers:
  • 
    
    
  • Microsoft Internet Information Server (IIS) version 3.0 or 4.0 for Microsoft Windows NT Server
  • Microsoft Peer Web Services for Microsoft Windows NT Workstation
  • Microsoft Personal Web Server for Microsoft Windows 95 with the ASP engine added
  • Microsoft Personal Web Server 4.0
• FrontPage 2000 Server Extensions

To perform the example in this article, you also need access to Microsoft SQL Server 7.0 and its Northwind database.

NOTE: Microsoft Personal Web Server version 4.x, Internet Information Server 4.x, and Microsoft Peer Web Services for Microsoft Windows NT Workstation include the ASP engine and thus do not require a separate installation of the ASP engine. Versions earlier than Microsoft Personal Web Server 4.x and Internet Information Server 4.x require that you install the ASP engine.

How to Delete a Record in a Microsoft SQL Server 7.0 Database Using ASP

1. On the computer that is running Microsoft Windows NT Server with SQL Server 7.0 installed, start the Query Analyzer by clicking Start. Point to Programs, point to Microsoft SQL Server 7.0, and then click Query Analyzer.
2. In the Query Analyzer window, type the following:

   USE Northwind
   GO
   SELECT * INTO FPEmployees FROM Employees

3. Click Execute Query on the toolbar, or press F5. This creates a copy of the Employees table in a new table called FPEmployees (where records will be deleted later in this article). This is done to maintain the current data in the Employees table.
   
   
4. Close the Query Analyzer.
5. Start Microsoft FrontPage 2000 and create a new web. To do this, on the File menu, click New and then click Web. In the Specify the location of the new web box, type http://localhost/aspdelete and click the Web site default of One Page Web.
6. Click Web Settings on the Tools menu, and then click the Database tab.
7. Click Add to create a new database connection.
8. Type SQLNorthwind for the Name, click Network Connection to Database Server, and then click Browse.
9. Type the name of your SQL Server, and then type Northwind for the Database Name. Click OK.
10. Click Advanced. For Authorization, type the name of a SQL Server account that has Delete privileges in the Northwind database. Click OK three times.
11. Create a new page. (On the File menu, point to New, click Page, and then click Normal Page.)
12. On a new page, on the Insert menu, point to Database, and then click Results.
13. In step 1, click Use existing database connection, and then click to select SQLNorthwind. Click Next.
14. In step 2, click to select FPEmployees as the Record Source, and then click Next.
15. In step 3, click Edit List. Remove the Fields named Notes, Photo, and PhotoPath from the Displayed Fields column. Click OK and then click Next.
16. In step 4, click to select List - one field per item in the Formatting list and Text fields in the List options list, and then click Next.
17. In step 5, click Split records into groups, and then type 1 in the Records per group box. Click Finish.
18. Double-click Submit, and then change the Value/Label field to Delete. Click OK.
19. Right-click inside the form, and then click Form Properties.
20. In Form Properties, click Send to other, and then click Options.
21. In the Action field, type Delete.asp. Click OK twice, and save the page as Delete_form.asp.
22. Create a second new page. (On the File menu, point to New, click Page, and then click Normal Page.)
23. On the Insert menu, point to Database, and then click Results.
24. In step 1, click Use an existing database connection, and click to select SQLNorthwind. Click Next.
25. In step 2, click Custom Query, and then click Edit.
26. In the SQL statement window, type the following SQL:

    DELETE FROM FPEmployees WHERE EmployeeID = '::EmployeeID::'

27. Click OK and then click Next.
28. In step 3, click More Options. Change the Message to display if no records are returned to Record Deleted and then click OK. Click Next.
29. In step 4, click to select Table-One Record Per Row, and then clear all three check boxes (this makes the table invisible at runtime). Click Next.
30. In step 5, make sure the Add search form check box is not selected. Click Finish.
31. Create a hyperlink below the Database Results Wizard to go back to the Delete_form.asp page.
32. Save the page as Delete.asp.
33. Return to Delete_form.asp, and preview the page in your browser.
34. Click the Back and Next buttons at the bottom of the database table to find a record that you want to delete, and then click Delete. This transfers the page to Delete_form.asp, and the selected record is then deleted from the database.
35. Click the hyperlink to go back to Delete_form.asp, and click the Back and Next buttons to cycle through the remaining records. The record that you deleted no longer appears within the records retrieved.

NOTE: After you run this example, you can restore the Northwind database to its original form by running instnwnd.sql within the Query Analyzer.