Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - Programming » SQL Anywhere OLE DB and ADO APIs » ADO programming with SQL Anywhere

Working with the Recordset object Next Page

Updating data through a cursor

The SQL Anywhere 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 Recordset.

Private Sub Command6_Click()
    Dim myConn As New ADODB.Connection
    Dim myRS As New ADODB.Recordset
    Dim SQLString As String
    ' Connect
    myConn.Provider = "SAOLEDB"
    myConn.ConnectionString = _
        "Data Source=SQL Anywhere 10 Demo"
    SQLString = "SELECT * FROM Customers"
    myRS.Open SQLString, _
  myConn, adOpenDynamic, adLockBatchOptimistic

    If myRS.BOF And myRS.EOF Then
        MsgBox "Recordset is empty!", _
        16, "Empty Recordset"
        MsgBox "Cursor type: " + _
        CStr(myRS.CursorType), vbInformation
        For i = 1 To 3
            MsgBox "Row: " + CStr(myRS.Fields("ID")), _
            If i = 2 Then
                myRS.Update "City", "Toronto"
            End If
        Next i
'        myRS.MovePrevious
    End If
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.