Updating data through a cursor

The ASE OLE DB Provider lets you update a result set through a cursor. This capability is not available through the MSDASQL provider.

Updating record sets

You can update the database through a record set.

Private Sub Command6_Click()
Dim myConn As New ADODB.Connection
Dim myRS As New ADODB.Recordset
Dim SQLString As String
' Connect
myConn.Provider = "ASEOLEDB"
myConn.ConnectionString = _
"Data Source=MANGO:5000"
myConn.Open
myConn.BeginTrans
SQLString = "Select * from customer"
myRS.Open SQLString, _
      myConn, adOpenDynamic, adLockBatchOptimistic
If myRS.BOF And myRS.EOF Then
MsgBox "Recordset is empty!", _
16, "Empty Recordset"
Else
MsgBox "Cursor type: " + _
CStr(myRS.CursorType), vbInformation
myRS.MoveFirst
For i = 1 To 3
MsgBox "Row: " + CStr(myRS.Fields("id")), _
vbInformation
If i = 2 Then
myRS.Update "City", "Toronto"
myRS.UpdateBatch
End If
myRS.MoveNext
Next i '
myRS.MovePrevious
myRS.Close
End If
myConn.CommitTrans
myConn.Close End Sub

Notes

If you use the adLockBatchOptimistic setting on the record set, the myRS.Update method does not make any changes to the database itself. Instead, it updates a local copy of the Recordset.

The myRS.UpdateBatch method makes the update to the database server but does not commit it, because it is inside a transaction. If an UpdateBatch method were invoked outside a transaction, the change would be committed.

The myConn.CommitTrans method commits the changes. The Recordset object has been closed by this time, so there is no issue of whether the local copy of the data is changed or not.