Concurrency violation issues on parent delete

[mmeier]

I have a simple windows form bound to the parent table -Checks-. This form has a datagridview of the children table -CheckSplits-.

The Dataset has the relationship setup between the parent and child with cascade delete enabled.

When I delete parent record and attempt to save the changes/deletes via the following code:

Me.Validate()
Me.TblChecksBindingSource.EndEdit()
Me.TblChecksTableAdapter.Update(Me.CheckWriterDataSet.tblChecks)

Me.TblCheckSplitsBindingSource.EndEdit()
Me.TblCheckSplitsTableAdapter.Update(Me.CheckWriterDataSet.tblCheckSplits)

I receive the following error:

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

This happens on the .update of the tblCheckSplits table.

Any thoughts greatly appreciated!!

 

[cjard]

If your database is set to cascade deleted children, then the children are already gone when you Update() the parent

Calling Delete() on the parent dataset row causes that parent and all known children to be marked deleted.
Update() parent causes the parent row to be deleted in the DB. The DB then deletes all children too. Then you try and call Update() for the children and attempts are made to delete children that are already deleted. Hence the statement affected 0 rows rather than the expected 1 -> Concurrency issue has arisen (you caused it )


This protection is intended for things like multi user environments where it is handy to find out that another user has deleted or updated a row youre working on


To solve, pick any one:


Update the child first. VB.NET will delete the children from the DB.

Change the datarelation to relation only, not enforcing deleted. Update() of the parent will make the DB delete the children. Dont update the deleted children (Call childrenDataTAble.GetChanges(Added Or Modified) to get only the rows that need insert/update. Not delete) - just forget about them

Remove the relationship from the DB. Update in any order. VB.NET must manage the relationship now

 

[mmeier]

Thanks. I was thinking the cause of this issue was exactly what you laid out. But I didn't know the the best way to deal with it.

Being new to vb.net, I'm looking for... Is it an accepted practice to setup the relationships in SQL server without 'Enforce Relationship', then have the datarelation in the VB.NET project set to 'Enforce foreign key constraint'?

This does work for my current issue. I'm just asking... Is the a generally acceptable method of doing this?

Or - does one keep the enforcement on the backend (SQL) and juggle the adds, updates, and deletes timing in the project?

Just trying to get started in the right (better) direction.

Thanks!

Mike

 

[cjard]

I wouldnt say there is an "accepted" way.. Multiple levels of security and structure enforcement are always desirable..