ADO Recordset Object Delete Method

Deletes the current record or a group of records.

Delete Method Syntax

recordset.Delete AffectRecords

Delete Method Parameters

AffectRecords

An optional AffectEnum value that determines how many records the Delete method will affect. Can be one of the following constants:

Constant	Description
adAffectCurrent	Default. Delete only the current record.
adAffectGroup	Delete the records that satisfy the current ADO Recordset Object Filter Property setting. You must set the Filter property to one of the valid predefined constants in order to use this option. The Filter property is not currently supported on UNIX.

Delete Method Remarks

Using the Delete method marks the current record or a group of records in a Recordset object for deletion. If the Recordset object doesn't allow record deletion, an error occurs. If you are in immediate update mode, deletions occur in the database immediately. Otherwise, the records are marked for deletion from the cache and the actual deletion happens when you call the ADO Recordset Object UpdateBatch Method. (Use the Filter property to view the deleted records.)

Retrieving field values from the deleted record generates an error. After deleting the current record, the deleted record remains current until you move to a different record. Once you move away from the deleted record, it is no longer accessible.

If you nest deletions in a transaction, you can recover deleted records with the RollbackTrans method. If you are in batch update mode, you can cancel a pending deletion or group of pending deletions with the ADO Recordset Object CancelBatch Method.

If the attempt to delete records fails because of a conflict with the underlying data (for example, a record has already been deleted by another user), the provider returns warnings to the ADO Errors Collection, but does not halt program execution. A run-time error occurs only if there are conflicts on all the requested records.

Delete Method Examples

This VBScript example uses the Delete method to remove a specified record from a recordset.

<% Language = VBScript %>

<HTML>

<HEAD><TITLE>ADO 1.5 Delete Method</TITLE>

</HEAD><BODY>

<Center><H3>ADO Delete Method</H3>

'Create and Open Connection Object

Set OBJdbConnection = Server.CreateObject("ADODB.Connection")

OBJdbConnection.Open "AdvWorks"

'Create and Open Recordset Object

Set RsCustomerList = Server.CreateObject("ADODB.Recordset")

RsCustomerList.ActiveConnection = OBJdbConnection

RsCustomerList.CursorType = adOpenKeyset

RsCustomerList.LockType = adLockOptimistic

RsCustomerList.Source = "Customers"

RsCustomerList.Open

If Not IsEmpty(Request.Form("WhichRecord")) Then

`Get value to move from Form Post method

Moves = Request.Form("WhichRecord")

RsCustomerList.Move CInt(Moves)

If Not RsCustomerList.EOF or RsCustomerList.BOF Then

RsCustomerList.Delete 1

RsCustomerList.MoveFirst

Else

Response.Write "Not a Valid Record Number"

RsCustomerList.MoveFirst

End If

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT>

</TD>

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT>

</TD>

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Phone Number</FONT>

</TD>

</TD>

<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT>

</TD></TR>

<!--Display ADO Data from Customer Table Loop through Recordset

adding one Row to HTML Table each pass-->

<% Do While Not RsCustomerList.EOF %>

<%= RSCustomerList("CompanyName")%>

</FONT></TD>

<%= RScustomerList("ContactLastName") & ", " %>

<%= RScustomerList("ContactFirstName") %>

</FONT></TD>

<%= RScustomerList("PhoneNumber")%>

</FONT></TD>

<%= RScustomerList("City")%>

</FONT></TD>

<%= RScustomerList("StateOrProvince")%>

</FONT></TD>

</TR>

<!-Next Row = Record Loop and add to html table-->

RScustomerList.MoveNext

Loop

</Table></Center></FONT>

<!-- Do Client side Input Data Validation Move to named

record and Delete it -->

<H4>Clicking Button Will Remove Designated Record</H4>

<H5>There are <%=RsCustomerList.RecordCount - 1%> Records in this Set</H5>

</BODY>

Sub cmdDelete_OnClick

If IsNumeric(Document.Form.WhichRecord.Value) Then

Document.Form.WhichRecord.Value = CInt(Document.Form.WhichRecord.Value)

Dim Response

Response = MsgBox("Are You Sure About Deleting This Record?", vbYesNo, "ADO-ASP Example")

If Response = vbYes Then

Document.Form.Submit

End If

Else

MsgBox "You Must Enter a Valid Record Number",,"ADO-ASP Example"

End If

End Sub

</Script>

</HTML>