|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
updating child records in a DataSet w/ new autoinc values after parent insert?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. 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 -- Show quote____________________________________ 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" <REMOVE_DUPED_LETTERSccchhrriiiissbbooooorrddeemma***@hhoottmmaaiill.com> wrote in message news:OBLwB2YDHHA.1224@TK2MSFTNGP04.phx.gbl... > 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. > 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. Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message news:etUtMFZDHHA.1196@TK2MSFTNGP02.phx.gbl... > 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" > <REMOVE_DUPED_LETTERSccchhrriiiissbbooooorrddeemma***@hhoottmmaaiill.com> > wrote in message news:OBLwB2YDHHA.1224@TK2MSFTNGP04.phx.gbl... >> 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. >> > > 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" <REMOVE_DUPED_LETTERSccchhrriiiissbbooooorrddeemma***@hhoottmmaaiill.com> Show quote schreef in bericht news:OsMcRZaDHHA.4396@TK2MSFTNGP02.phx.gbl... > 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. > > "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message > news:etUtMFZDHHA.1196@TK2MSFTNGP02.phx.gbl... >> 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" >> <REMOVE_DUPED_LETTERSccchhrriiiissbbooooorrddeemma***@hhoottmmaaiill.com> >> wrote in message news:OBLwB2YDHHA.1224@TK2MSFTNGP04.phx.gbl... >>> 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. >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics