Update 和 CancelUpdate 方法范例
內(nèi)容
Update 和 CancelUpdate 方法范例
該范例連同 CancelUpdate 方法說明 Update 方法。
Public Sub UpdateX()
Dim rstEmployees As ADODB.Recordset
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String
' 使用雇員表中的姓名打開記錄集。
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "SELECT fname, lname " & _
"FROM Employee ORDER BY lname", strCnn, , , adCmdText
' 儲(chǔ)存原始數(shù)據(jù)。
strOldFirst = rstEmployees!fname
strOldLast = rstEmployees!lname
' 更改編輯緩沖區(qū)中的數(shù)據(jù)。
rstEmployees!fname = "Linda"
rstEmployees!lname = "Kobara"
' 顯示緩沖區(qū)的內(nèi)容并獲取用戶輸入。
strMessage = "Edit in progress:" & vbCr & _
" Original data = " & strOldFirst & " " & _
strOldLast & vbCr & " Data in buffer = " & _
rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
"Use Update to replace the original data with " & _
"the buffered data in the Recordset?"
If MsgBox(strMessage, vbYesNo) = vbYes Then
rstEmployees.Update
Else
rstEmployees.CancelUpdate
End If
' 顯示結(jié)果數(shù)據(jù)。
MsgBox "Data in recordset = " & rstEmployees!fname & " " & _
rstEmployees!lname
' 恢復(fù)原始數(shù)據(jù)，因?yàn)檫@只是演示。
If Not (strOldFirst = rstEmployees!fname And _
strOldLast = rstEmployees!lname) Then
rstEmployees!fname = strOldFirst
rstEmployees!lname = strOldLast
rstEmployees.Update
End If
rstEmployees.Close
End Sub
該范例連同 AddNew 方法說明 Update 方法。
Public Sub UpdateX2()
Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strEmpID As String
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String
' 打開連接。
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
cnn1.Open strCnn
' 使用雇員表中的數(shù)據(jù)打開記錄集。
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable
rstEmployees.AddNew
strEmpID = "B-S55555M"
rstEmployees!emp_id = strEmpID
rstEmployees!fname = "Bill"
rstEmployees!lname = "Sornsin"
' 顯示緩沖區(qū)內(nèi)容并獲取用戶輸入。
strMessage = "AddNew in progress:" & vbCr & _
"Data in buffer = " & rstEmployees!emp_id & ", " & _
rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
"Use Update to save buffer to recordset?"
If MsgBox(strMessage, vbYesNoCancel) = vbYes Then
rstEmployees.Update
' 轉(zhuǎn)到新記錄并顯示結(jié)果數(shù)據(jù)。
MsgBox "Data in recordset = " & rstEmployees!emp_id & ", " & _
rstEmployees!fname & " " & rstEmployees!lname
Else
rstEmployees.CancelUpdate
MsgBox "No new record added."
End If
' 刪除新數(shù)據(jù)，因?yàn)檫@只是演示。
cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strEmpID & "'"
rstEmployees.Close
End Sub