Deleting a Record

When you need to delete a record, you can use the SQL DELETE statement. When writing a VBScript to handle the delete request, it's always a good idea to check for the following conditions:

Did the user specify a record to delete? The user may have pressed the submit button without typing the record ID in the provided text box. Does the provided record ID exist in the table? This question is particularly important when the user is expected to type the record ID in a text box instead of selecting it from the drop-down list.

What happens when the record the user wants to delete has related records in other tables? As you know, Microsoft Access will not allow you to delete records when the referential integrity rules are enforced.

The next hands-on exercise demonstrates how to delete a shipper from the Shippers table.

©Hands-On 28-15: Creating Pages that Allow Record Deletions

This hands-on uses two files for performing the delete operation. The first file is the HTML form that will submit the data to the second file, which is the ASP page.

If you don't feel like typing, both files (DeleteShipper.html and DeleteShipper.asp) are available in the downloadable files and should be copied to your Learn_ASP folder.

1. Start Windows Notepad and enter the ASP code as shown below.


Part V

<FORM Name=DeleteShipperForm Method=Get Action="DeleteShipper.asp"

<INPUT Type="Hidden" Name="Action" Value="Delete">

Please enter the Shipper ID you want to delete

<Input Type="Text" Size="6" Name="ShipperId">

<INPUT Type="Submit" Name="Delete" Value="Submit">

2. Save the file as DeleteShipper.html in the Learn_ASP folder.

In the above HTML page, the form's Action argument will call the ASP page named DeleteShipper.asp when the user clicks the Submit button (see Figure 28-33).

3. Start Windows Notepad and enter the ASP code shown below.

Set conn = Server.CreateObject("ADODB.Connection") mydbFile=Server.MapPath("Northwind.mdb")

conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & mydbFile & ";" myShipper = Cstr(Request.QueryString("ShipperID")) If myShipper <>"" Then

Set rst = Server.CreateObject("ADODB.Recordset") rst.Open "Shippers", conn, 3 rst.Find "ShipperID = " & myShipper If rst.EOF Then

Response.Write "The Shipper ID" & myShipper & " does not exist."


On Error Resume Next conn.Execute "DELETE * FROM Shippers WHERE ShipperId = " & _

myShipper If conn.Errors.Count > 0 Then

Response.Write "Error Number: " & err.Number & "<P>" Response.Write "Error Description: " & err.Description _ & "<P>"


Response.Write "<H2>The Shipper ID " & myShipper & " _ was deleted.</H2>"

End If rst.close Set rst = Nothing End If


Response.Write "The Shipper ID was not supplied. Cannot Delete."

End If


<A HREF="DeleteShipper.html">Please click here to return.</A>

Taking Your VBA Programming Skills to the Web

4. Save the file as DeleteShipper.asp in the Learn_ASP folder.

The VBScript code segment shown above establishes a connection to the data source and stores the ShipperId value in the myShipper variable. If the variable is not empty, then the code proceeds to create an instance of the Recordset object and opens the Shippers table. The recordset is opened using the static cursor (adOpenStatic) represented by the value of 3 in the following statement:

rst.Open "Shippers", conn, 3

Recall that the static cursor retrieves all the data as it was at a point in time and is particularly desirable when you need to find data. The next statement uses the Find method to check if the supplied ShipperId exists in the Shippers table:

rst.Find "ShipperId = " & myShipper

Next, the If.. .Then.. .Else statement decides what information should be returned to the browser. When the EOF property of the Recordset object is True, the recordset contains no records. In this situation you want to tell the user that there is no such record in the table. However, if the record is found in the Shippers table, the SQL DELETE statement is executed:

conn.Execute "DELETE * FROM Shippers WHERE ShipperId = " & myShipper

As noted at the beginning of this section, a user may enter a ShipperId that has related records in other tables. Because this situation will certainly result in an error, the VBScript is instructed to ignore the error and continue with the next line of code:

On Error Resume Next

The next line of code is another If.. .Then.. .Else block statement that sends a different text message to the browser depending on whether the error was generated or not. You will know this by checking the contents of the Errors collection of the Connection object. The code displays the error number and error description if the user picked a Shipper ID that cannot be deleted. You may want to replace this code section with a more user-friendly message. If there is no error, then the browser will display a message that the record was deleted. The text of this message is formatted in large letters using the HTML level 2 heading tag <H2>.

Next, the Recordset object is closed. And now we are back at the first If.. .Then.. .Else statement block where the Else part is executed if the user happened to click the Submit button without first typing in the Shipper ID to delete.

The final part of the ASP page shown above creates a hyperlink to allow the user to navigate back to the HTML form (DeleteShipper.html). To create a hyperlink, use the following format:

<A HREF="address">displaytext</A>

where address is the name of the HTML file you want to activate and displaytext is the text that the user should click on.

Part V

Now let's proceed to actually performing the delete operation.

5. Open your browser and type http://localhost/NorthDB/DeleteShip-

per.html in the address bar.

6. Press Enter or click Go. Your screen should resemble Figure 28-33.

Figure 28-33: This HTML page is used for submitting information to an ASP page.



File Fdt View Favorites I on Is Heip


Qlia* - Q 0 L") 1/J p!"mi


—JJir?:. ■ http;/ilocalhosUNorthDB/Deleie5tilpper.htrrt

Please enter the Shipper ID you want lo delete

1 [ Submit j


Lo& intranet

7. Click the Submit button without typing anything in the provided text box. You should see a message informing you that the Shipper ID was not supplied. Also, there is a link to allow you to return to the previous page.

8. Click the hyperlink to return to the previous page and enter 999 in the text box, then click the Submit button. Because this Shipper ID does not exist in the Shippers table, you are again informed about the problem and provided a way to return to the previous page.

9. Click the hyperlink to return to the previous page. Enter the Shipper ID that you inserted into the Shippers table in Hands-On 28-13 and click the Submit button. If you don't have any shipper record to delete, add a new record to the Shippers table and delete it using this process. When you type in a Shipper ID that exists in the Shippers table but is not referenced in other tables, you get the screen that confirms the deletion (Figure 28-34).

Figure 28-34: This screen announces a deletion of the shipper record having the ID of 5.


E (öllxl

File £dt view Ferrit« Toot

O»" • j ¿1 i t-VFevortt« €> 0-



■ddi <?;; .ej http:í/Eocíiti[)st/Nort:hDE/DElEtsSHppH.asp7Sh"FpErId-S&DEk:ts-SLt™t

- 0&

The Shipper 5 was deleted.


| Please ctick here to return.


Done S Local Intranet

When you enter a Shipper ID that is referenced in other tables, Access will not allow you to delete that Shipper's record:

Error Number: -2147467259

Error Description: [Microsoft][ODBC Microsoft Access Driver] The record cannot be deleted or changed because table 'Orders' includes related records.

Taking Your VBA Programming Skills to the Web

To see this error in action, try to delete the shipper with an ID of 1.

You can trap the error -2147467259 in your VBScript code to display a user-friendly message.

0 0

Post a comment