none
concurrency violation when trying to update child/detail tables in foreign key relationship RRS feed

  • Question

  • Hey everyone,

    I have 3 tables in an Access database - tblMain, tblDelivery, and tblOtherCosts.  tblMain is the parent table with a primary key (ID) and tblDelivery and tblOtherCosts are detail tables with foreign keys (ID) in a one to many relationship with tblMain.  Update and delete events are set to cascade (via Dataset Designer in VB 2008).

    I display data from all three tables on a form: I have a DataGridView bound to tblMain.   tblDelivery and tblOtherCosts have their respective textboxes (bound to the appropriate fields) on the form.  tblMain also has a DataNavigator on the form.

    I fill the dataset with:

    Me.TblOtherCostsTableAdapter.Fill(Me.SpDataSet.tblOtherCosts)
              Me.TblDeliveryTableAdapter.Fill(Me.SpDataSet.tblDelivery)
              Me.TblMainTableAdapter.Fill(Me.SpDataSet.tblMain)



    To save changes to the database, I use the following:


    Private Sub TblMainBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TblMainBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.TblMainBindingSource.EndEdit()
            Me.TblDeliveryBindingSource.EndEdit()
            Me.TblOtherCostsBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.SpDataSet)

    End Sub

    If I edit a value in the databound text box for tblDelivery or tblOtherCosts and try to save it (update) I get "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records".  Does anyone know why this is, and how I should modify my code to fix it?
    Saturday, January 5, 2008 11:40 PM

All replies

  • Hi,

    I hate to post a "me too" rather than a useful response. I'm getting the same problem in C#. If you've managed to resolve this I'd be very interested to hear the solution.

    Saturday, January 12, 2008 5:43 PM
  • Okay,

    The cause of the problem for me was setting the "Accept/Reject Rule" for the foreign key relation to "Cascade". The problem was resolved by setting the rule to "None"

    I hope that makes sense

    Saturday, January 12, 2008 7:19 PM