updating child records in a DataSet w/ new autoinc values after parent insert?

[Chris Bordeman]

Hi all. Using Dot Net 2.0.

I have a typed dataset generated by VS. It contains a parent and a child
table with a relationship set up in the MSSQL database and showing in the
dataset. _Both_ tables have new records that need to be added to database.

I'm able to retrieve the server's autoinc values on the parent table, but I
can't seem to get my data adaper to filter the new autoinc values down to
the foreign key field in the child table.


HOW EXACTLY is this done???


I'm currently doing 3 things:

1. Added an output parameter to the insert command to grab the new autoinc
value and feed it into my autoinc field.
2. Set insertCommand.UpdatedRowSource = UpdateRowSource.Both (figure good
idea to grab it all).
3. Appended " SELECT @" + identityColumn.ColumnName + " = SCOPE_IDENTITY()"
to the insert command.

Does #3 keep the entire column's values from being fed back to the DataTable
row?

Do I need to AcceptChanges?

What about if the operation fails and the transaction is rolled back, how do
I undo changes made to the dataset?

THANKS!!!

Chris B.

 

[William \(Bill\) Vaughn]

This question has been asked (and answered) 3,245 times so far...
The answer is in the archives and in the white paper on my site. See
http://www.betav.com/Files/Content/whitepapers.htm

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)

 

[Chris Bordeman]

Bill, thanks for the article. The relations do seem to work on the FIRST
set of tables, when I check 'Nested Relation.'

However, I'm having a problem now where the *second* table isn't updating
correctly at all. When I do: dataAdapter.Update(SecondTable), it should be
executing an insert, but it instead seems to be doing an UPDATE. I
inspected the state of those records and it seems the relation sets the
child record values OK, but then changes their .RowState to 'Updated' from
'Added.' This is not correct and would cause the records to be updated
instead of added, which explains the concurrency exception I see saying 0
rows were updated.

Thanks for any further thoughts,

Chris B.

 

[Cor Ligthert [MVP]]

Chris,

Will you please not repeat your message, this newsgroup is active enough to
answer in decent time your question. Now we have an open question for people
who are looking for this on Google or whatever.
(And for those I have now explained why).

Thanks in advance.

Cor

"Chris Bordeman"