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   ' 储存原始数据。
   strOldFirst = rstEmployees!fname
   strOldLast = rstEmployees!lname
   ' 更改编辑缓冲区中的数据。
   rstEmployees!fname = "Linda"
   rstEmployees!lname = "Kobara"   ' 显示缓冲区的内容并获取用户输入。
   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   ' 显示结果数据。
   MsgBox "Data in recordset = " & rstEmployees!fname & " " & _
      rstEmployees!lname   ' 恢复原始数据，因为这只是演示。
   If Not (strOldFirst = rstEmployees!fname And _
         strOldLast = rstEmployees!lname) Then
      rstEmployees!fname = strOldFirst
      rstEmployees!lname = strOldLast
      rstEmployees.Update
   End If   rstEmployees.CloseEnd 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   ' 使用雇员表中的数据打开记录集。
   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"   ' 显示缓冲区内容并获取用户输入。
   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
      ' 转到新记录并显示结果数据。
      MsgBox "Data in recordset = " & rstEmployees!emp_id & ", " & _
          rstEmployees!fname & " " & rstEmployees!lname
   Else
      rstEmployees.CancelUpdate
      MsgBox "No new record added."
   End If   ' 删除新数据，因为这只是演示。
   cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strEmpID & "'"
   
   rstEmployees.CloseEnd Sub