Update and CancelUpdate Methods Example

This example demonstrates the Update method in conjunction with CancelUpdate method.

Public Sub UpdateX()

	Dim rstEmployees As ADODB.Recordset
	Dim strOldFirst As String
	Dim strOldLast As String
	Dim strMessage As String

	' Open recordset with names from Employee table.
	strCnn = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	Set rstEmployees = New ADODB.Recordset
	rstEmployees.CursorType = adOpenKeyset
	rstEmployees.LockType = adLockOptimistic
	rstEmployees.Open "SELECT fname, lname " & _
		"FROM Employee ORDER BY lname", strCnn, , , adCmdText

	' Store original data.
	strOldFirst = rstEmployees!fname
	strOldLast = rstEmployees!lname
	' Change data in edit buffer.
	rstEmployees!fname = "Linda"
	rstEmployees!lname = "Kobara"

	' Show contents of buffer and get user input.
	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

	' Show the resulting data.
	MsgBox "Data in recordset = " & rstEmployees!fname & " " & _
		rstEmployees!lname

	' Restore original data because this is a demonstration.
	If Not (strOldFirst = rstEmployees!fname And _
			strOldLast = rstEmployees!lname) Then
		rstEmployees!fname = strOldFirst
		rstEmployees!lname = strOldLast
		rstEmployees.Update
	End If

	rstEmployees.Close

End Sub

This example demonstrates the Update method in conjunction with the AddNew method.

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

	' Open a connection.
	Set cnn1 = New ADODB.Connection
	strCnn = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	cnn1.Open strCnn

	' Open recordset with data from Employee table.
	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"

	' Show contents of buffer and get user input.
	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
		' Go to the new record and show the resulting data.
		MsgBox "Data in recordset = " & rstEmployees!emp_id & ", " & _
			 rstEmployees!fname & " " & rstEmployees!lname
	Else
		rstEmployees.CancelUpdate
		MsgBox "No new record added."
	End If

	' Delete new data because this is a demonstration.
	cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strEmpID & "'"
	
	rstEmployees.Close

End Sub