Microsoft ActiveX Data ObjectsMicrosoft ActiveX Data Objects*
*Contents  *Index  *Topic Contents

Delete Method Example

This example uses the Delete method to remove a specified record from a Recordset. The DeleteRecord procedure is required for this procedure to run. 

Public Sub DeleteX()

	Dim rstRoySched As ADODB.Recordset
	Dim strCnn As String
	Dim strMsg As String
	Dim strTitleID As String
	Dim intLoRange As Integer
	Dim intHiRange As Integer
	Dim intRoyalty As Integer

	' Open RoySched table.
	strCnn = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	Set rstRoySched = New ADODB.Recordset
	Set rstRoySched.CursorLocation = adUseClient
	rstRoySched.CursorType = adOpenKeyset
	rstRoySched.LockType = adLockBatchOptimistic
	rstRoySched.Open "SELECT * FROM roysched " & _
		"WHERE royalty = 20", strCnn, , , adCmdText

	' Prompt for a record to delete.
	strMsg = "Before delete there are " & _
		rstRoySched.RecordCount & _
		" titles with 20 percent royalty:" & vbCr & vbCr
	Do While Not rstRoySched.EOF
		strMsg = strMsg & rstRoySched!title_id & vbCr
		rstRoySched.MoveNext
	Loop
	strMsg = strMsg & vbCr & vbCr & _
		"Enter the ID of a record to delete:"
	strTitleID = UCase(InputBox(strMsg))

	' Move to the record and save data so it can be restored.
	rstRoySched.Filter = "title_id = '" & strTitleID & "'"
	intLoRange = rstRoySched!lorange
	intHiRange = rstRoySched!hirange
	intRoyalty = rstRoySched!royalty

	' Delete the record.
	rstRoySched.Delete
	rstRoySched.UpdateBatch

	' Show the results.
	rstRoySched.Filter = adFilterNone
	rstRoySched.Requery
	strMsg = ""
	strMsg = "After delete there are " & _
		rstRoySched.RecordCount & _
		" titles with 20 percent royalty:" & vbCr & vbCr
	Do While Not rstRoySched.EOF
		strMsg = strMsg & rstRoySched!title_id & vbCr
		rstRoySched.MoveNext
	Loop
	MsgBox strMsg

	' Restore the data because this is a demonstration.
	rstRoySched.AddNew
	rstRoySched!title_id = strTitleID
	rstRoySched!lorange = intLoRange
	rstRoySched!hirange = intHiRange
	rstRoySched!royalty = intRoyalty
	rstRoySched.UpdateBatch

	rstRoySched.Close

End Sub

VBScript Version

The following is the same example written in VBScript to be used in an Active Server Page (ASP). To view this example, you need to create a system Data Source Name (DSN) called AdvWorks for the Microsoft Access database file AdvWorks.mdb, which is installed with Microsoft Internet Information Server and located at C:\InetPub\ASPSamp\AdvWorks. Locate the file adovbs.inc and place it in the directory you plan to use. Cut and paste the following code to Notepad or another text editor and save it as Delete.asp. You can view the result in any client browser.

To exercise the example, try using the AddNew example first, to add some records. Then try to delete them. 

<!-- #Include file="ADOVBS.INC" -->
<% Language = VBScript %>

<HTML>

<HEAD><TITLE>ADO 1.5 Delete Method</TITLE>
</HEAD><BODY> 
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center><H3>ADO Delete Method</H3>

<!--- ADO Connection Object used to create recordset-->
<% 
'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
%>
<!-- Move to designated Record and Delete It -->
<% 

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
End If

%>
<!-- BEGIN column header row for Customer Table-->

<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0><TR>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT>
</TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Phone Number</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<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 %>
  <TR><TD BGCOLOR="f7efde" ALIGN=CENTER> 
    <FONT STYLE="ARIAL NARROW" SIZE=1> 
      <%= RSCustomerList("CompanyName")%> 
    </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
    <FONT STYLE="ARIAL NARROW" SIZE=1> 
      <%= RScustomerList("ContactLastName") & ", " %> 
      <%= RScustomerList("ContactFirstName") %> 
    </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
    <FONT STYLE="ARIAL NARROW" SIZE=1>
      <%= RScustomerList("PhoneNumber")%> 
   </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
    <FONT STYLE="ARIAL NARROW" SIZE=1> 
      <%= RScustomerList("City")%> 
    </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
    <FONT STYLE="ARIAL NARROW" SIZE=1> 
      <%= 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 -->

<Center>
<H4>Clicking Button Will Remove Designated Record</H4>
<H5>There are <%=RsCustomerList.RecordCount - 1%> Records in this Set</H5>
<Form Method = Post Action = "Delete.asp" Name = Form>
<Input Type = Text Name = "WhichRecord" Size = 3></Form>
<Input Type = Button Name = cmdDelete Value = "Delete Record"></Center>

</BODY>

<Script Language = "VBScript">

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>

Up Top of Page
© 1997 Microsoft Corporation. All rights reserved. Terms of Use.