Cathi,
I do have all of the necessary info set - I've set the SP to run for both tables, set the connection, used SqlCommandBuilder.DeriveParameters(Command) - it works and writes all of the data if I have no error - it is only when I introduce an error it blows up.
I am not using a data adapter. I adapted some of the code JVP wrote in CoDe and moved it over to C# and fixed up a few bugs and cleaned up the SQL - it really is quite sweet. I think I fixed the actual problem (I had two records in the Header record, not one) - I'll let you know. Thanks for your help, you have posted quite a few really good examples.
Thanks Again,
Doug
The CommandBuilder does not actually generate the updating logic when you instantiate it. If you instantiate a CommandBuilder object and later call DataAdapter.Update, the CommandBuilder will not actually build the updating logic until you call the DataAdapter object's Update method. When you call DataAdapter.Update, the CommandBuilder will fetch the required metadata from the database using the DataAdapter object's SelectCommand. You have not associated the Command object in the SelectCommand property with the newly created transaction. As a result, the CommandBuilder cannot use the SelectCommand and the CommandBuilder throws an exception. To get around this, you need to force the CommandBuilder to generate updating logic before starting the transaction. You can do this by calling the CommandBuilder object's GetUpdateCommand, GetInsertCommand, and GetDeleteCommand method. You can then associate the Command objects that the CommandBuilder generated with the new Transaction object using the following sample code, and the DataAdapter will submit the updates within the transaction: