Multi-table dataset update - Help required

[Derek Chong]

Hi,

I have a dataset containing a parent and child datatable linked by a foreign
key relationship. When I update the parent datatable primary key field, the
update cascades to all the dependent child records. Problem arises after I
have updated the database with the parent datatable, the foreign key trigger
on the database cascades the change to the child records. When I go to
update the database with the child datatable I get a concurrency error as
the child record Id's have already been changed and the dataadapter cannot
find the original records to change them.

What am I doing wrong? Is there an easier way to update the database with a
multitable dataset?

 

[David Browne]

Hi,

I have a dataset containing a parent and child datatable linked by a foreign
key relationship. When I update the parent datatable primary key field, the
update cascades to all the dependent child records. Problem arises after I
have updated the database with the parent datatable, the foreign key trigger
on the database cascades the change to the child records. When I go to
update the database with the child datatable I get a concurrency error as
the child record Id's have already been changed and the dataadapter cannot
find the original records to change them.

What am I doing wrong?

What you are doing wring is updating the primary key of your table. Primary
keys should be immutable. Similarly, cascade updates should not be used.
Cascade deletes, yes, cascade updates, no.

Is there an easier way to update the database with a
multitable dataset?

You've go a couple of choices here. The easiest may be to change the
IDataParameter.SourceVersion for the "where clause" parameter on the
UpdateCommand bound to the foreign key field. Try changing it to
DataRowVersion.Original. If you're using a CommandBuilder, the code would
look something like:

myDataAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand()
'trace.writeline(myDataAdapter.UpdateCommand.CommandText)
'eg "update my_table set id = @id, name = @name, fk = @fk where id = @oldID
and name=@oldName and fk=@oldFK"
dim p as IDataParameter = da.UpdateCommand.Parameters(5) 'or whichever
parameter it is
p.SourceVersion = DataRowVersion.Orignal

Also you could just generate an update command of the form
"update my_table set id = @id, name = @name, fk = @fk where id = @oldID"
And leave out the optimistic concurrency altogether.

David

 

[Derek LaZard]

Hi Derek. If the child tables are being updated manually (by app code) then
don't cascade the update...

What do you think?

Derek LaZard

 

[Derek Chong]

Good point about not updating the primary key David, having a separate user
updatable unique Id field and a hidden system generated index would get
around the problem. Would rather do that than messing around with the
dataadapter update command.

Thanks for the tip
Derek