Ian Griffiths wrote: > > > > I can see how that might work for inserting rows into a single > > table, but I don't understand how it addresses the poster's > > original quetion, which involved adding a *new* parent row, > > and a *related* child row. .....
Ben Kloosterman replied: > I asume the problem the poster is having adding a row is due > to not having a key. By setting values of -1 which increment > at -1 you know the value . Not really - you know the local ID you were using before pushing the data back to the DB. But the dicussion here surrounded the case where that column was really an autoincrement column on the DB side. This means that our local ID will be meaningless to the DB since it will invent its own identity. So the problem is how do I go about sending the second INSERT (for the related child) in such a way that the correct foreign key (i.e. a key that the DB itself will recognize) is sent? > At this point it is not relevant what the ID is as long as it does > not match an existing ID hence the use of negative numbers. It matters a great deal if I want to send two INSERTs, one of which adds a new row to the parent, and the second of which is in a child table and is RELATED TO THE NEWLY-ADDED ROW. That bit in capitals is crucial here. The DB will have invented a new ID for the parent row. It is vital that when I add the child row, the foreign key for the relation to the parent table contains the ID that the DB generated, not the ID that the DataSet generated. > >.... Since the DB will invent its own identity column, > > how on earth do you indicate that the newly-added > > child should be related to the newly added child. > > At this point the DB is not involved only after you finish >adding all the rows and often getting a user to fill out the > details in a form will you send it to the DB/ MidTier. I > will get to this later. Sure the DB is involved! How is the DB not involved? (Admittedly if the DB is not involved it's much simpler, but then where do I store my data?...) However, I notice that Chris Anderson has answered my question - you do it with two round trips. First, INSERT the new parent row, and you get back the new identity value. If you have an appropriate foreign key constraint set up on your DataSet, the act of updating the parent row's identity will have the side effect of upating any references to this row in the child tables. So when you come to update those, they will indeed have the correct key. -- Ian Griffiths DevelopMentor You can read messages from the Advanced DOTNET archive, unsubscribe from Advanced DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com.
