Thread: Transaction is affected by a recordset

Transaction is affected by a recordset

From
"曾军"
Date:
Sorry to my poor English.
    I'm a program using Visuual Basic. I write the following code , as you see, I want to delete some data in a
transaction, in the transaction I execute "delete ..." first , and then open a recordset. At the end ,I commit the
transaction. But , delete is no effect . When I instead the parameter "adLockOptimistic"  with "adLockReadOnly" . the
deleteruns successful. Why ? 

    I notice that if I use parameter "adLockOptimistic" and the "sql"  selects from two table or selects from a view  ,
 con.Execute "delete from xsfpex "  runs with no effect . 

    I also notice that if I change  rs.CursorLocation to adServerClient , the "delete" runs successful .



the source codes:
'------------------------------------------------
        con.BeginTrans

        Dim rs As New Recordset
        rs.CursorLocation = adUseClient
        con.Execute "delete from xsfpex "

        Dim sql As String
        sql = "select a.tablename from wise_table a, wise_table  b where a.isleaf=1 and a.type_=3  and a.ptableid=b.id
andb.tablename='xsfp'" 
          rs.open sql, con, adOpenStatic, adLockOptimistic, adCmdText


        rs.MoveFirst

        Do While Not rs.EOF
                Debug.Print rs.Fields(0).Value
                rs.MoveNext
        Loop

        rs.Close

        Set rs = Nothing

        con.CommitTrans


'------------------------------------------------


        junzeng@netease.com
          2003-02-25