none
OleDbTransaction with more then one dataadapters RRS feed

  • Question

  • Hi, I'm using two OleDbDataAdapters (da1 and da2), both with their own Select- and UpdateCommand (using batch updating), and sharing the same connection (cn).
    While performing an update on both dataadapters, I would like to this withing a transaction. Update code is something like this:

    cn = da1.UpdateCommand.Connection
    cn.Open()
    tnx = cn.BeginTransaction

       da1.Update(dsMyDataset.Tables("table1))
       da2.Update(dsMyDataset.Tables("table2))

    tnx.Commit()
    cn.Close()

    Exception at second update: {"ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized."}
    How to handle this situation correct?

    Tuesday, February 15, 2011 10:32 AM

Answers

  • The .Update() command doesn't use an existing command, it dynamically builds one. This, of course, means that the
    transaction is not set on that command. So you should use manual SQL statements instead of Update.

    Ali Hamdar (alihamdar.com - www.ids.com.lb)
    Wednesday, February 16, 2011 4:05 PM

All replies

  • I think the key issue is that you can't really share the connection amongst DataTables or DataReaders. Alternatives would be to try using a single DataSet for your DataTables, which is a little tricky, use TableAdapters or just use SQL code with a Command object and ExecuteNonQuery.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, February 16, 2011 1:35 PM
  • The .Update() command doesn't use an existing command, it dynamically builds one. This, of course, means that the
    transaction is not set on that command. So you should use manual SQL statements instead of Update.

    Ali Hamdar (alihamdar.com - www.ids.com.lb)
    Wednesday, February 16, 2011 4:05 PM
  • Hi Coen,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 21, 2011 3:51 AM
    Moderator
  • Hi, I want to let you know that at this moment I don't have the time to make further investigations.
    The problem isn't yet solved, but I will try to use your remarks and when I found a solution I will post it here.

    Thank you all.
    Regards Coen

    Friday, February 25, 2011 3:01 PM
  • Coen,

    When SqlDataAdapter call Update, it uses SqlCommand for each INSERT, UPDATE or DELETE action. Those commands must use same SqlTransaction to be able to work it as single transaction. That means that you need explicitly set Transaction property of SelectCommand, UpdateCommand and DeleteCommand to use your transaction. If you do it for all SqlDataAdapters then all of them will update data using same transaction against same connection.

     

     


    Val Mazur (MVP)

    http://www.xporttools.net

    Monday, February 28, 2011 11:20 AM
    Moderator