Error dataset deletion

[Flomo Togba Kwele]

I got the following error:

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

I have two tables in a strongly typed dataset, parent and child. I have defined
the delete rule of the relationship to cascade in both the dataset and in the
database.

I can delete a parent, and its associated children via sql:

DELETE FROM Parent where ParentID=6

I have defined the following updates in code:
_parentTableAdapter.Update(aDataSet.ParentTable)
_childTableAdapter.Update(aDataSet.ChildTable)

The error occurs in the second update. However, the parent and children rows
have been successfully deleted from the dataset and the database.

I don't understand the error message, nor how to avoid it. Can anyone point me
in the correct direction?

Thanks, Flomo
--

 

[Cor Ligthert [MVP]]

Flomo,

This comes AFAIK as well as you sent a DataAdapter with non rows to delete
in it.

Cor

 

[Earl]

I think what Cor means to say is that you deleted a parent with a cascade
delete (which deletes the children via cascade) then on your second update,
you are again firing a delete on the now non-existing children.

 

[Flomo Togba Kwele]

Thanks for both of your explanations. I now understand the problem.

In an attempt to fix it, I created a datatable with only the deleted rows, and
then called the RejectChanges method on it before performing the Update method
on the original datatable, but the error remained the same.

I then walked through the datatable rows, executing RejectChanges on those rows
which had a DataRowState of Deleted, but this broke the foreign key constraint
between the parent and child tables when calling the Update method on the table
adapter.

Can you think of a method which I could use to solve this problem?

Thanks, Flomo

--

 

[WenYuan Wang [MSFT]]

Hi Flomo,
Thanks for Cor and Earl's suggestion.

I have two tables in a strongly typed dataset, parent and child. I have defined
the delete rule of the relationship to cascade in both the dataset and in the
database.

I think the root cause is the delete rule in your database.
When you update the parent table
(parentTableAdapter.Update(aDataSet.ParentTable)), the related rows in the
child table will also be deleted by the delete rule (in database)
automatically. So that, you will receive the concurrency violation said
"non rows to delete" if you update the child table
(childTableAdapter.Update(aDataSet.ChildTable)).

I then walked through the datatable rows, executing RejectChanges on those rows
which had a DataRowState of Deleted, but this broke the foreign key constraint
between the parent and child tables when calling the Update method on the table
adapter.

You can switch the "EnforceConstraints" property of dataset to avoid
"Foreign key constratint" issue.
aDataSet. EnforceConstraints=false;
However, I'm afraid it is not a good solution in your case. I would like to
suggest you update parent-child tables from bottom to top. You can update
the child table first and then update the parent table.
_childTableAdapter.Update(aDataSet.ChildTable)
_parentTableAdapter.Update(aDataSet.ParentTable)
This should work on your side. If you still meet any further issue on this,
please don't hesitate to let me know. I'm glad to assist you.

Have a great day.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Flomo Togba Kwele]

Wen Yuan,

Thanks for your suggestions. I thought I was making it easier on myself by
using the Cascade delete!

So are you suggesting that I remove the cascade rule from both the dataset and
the database and use the "bottom-up" technique you described?

Thanks, Flomo

 

[WenYuan Wang [MSFT]]

Hi Flomo,
Thanks for your reply.

You needn't remove the cascade rule from the dataset. Otherwise, the rows
related to parent table will not be deleted from dataset. My suggest is
that, you can update the dataset "bottom-up" and leave the cascade rule
alive both in dataset and database. Does this method work fine on your
side? Please let me know if you still have any further issue on this.

Have a great day.
Sincerely,
Wen Yuan
Microsoft Online Community Support