none
Update Multiple DataAdapters with one Sql Transaction RRS feed

  • Question

  • hello, am trying to implement a save routine , common scenario master detail data . However after the first save succeed the second time the save fails with the common message. Concurrency Violation The Update affect....  

    I have the following function Save

      Private Function _Save() As Boolean
            _Save = False
            Dim con As New SqlClient.SqlConnection(connectionString)
            con.Open()
            Dim Tran As SqlClient.SqlTransaction = con.BeginTransaction
            Try
                MasterTA.InsertCommand = Masterbuilder.GetInsertCommand
                MasterTA.UpdateCommand = Masterbuilder.GetUpdateCommand
                MasterTA.DeleteCommand = Masterbuilder.GetDeleteCommand
    
                MasterTA.InsertCommand.Transaction = Tran
                MasterTA.DeleteCommand.Transaction = Tran
                MasterTA.UpdateCommand.Transaction = Tran
    
    
                MasterTA.InsertCommand.Connection = con
                MasterTA.DeleteCommand.Connection = con
                MasterTA.UpdateCommand.Connection = con
    
                DetailsTA.InsertCommand = DetailsBuilder.GetInsertCommand
                DetailsTA.UpdateCommand = DetailsBuilder.GetUpdateCommand
                DetailsTA.DeleteCommand = DetailsBuilder.GetDeleteCommand
    
                DetailsTA.InsertCommand.Transaction = Tran
                DetailsTA.UpdateCommand.Transaction = Tran
                DetailsTA.DeleteCommand.Transaction = Tran
    
                DetailsTA.InsertCommand.Connection = con
                DetailsTA.UpdateCommand.Connection = con
                DetailsTA.DeleteCommand.Connection = con
    
               
                MasterTA.Update(DS.Tables(0))
                DetailsTA.Update(DS.Tables(1))
                UpdateLedger(Tran)
                Tran.Commit()
                Return True
    
            Catch ex As Exception
                Tran.Rollback()
                MessageBox.Show(ex.Message, My.Application.Info.Title, MessageBoxButton.OK, MessageBoxImage.Error)
                Return False
    
            Finally
                con = Nothing
                Tran = Nothing
    
            End Try
    
        End Function
    • I am using a CommandBuilder
    • if the Adpater 1 pass and the adapter 2 fail , I need to Re Fill my adapters again in order to avoid the concurrency violation . I CANNOT refill because I already have changes in the  DataTables I don't want the user to loose their information and start writing them again.
    •  The FULL procedure is successfully executed if no Exception occur with in the try catch
    • I was able to eliminate the concurrency violation by  setting the commandBuilder.ConfilctOption=ConflictOption.OverwriteChanges but this will fail to update the correct changes the 2nd time to the database

    the only possible solution that I come  close to it. Before attempt to save anything

    • Is to copy the datatables
    • After update Fail Or Not
    • Clear the DataTables from the Dataset
    • Merge The dataTables from the DataSet with the Previous Copy Tables
    • Exception Occure (Violation of primary key constraint '.....' attempt to insert a duplicate key)
    • My IDS are not Auto Increment .

    I Really really need to use a transaction with DataAdapters ,

    I Also try the System.Transactions.TransactionScope

    but I have 2 problems

    • Sometimes raise an error from the Server
    • It doesn't work always as it behaves almost the same as I describe the problems above (Works only The first time)


    stelios ----------

    Tuesday, March 31, 2015 10:13 AM

All replies

  • You should only connect to the database one.  Every time you reconnect you are loosing the links between the dataadapter and the database.

            Dim con As New SqlClient.SqlConnection(connectionString)

    Move the line of code to a common to the form load.  Then move variable 'con' to global memory space so all the methods have access to the same instance of the connection.


    jdweng

    Tuesday, March 31, 2015 10:20 AM
  • hi, I've done this already ,I have try it and I still have the concurrency violation
    The problem Is raise after the following conditions

    • Adapter1.Update(DataSet.Tables(0)) 'Succeed
    • Adapter.Update(DataSet.Tables(1))' Fail

    The second Time when I try

    • Adapter1.Update(DataSet.Tables(0)) 'Concurrency violation 
    • Adapter.Update(DataSet.Tables(1)) 'Never execute since the above fail

    stelios ----------

    Tuesday, March 31, 2015 10:33 AM
  • Are you closing the connection or opening multiple connection?  You can have only one connection open and you can't close the connection between the Select Query and the Update Query.

    jdweng

    Tuesday, March 31, 2015 12:28 PM
  • I was using multiple connection. 1 for select and different connection for the update as I show in the first post but i have change the connection now and move it to global , now either my select command or transaction using the same connection, However when I try to update I get the following error:

    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.


    stelios ----------





    • Edited by stelios84 Tuesday, March 31, 2015 1:01 PM
    Tuesday, March 31, 2015 12:50 PM
  • So the connection gets open only once it should initially get set to null.  Then test for null and when null open the connection.

    jdweng

    Tuesday, March 31, 2015 1:45 PM
  • nop, still the same.. I was able to solve the error ExecuteReader requires the command to have a transaction ...

    but I still have the problem with the concurrency violation.

    What am trying to do is to

    • Adapter1.Update(ds.Tables(0))
    • Adapter2.Update(ds.Tables(1))
    • ...
    • ..
    • Tran.Commit

     if adapter2 fail , the next time adapter1 raise an error concurrency violation ..


    stelios ----------

    Tuesday, March 31, 2015 2:03 PM
  • We need to determine the type of failure adapter2 is failing. For example if the failure is the connection closed then of course the adapter1 is going to fail if they share the same connection.

    Can you get the StackTrace for the exception?  Either from the exception handler e.StackTrace or from the Exception Popup "Copy to Clipboard".  Then paste into Notepad.


    jdweng

    Tuesday, March 31, 2015 2:09 PM
  • this the StackTrace when the error is ExecuteReader requires the command to have....

    •  at System.Data.Common.DbDataAdapter.UpdatingRowStatusErrors(RowUpdatingEventArgs rowUpdatedEvent, DataRow dataRow)
         at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
         at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
         at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

    And this is the StackTrace for the error Concurrency Violation

    •    at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
         at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
         at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
         at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
         at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

    stelios ----------

    Tuesday, March 31, 2015 2:16 PM
  • the adapter 2 is fail because my sql field is set not to allow NULLS that's why fail. the user is enter a nullable information to one of the fields. How ever this is not the problem. The problem is that Adapter1.Update ok Adapter 2 Fail of the reason I describe , The user corrects the Null Field hits the save again and the adapter1 fail with the concurrency violation

    stelios ----------


    • Edited by stelios84 Tuesday, March 31, 2015 2:20 PM
    Tuesday, March 31, 2015 2:20 PM
  • You may need to put a lock around the update command to prevent these conflicts.

    jdweng

    Tuesday, March 31, 2015 2:21 PM
  • to reproduce the problem

    a save button is click by the user

    • adapter1.Update Pass OK
    • Adapter2.Update Fail because user didn't complete some information
    • Tran.Commit

    Now User corrects his mistakes and hits the save button again

    • adapter1.Update FAIL Concurrency Violation exception 
    • Adapter2.Update - this line will never execute since the above fail and gets in the exception
    • Tran.Commit

    stelios ----------

    Tuesday, March 31, 2015 2:26 PM
  • what do you mean by saying put a lock ?

    stelios ----------

    Tuesday, March 31, 2015 2:31 PM
  • Are you still setting to null in code below

     Finally
                con = Nothing
                Tran = Nothing
    

    I think there is something wrong in the failure paths.  When reading the dataset are you testing for null incase no data is returned.  What I suspect is happening is you are reading past the EOF of the reader/adapter.  That is locking the interface.  Make sure you are testing for null in any event handlers for the adapter, and any event that is checking rows/columns that the index is >= 0.


    jdweng

    Tuesday, March 31, 2015 2:35 PM
  • I have try both ways , when I try your suggestion to move the connection globally the connection was not close 
    Finally
                

               
    Tran = Nothing

    Tran was stay there . I initialize tran every time I was get in the save function but I think this is not the problem

    Can you suggest a sample code by using the similar code to this by using Sql Transaction? maybe I miss something I don't know . I have stack on this the last 2- 3 days. am out of any ideas. I read almost all the posts around the internet

    Try

    • Adapter1.Update(DataSet.Tables(0))
    • Adapter2.Update(DataSet.Tables(1))
    • Adapter3.Update(DataSet.Tables(2))

    Transaction.Commit

    Catch Ex as Exception

    Transaction.RollBackEnd Try


    stelios ----------

    Tuesday, March 31, 2015 2:48 PM
  • I suggest that you could try with RAW sql to do the insertion.

    Tuesday, April 7, 2015 10:59 AM