Delete 方法范例
內(nèi)容
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))
' 移動(dòng)到記錄并保存數(shù)據(jù)以使其可被恢復(fù)。
rstRoySched.Filter = "title_id = '" & strTitleID & "'"
intLoRange = rstRoySched!lorange
intHiRange = rstRoySched!hirange
intRoyalty = rstRoySched!royalty
' 刪除記錄。
rstRoySched.Delete
rstRoySched.UpdateBatch
' 顯示結(jié)果。
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
' 恢復(fù)數(shù)據(jù)，因?yàn)檫@只是演示。
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) 的相同范例。如需查看該完整功能范例，請(qǐng)使用與 IIS 一同安裝并位于 C:\InetPub\ASPSamp\AdvWorks 的數(shù)據(jù)源 AdvWorks.mdb，來(lái)創(chuàng)建名為 AdvWorks 的系統(tǒng)“數(shù)據(jù)源名稱”(DSN)。這是 Microsoft Access 數(shù)據(jù)庫(kù)文件。請(qǐng)使用“查找”命令定位文件 Adovbs.inc，并將其放入計(jì)劃使用的目錄中。請(qǐng)將以下代碼剪切并粘貼到“記事本”或其他文本編輯器中，另存為“Delete.asp”。這樣，便可在任何客戶端瀏覽器中查看結(jié)果。
如要執(zhí)行該范例，請(qǐng)先使用 AddNew 范例添加一些記錄，然后可將其刪除。并在任一客戶端瀏覽器中查看結(jié)果。
<!-- #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>
<!--- 用于創(chuàng)建記錄集的 ADO Connection 對(duì)象 -->
<%
' 創(chuàng)建并打開 Connection 對(duì)象。
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "AdvWorks"
' 創(chuàng)建并打開 Recordset 對(duì)象。
Set RsCustomerList = Server.CreateObject("ADODB.Recordset")
RsCustomerList.ActiveConnection = OBJdbConnection
RsCustomerList.CursorType = adOpenKeyset
RsCustomerList.LockType = adLockOptimistic
RsCustomerList.Source = "Customers"
RsCustomerList.Open
%>
<!-- 移動(dòng)到指定記錄并將其刪除 -->
<%
If Not IsEmpty(Request.Form("WhichRecord")) Then
' 獲取值以便從 Form Post 方法移動(dòng)
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 列標(biāo)頭行 -->
<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 數(shù)據(jù)，
每循環(huán)一次向 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>
<!-- 對(duì)命名的記錄執(zhí)行客戶端 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>