Delete 方法范例

该范例使用 Delete 方法从 Recordset 删除指定的记录。

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   ' 打开 RoySched 表。
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set rstRoySched = New ADODB.Recordset
   rstRoySched.CursorLocation = adUseClient
   rstRoySched.CursorType = adOpenStatic
   rstRoySched.LockType = adLockBatchOptimistic
   rstRoySched.Open "SELECT * FROM roysched " & _
      "WHERE royalty = 20", strCnn, , , adCmdText   ' 提示删除记录。
   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))   ' 移动到记录并保存数据以使其可被恢复。
   rstRoySched.Filter = "title_id = '" & strTitleID & "'"
   intLoRange = rstRoySched!lorange
   intHiRange = rstRoySched!hirange
   intRoyalty = rstRoySched!royalty   ' 删除记录。
   rstRoySched.Delete
   rstRoySched.UpdateBatch   ' 显示结果。
   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   ' 恢复数据,因为这只是演示。
   rstRoySched.AddNew
   rstRoySched!title_id = strTitleID
   rstRoySched!lorange = intLoRange
   rstRoySched!hirange = intHiRange
   rstRoySched!royalty = intRoyalty
   rstRoySched.UpdateBatch   rstRoySched.CloseEnd Sub

VBScript 版本

下面是使用 VBScript 编写、并用于 Active Server Page (ASP) 的相同范例。如需查看该完整功能范例,请使用与 IIS 一同安装并位于 C:\InetPub\ASPSamp\AdvWorks 的数据源 AdvWorks.mdb,来创建名为 AdvWorks 的系统“数据源名称”(DSN)。这是 Microsoft Access 数据库文件。请使用“查找”命令定位文件 Adovbs.inc,并将其放入计划使用的目录中。请将以下代码剪切并粘贴到“记事本”或其他文本编辑器中,另存为“Delete.asp”。这样,便可在任何客户端浏览器中查看结果。

如要执行该范例,请先使用 AddNew 范例添加一些记录,然后可将其删除。并在任一客户端浏览器中查看结果。

<!-- #Include file="ADOVBS.INC" -->
<% Language = VBScript %><HTML><HEAD><TITLE>ADO Delete Method</TITLE>
</HEAD><BODY> 
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center><H3>ADO Delete Method</H3><!--- 用于创建记录集的 ADO Connection 对象 -->
<% 
' 创建并打开 Connection 对象。
Set OBJdbConnection = Server.CreateObject("ADODB.Connection") 
OBJdbConnection.Open "AdvWorks" 
' 创建并打开 Recordset 对象。
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
   ' 获取值以便从 Form Post 方法移动
   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%>
<!-- Customer 表的 BEGIN 列标头行 --><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><!-- 显示在记录集中 Customer Table Loop 的 ADO 数据,
每循环一次向 HTML 表添加一行 -->
<% 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 并添加至 html 表 -->
<% 
RScustomerList.MoveNext 
Loop 
%>
</Table></Center></FONT>
<!-- 对命名的记录执行客户端 Input Data Validation  Move 并将其删除 --><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>
www.holmesian.org