RSS
| << 14.2.2- Updating Existing Data | Chapter14 | 14.2.4- Using SQL Commands to Insert and Delete Data >> |
Deleting Records Using a Recordset
Next, we come to deleting records. You probably won't be surprised to learn that there is a Delete method for the recordset:
objRS.Delete
This statement deletes the record that the cursor is currently pointing to. The record is deleted immediately (unless you are in batch update mode – in which case the Delete requests remain pending until you call the UpdateBatch method). If you're deleting records in batch update mode, you can filter the recordset using adFilterPendingRecords to show only the records that have been deleted.
Let's look at an example in which we delete a record. I don't know how many times you ran the AddNew.asp example above, but every time you refreshed the page you will have added another record to the Movies table (each new record having a unique MovieID value and a Title of 'Psycho'). Whether you created one new record, or lots of them, we don't really want to include Psycho in our database so let's delete any records referring to Psycho now.
Try It Out – Deleting our previously added record
1. We'll assume by now that you've got your Movie2000.mdb or Movie2000.mdf database file set up, along with the DataStore.asp SSI file.
2. Open up a new file, which we'll call Delete.asp, and insert the following code:
<%
Option Explicit
Dim strConnect
%>
<!-- #include file="DataStore.asp" -->
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<HTML>
<HEAD>
<TITLE>deketing a New Record</TITLE>
</HEAD>
<BODY>
<%
Dim objRS, intIDForNewRecord
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "Movies", strConnect, adOpenDynamic, adLockOptimistic, adCmdTable
objRS.Filter = "Title = 'Psycho'"
Response.Write "We'll delete all of the following records:<BR> "
While Not objRs.EOF
Response.Write objRS("MovieID") & "<BR>"
objRS.Delete
objRS.MoveNext
Wend
objRS.Close
Response.Write "<BR>Just to check:<BR>"
objRS.Open "SELECT * FROM Movies WHERE Title LIKE 'Psycho'", _
strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
If objRS.EOF Then
Response.Write "All records of Psycho have been removed from the database<BR> "
Else
Response.Write "Psycho still exists in the database, " & _
"at the record with MovieID=" & objRS("MovieID")
End If
objRS.Close ' now close and clean up
Set objRS = Nothing
%>
</BODY>
</HTML>
3. Save Delete.asp into the \inetpub\wwwroot\BegASPFiles folder, and open it up in your browser. In this screenshot, you can see that I was a bit enthusiastic when testing AddNew.asp, and I managed to create four records containing the film 'Psycho'. However, Delete.asp has deleted them all.
|
|
How It Works
We start by opening a recordset that contains all the records of the Movies table:
objRS.Open "Movies", strConnect, adOpenDynamic, adLockOptimistic, adCmdTable
Now we apply a filter to hide all records except those whose title is 'Psycho':
objRS.Filter = "Title = 'Psycho'"
Now we'll loop through the filtered records. We'll display the MovieID of each record, and then we'll delete it from the recordset:
Response.Write "We'll delete all of the following records:<BR> "
While Not objRs.EOF
Response.Write objRS("MovieID") & "<BR>"
objRS.Delete
objRS.MoveNext
Wend
We're not working in batch update mode (because the recordset was not opened using adOpenBatchOptimistic), so we don't need to call the UpdateBatch method – the deletions are passed to the database automatically.
Now, we'll check that it worked. We'll close the recordset and reopen it with fresh information from the database, asking for any records of Psycho in the database. There shouldn't be any such records, and therefore EOF should be true and we'll get the output message we want:
objRS.Close
Response.Write "<BR>Just to check:<BR>"
objRS.Open "SELECT * FROM Movies WHERE Title LIKE 'Psycho'", _
strConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
If objRS.EOF Then
Response.Write "All records of Psycho have been removed from the database<BR> "
Else
Response.Write "Something went wrong. Psycho still exists in the database, " & _
"at the record with MovieID=" & objRS("MovieID")
End If
objRS.Close ' now close and clean up
Set objRS = Nothing
| << 14.2.2- Updating Existing Data | Chapter14 | 14.2.4- Using SQL Commands to Insert and Delete Data >> |
