The SQL Anywhere OLE DB provider lets you update a result set through a cursor. This capability is not available through the MSDASQL provider.
You can update the database through a Recordset.
Private Sub cmdUpdateThroughCursor_Click( _ ByVal eventSender As System.Object, _ ByVal eventArgs As System.EventArgs) _ Handles cmdUpdateThroughCursor.Click ' Declare variables Dim i As Integer Dim myConn As New ADODB.Connection Dim myRS As New ADODB.Recordset Dim SQLString As String On Error GoTo HandleError ' Connect myConn.Provider = "SAOLEDB" myConn.ConnectionString = _ "Data Source=SQL Anywhere 11 Demo" myConn.Open() myConn.BeginTrans() SQLString = "SELECT * FROM Customers" myRS.Open(SQLString, myConn, _ ADODB.CursorTypeEnum.adOpenDynamic, _ ADODB.LockTypeEnum.adLockBatchOptimistic) If myRS.BOF And myRS.EOF Then MsgBox("Recordset is empty!", 16, "Empty Recordset") Else MsgBox("Cursor type: " & CStr(myRS.CursorType), _ MsgBoxStyle.Information) myRS.MoveFirst() For i = 1 To 3 MsgBox("Row: " & CStr(myRS.Fields("ID").Value), _ MsgBoxStyle.Information) If i = 2 Then myRS.Update("City", "Toronto") myRS.UpdateBatch() End If myRS.MoveNext() Next i myRS.Close() End If myConn.CommitTrans() myConn.Close() Exit Sub HandleError: MsgBox(ErrorToString(Err.Number)) Exit Sub End Sub |
If you use the adLockBatchOptimistic setting on the Recordset, 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 was 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.
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |