Problem with Updating Related Tables

[Chris Simmons]

I've got an app in C# using SQL as my data provider and I'm trying to
add information into 3 tables where the first is the parent, the
second is it's child and the third is a child to the second table. I
posted a while back asking how to update when they are related in SQL.
I was given three examples and none of them worked.

Here's what I'm doing.

I'm inserting a row into my first table, then inserting multiple rows
in the second table. The examples I was given told me to use negative
values for ID fields within the program and then using the
relationship, update the parent rows, which would bring back the
parent row's ID and the relationship would pass that on to my tables
within the program. The problem is that once the update command is
called through the DataAdapter for the parent table, the row state of
all the rows I have added to the child table becomes unchanged.
Because of this, when I run the update for the second table, it's
child, no rows are added to the SQL table. Why is this happening.
This doesn't make any sense. Why would it change the row state and
then not allow me to use the update function provided by the
DataAdapter to insert those rows to the SQL table?

Thanks in advance,
Chris

 

[Greg]

A DataRow should not get flagged from Added to Unchanged until Accept
Changes is called. Are you calling AcceptChanges in the DataSet by chance
after the parent insert?

 

[Chris Simmons]

Yes, I know this. In my code, I run the update function for the parent
table, then the VERY NEXT line checks the row state of the rows in the
child table. I'm baffled.

 

[Greg]

And when you check those DataRows, they are marked as Unchanged? Prior to
calling Update and passing in the parent DataTable, they were marked as
Modified?

 

[David Sceppa]

Chris,

It sounds like the ForeignKeyConstraint for your
DataRelation has its AcceptRejectRule property set to Cascade
rather than the default of None. If you set this property back
to None, the pending child inserts will still have a RowState of
Added after the call to ParentAdapter.Update.

When you call DataAdapter.Update and successfully submit a
pending change in a DataRow, the DataAdapter implicitly calls
DataRow.AcceptChanges to denote that the change has been
submitted successfully. If the row has related child rows and
the DataRelation's ForeignKeyConstraint has AcceptRejectRule =
Cascade, the call to AcceptChanges cascades down to the related
child rows.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[Chris Simmons]

David,

Thanks, and you were right on the money. I was out of the office and
during that day my boss figured this out and it fixed the problem. When
I returned, he told me his findings. I then checked here and sure
enough your suspicion was right on. Thank you very much.

Chris