none
Primary key value change in parent table causing Concurrency Violation error

    Question

  • I am new to VB.net and looking for anyone can help to solve the problem.

    I use two DataGridView controls to retrieve parent-child tables in SQL with cascade update and delete relationship. When I update the primary key field value of a record in parent table, it prompts Concurrency Violation exception at the child DataAdapter update method.

    Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

    The code of Save button is the following.

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

       Dim deleteChild As DataTable = dataset1.Tables("childTable").GetChanges(DataRowState).Deleted)

       Dim insertChild As DataTable = dataset1.Tables("childTable").GetChanges(DataRowState).Added)

       Dim updateChild As DataTable = dataset1.Tables("childTable").GetChangez(DataRowState).Modified)

       Try

          If Not deleteChild Is Nothing Then

             daChild.Update(deleteChild)

          End If

          daParent.Update(dataset1, "parentTable")

          If Not insertChild Is Nothing Then

             daChild.Updatd(insertChild)

          End If

          If Not updateChild Is Nothing Then

             daChild.Update(updateChild)     'result concurrency violation

          End If

          dataset1.AcceptChanges()

       Catch ex As Exception

          MessageBox.Show(ex.Message)

       End Try

    End Sub

    Saturday, April 29, 2017 4:13 AM

Answers

  • Assuming the primary key in the master record has been changed it's a good guess that when the violation occurs it can't find the primary key which is why you should not change a key field that the detail records depend on. A simple master-detail will have a primary key (auto-incrementing, read-only) key that allows the master-detail relationship to work. Since the DataAdapter is a closed class I would assume to locate the parent record for the child it uses GetParentRow and when the primary key changed this is why the exception is thrown. So consider not changing the primary key.

    I don't have a code example for this as in this case I would only use the DataAdapter to read in data and setup the relations to the master-detail then handle each change at the time of the operation e.g. press the delete button, delete that record, insert a new record save or cancel etc. If this was Entity Framework I would do this slightly different e.g. mark (set their state as they would be detached from the entity)  records for update, delete or add then execute SaveChanges which is the same logic as using TableAdapters in tangent with a DataSet and BindingSource.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by HS-C Saturday, April 29, 2017 3:40 PM
    Saturday, April 29, 2017 9:24 AM
    Moderator

All replies

  • Assuming the primary key in the master record has been changed it's a good guess that when the violation occurs it can't find the primary key which is why you should not change a key field that the detail records depend on. A simple master-detail will have a primary key (auto-incrementing, read-only) key that allows the master-detail relationship to work. Since the DataAdapter is a closed class I would assume to locate the parent record for the child it uses GetParentRow and when the primary key changed this is why the exception is thrown. So consider not changing the primary key.

    I don't have a code example for this as in this case I would only use the DataAdapter to read in data and setup the relations to the master-detail then handle each change at the time of the operation e.g. press the delete button, delete that record, insert a new record save or cancel etc. If this was Entity Framework I would do this slightly different e.g. mark (set their state as they would be detached from the entity)  records for update, delete or add then execute SaveChanges which is the same logic as using TableAdapters in tangent with a DataSet and BindingSource.



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by HS-C Saturday, April 29, 2017 3:40 PM
    Saturday, April 29, 2017 9:24 AM
    Moderator
  • If you have a parent child relation, then an update should be in phases using getchanges method with the rowstate , 

    First the New parent records
    Then the new detail records
    Then the updates does not matter in what order
    Then the delete of the detail records
    Then the delete of the parent records. 

    This is quite a long job to do, easier is to use the TableAdapterManager which was introduced in version 2010

     https://msdn.microsoft.com/en-us/library/bb384426.aspx

    If you still want to do it in your own way then look at this and use the sequence above

    https://msdn.microsoft.com/en-us/library/5dxfaha8(v=vs.110).aspx

    Don't worry about for instance updated new rows and things like that, there are no rows with 2 states in a datatable


    Success
    Cor

    Saturday, April 29, 2017 11:03 AM
  • Thanks for your advice. I can eliminate the exception after added a auto-incremental field and use it in WHERE clause for UpdateCommand and DeleteCommand.
    Saturday, April 29, 2017 3:37 PM
  • Thanks for information, Cor. I will study and try using TableAdapterManager.
    Saturday, April 29, 2017 3:40 PM
  • I see that you don't use my advice but Karens' which means that you handle in fact the Parent Child records only for one Master ID.

    Tell that next time, than it saves time in replying to you.  


    Success
    Cor

    Saturday, April 29, 2017 4:56 PM
  • I see that you don't use my advice but Karens' which means that you handle in fact the Parent Child records only for one Master ID.

    Tell that next time, than it saves time in replying to you.  


    Success
    Cor

    Saturday, April 29, 2017 4:57 PM