Delete 方法范例
内容
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.Close
End 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>