Calling EndEdit causes DataRelations to fail

[cjard]

I have two DataTables, related by a DataRelation that forms a relation and an foreign key update/delete cascade.
On a form, some controls are bound to these tables.

When adding a new record, I call
ParentBindingSource.AddNew()
ChildBindingSource.AddNew()

The two binding navigators on the form now correctly show that each source contains a single row. Querying the ParentBindingSource.List.Count and ChildBindingSource.List.Count shows them both to be 1

Immediately that I call ParentBindingSource.EndEdit() (as part of the save routine) the ChildBindingSource list entry disappears and I'm left is a ChildBindingSource.List.Count of 0

If i first call ChildBindingsource.EndEdit, an exception is fired because "ForeignKeyConstraint ParentDT_ChildDTRelation requires the child key values (-1) to exist in the parent table."

When I EndEdit() either, why does the other suddenly lose its related value?


-----------


Here is some more info:

No matter what kind of relation (relation only, fk constraint, both) with whatever combination of update/delete cascades, this behaviour still occurrs

Immediately before Calling EndEdit():

Both Bindingsources have 1 row in their List
Both these rows are RowState "Detached"
Neither of the underlying DataTables have a row



After calling EndEdit on the parent:

The "Detached" row in the child BindingSource's list simply disappears.
The child DataTable has 0 rows.
The parent DataTable has 1 row and its RowState is "Added"
The parent BindingSource has 1 list entry and its RowState is "Added"



------------

It appears I am not the only person to hit this problem..
http://www.eggheadcafe.com/aspnet_answers/NETFrameworkNETWindowsForms/May2006/post26930371.asp
http://www.eggheadcafe.com/aspnet_answers/NETFrameworkNETWindowsForms/May2006/post26930424.asp

But I must ask..

What am I doing that noone else in the world does?
Why isnt everyone complaining that their master/detail relationships dont work?
How do other people get their master/detail forms to work?

 

[jmcilhinney]

When you call AddNew on a BindingSource a new row is created with the correct schema for the DataTable that is bound to the Binding Source BUT that row is NOT added to the DataTable. It's like call NewRow() on the DataTable. When you call EndEdit on the BindingSource an attempt is made to add the new row to the bound DataTable. If that row violates any constraints applied to the DataTable then that attempt will fail. It's like calling Rows.Add(newRow) on the DataTable. If you haven't already added the parent row to the parent table then you cannot add the child row to the child table because it violates the foreign key constraint. There is no corresponding parent ID in the parent table. You must populate all required fields in the parent row and commit that to the parent table before committing the child row to the child table, and all required fields in the child row must be populated too.

 

[cjard]

I believe i'm doing that.. Let me post an example project highlighting the issue...

 

[cjard]

Here is the project. In a way I can understand the vanishing data in the Child, IF something about the parent row changes when EndEdit is called and this something is also needed for the relation to succeed..

But then, it's nonsensical and chicken/egg:

You cant call EndEdit on the parent first because the child will be lost
You cant call EndEdit on the child first because the parent has to be committed first

 

[cjard]

Note to any future readers of this thread:

I found when I was working with them that if I AddNew to the parent, then AddNew to the child, then edited the data in both, as soon as I called EndEdit on the parent, it destroyed all the data in the child. If I called it on the child first, the relation failed because the parent record wasnt in the datatable. It's very annoying

My workaround is to call EndEdit on the parent immediately after I call AddNew, then call the chld AddNew/EndEdit
Then I can edit them successfully and save it

In case this issue also affects your situation, you can try the workaround I follow, namely that immediately after you AddNew, you EndEdit before doing anything else. Then you can edit the records and call EndEdit again (as many times as you like) and the (bug?) will not destroy the data in the child bindingsource