How do I commit changes to a table within the subroutine

[HDokes]

Well greetings yet again.

 

Ok.... I have this routine which is initiated on a button click to add a record to a table. At the time the record is added I then need to automatically add many more records to a child table within the same form.

 

A field in the child table assumes a value in an autonumber field of the current (new) record of the parent table. The dilema is that the parent table does not actually update until the subroutine (button_click) is exited.

 

Is there a way to 'force' the update of the table within the subroutine so that the rest of the code can then initiate without an error due to a null value sitting in that field?

[angula]

Are you using a dataset? Are you updating the dataset of the parent table and then working with the child table? Can you post a sample of your code in question?

 

Perhaps calling:

 

daMyDataAdapter.Update(dsMyParentDataSet1.MyParentTable)

 

Is this along the lines of what you are referring to?

[angula]

Also, are you by chance using the drDataRow.BeginEdit and the drDataRow.EndEdit?

 

.BeginEdit places the row in "edit mode" and you can work with the proposed values before they are saved.

[MarkSn]

I too have this problem

 

I can verify that this is a problem. I have an application that does a similar thing. I want to add records to both the parent and the child table in the same update function.

 

I originally just has a single call to da.Update, passing my dataset. Then I read somewhere that when updating multiple tables, it is preferable to process the deletes first, then the adds, then the updates, so I modified my code to simply pass those rows in batches, like this:

 

' first, deleted rows are processed, in child to parent order

daChild.Update(dtChild.Select(Nothing, Nothing, DataViewRowState.Deleted))

daParent.Update(dtParent.Select(Nothing, Nothing, DataViewRowState.Deleted))

 

' next, inserted rows are processed, in parent to child order

daParent.Update(dtParent.Select(Nothing, Nothing, DataViewRowState.Added))

daChild.Update(dtChild.Select(Nothing, Nothing, DataViewRowState.Added))

 

' last, all of the updated rows are processed

daParent.Update(dtParent.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))

daChild.Update(dtChild.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))

 

But this fails when trying to insert the child records, and upon examination, they don't have the right foreign key value. That wasn't really surprising, because further investigation revealed that neither does the parent record following its insertion.

 

So somehow, I need to get in there and update the primary key field of all added records in the parent table with the value the database assigned as the autonumbered value, and then go through and update all the new records from the child table that reference that record so they now indicate the new foreign key value, and this needs to happen after the parent record is inserted, but before I insert the child records? Lovely.