Question Updating MASTER / DETAIL Records

[SteveR]

Hope someone can help / point me in the right direction, as I have been looking this up for some while, and am stumped.

I have VB 2010 accessing Access via the normal OLEDB connection (project data source). A Form containing MASTER/DETAIL Records - created by making a dataset from both tables, setting the relationship to cascade all changes etc and with FK + relation constraints). Simply dropped the MASTER Data Source onto the form as detail records, and then the subordinate DETAIL table as a Datagridview.

I am loading the MASTER first, and applying updates MASTER then Child ( as I believe I should)

Save code - autogenerated..

Me.Validate()
Me.PurchasesBindingSource.EndEdit()
Me.PurchaseItemsBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.PURCHASESDataSet)

The form correctly displays the right detail records, and allows me to ADD new child records. BUT - If I add a new MASTER, then go straight to CHILD - I get an error immediately advising that I have violated Foreign Key Constraints (ie; my FK in the detail table is showing as -1 and has no corresponding Master Records.

Appreciate I must be doing something basically wrong, but this sort of thing worked fine back in VB6, and whilst I have a work-around - this simple conundrum is driving me mad.

Help appreciated please

Steve

 

[jmcilhinney]

I think that you've left out a bit of information. I think I know what's going on though. Generally speaking, you'll be using an AutoNumber column as your primary key. When you add a row to your DataTable in your application, it will generate a temporary PK value. When you save that row to your database, it will generate a final PK value. If your DataTable still contains the temporary value though, any child rows you add to the DataSet will contain an invalid foreign key. When you save a new row from your DataTable to the database, you need to retrieve the final AutoNumber value from the database back into the DataTable. The DataRelation in your DataSet will then take care of propagating the PK value from the parent DataTable to the FK column in the child DataTable. To learn how to do that, check this out:

Retrieve Access AutoNumber Value After Insert

 

[SteveR]

Thanks - a couple of issues...

I think that you've left out a bit of information. I think I know what's going on though. Generally speaking, you'll be using an AutoNumber column as your primary key. When you add a row to your DataTable in your application, it will generate a temporary PK value. When you save that row to your database, it will generate a final PK value. If your DataTable still contains the temporary value though, any child rows you add to the DataSet will contain an invalid foreign key. When you save a new row from your DataTable to the database, you need to retrieve the final AutoNumber value from the database back into the DataTable. The DataRelation in your DataSet will then take care of propagating the PK value from the parent DataTable to the FK column in the child DataTable. To learn how to do that, check this out:

Retrieve Access AutoNumber Value After Insert

Thanks - had seen a few of your posts, and wish I'd found the above a week ago!

Trying to match the logic to my particular scenario, and whilst it was going well - I have run into 2 issues.
1. Dim lastAutoNumber = Me.parentAdapter.GetLastAutoNumber().Value. Throws an error that GetLastAutoNumber is not a member of ... table adapter. Where does all that underlying code get generated / suggests it is generated by ??
2. Private Sub HandleRowUpdatedEvents() AddHandler Me.PurchasesTableAdapter.Adapter.RowUpdated, AddressOf PurchasesTableAdapter_Rowupdated (the PARENT - errors that _RowUpdated is not declared)
AddHandler Me.PurchaseItemsTableAdapter.Adapter.RowUpdated, AddressOf PurchaseItemsTableAdapter_RowUpdated

Is there some specific sequence of steps to get to your solution. What I did was ..

Added database to the Project / Created the DATASET from the project Data
Dropped a dataset onto the form (linked to the above DATASET)
Dropped the Parent/Child Data Source onto the form to create the table adapter/binding sources/binding navigator etc. Then did the same for the child grid view. Displays OK, but run into the above code issues

Thanks for your help. Steve

 

[JohnH]

I believe this article explains the same concept: Using TableAdapters to Insert Related Data into an MS Access Database | Beth Massi

 

[SteveR]

Thanks John - I had already looked at another of Beth Massi's posts, but alas - maybe my lack of coding expertise in this area - her solution to the problem missed a key point (she claimed that if the relationships were set up, then the cascade option automatically populated the FK values and performed the parent/child updates (no extraneous code was proposed, but her solution did not work for FK key constraint failures.

I have tried the code in the post shown, and OK - much doesn't make a lot of sense, but if I could at least get it working - it would let me step through the logic and get to grips with it. It wont compile (as below) - Dont know why, nor do I understand what the error message is directing me to do.


Private Sub _adapter_RowUpdated(ByVal sender As Object, _
ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) _
Handles _adapter.RowUpdated ................... REQUIRES A WITHEVENTSVARIABLE .....

AccessIDHelper.SetPrimaryKey(Me.Transaction, e) .....................TRANSACTION IS NOT A MEMBER OF DATASET TABLE ADAPTER

 

[jmcilhinney]

1. Dim lastAutoNumber = Me.parentAdapter.GetLastAutoNumber().Value. Throws an error that GetLastAutoNumber is not a member of ... table adapter. Where does all that underlying code get generated / suggests it is generated by ??

GetLastAutoNumber is a method that you add to the table adapters by adding a new query in the DataSet designer.

2. Private Sub HandleRowUpdatedEvents() AddHandler Me.PurchasesTableAdapter.Adapter.RowUpdated, AddressOf PurchasesTableAdapter_Rowupdated (the PARENT - errors that _RowUpdated is not declared)
AddHandler Me.PurchaseItemsTableAdapter.Adapter.RowUpdated, AddressOf PurchaseItemsTableAdapter_RowUpdated

It certainly does not say that. Maybe it says that 'PurchasesTableAdapter_Rowupdated' is not declared so the obvious fix is to declare it. In my demo, I have a 'parentAdapter_RowUpdated' method that handles the RowUpdated event of the parent table adapter. If you want to handle a RowUpdated event of a table adapter with a method named 'PurchasesTableAdapter_Rowupdated' then you actually need to have a method with that name.

 

[JohnH]

Thanks John - I had already looked at another of Beth Massi's posts, but alas - maybe my lack of coding expertise in this area - her solution to the problem missed a key point (she claimed that if the relationships were set up, then the cascade option automatically populated the FK values and performed the parent/child updates (no extraneous code was proposed, but her solution did not work for FK key constraint failures.

It works like that for SQL Server. Problem is with getting updated IDs from MSAccess database. As can be seen in this article Walkthrough: Saving Data from Related Data Tables (Hierarchical Update) for Sql Server only two EndEdit calls are added manually in code for hierarchical updates to work properly.

I have tried the code in the post shown, and OK - much doesn't make a lot of sense, but if I could at least get it working - it would let me step through the logic and get to grips with it. It wont compile (as below) - Dont know why, nor do I understand what the error message is directing me to do.


Private Sub _adapter_RowUpdated(ByVal sender As Object, _
ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) _
Handles _adapter.RowUpdated ................... REQUIRES A WITHEVENTSVARIABLE .....

AccessIDHelper.SetPrimaryKey(Me.Transaction, e) .....................TRANSACTION IS NOT A MEMBER OF DATASET TABLE ADAPTER

You must match your ...DataSetTableAdapters namespace here: Namespace ProductsDataSetTableAdapters
and match both your ...TableAdapter here: Partial Public Class CategoriesTableAdapter
and here: Partial Public Class ProductsTableAdapter

From post 1 it looks like yours is:
PURCHASESDataSetTableAdapters
PurchasesTableAdapter
PurchaseItemsTableAdapter

 

[SteveR]

Thanks for the feedback - appreciate I have 2 minds on this with far more knowledge than mine.

John: I will work through your comments with Beth Massi's code, and hopefully all will be well.

JMC: Have fixed the 2 bugs that had been thrown up, and understand your comments. As far as I can tell, I have created a 'copy' of your published logic on my own parent/child dataset and it displays records fine. Unfortunately, as soon as I step off the new record in the 'child grid' with a new parent an exception is thrown (System.Data.InvalidConstraintException: Foreign Key Constraint). The rowupdate code hasn't been invoked at that stage, and I am unable to see where this is falling over?

It doesn't seem to be showing an error in the ChildDataGridView_DataError handler and creating this stub avoids the error, but obviously doesn't save the record. Otherwise, the error implies System.Data.DataView.FinishAddNew, but I cannot see why this is firing in my example, whereas yours works. There must be a hidden difference somewhere ( I have set the datasets up with the same relationships as yours / form code is identical, and have added the GetLastAutoNumber code to each table, and this is working fine when I preview the code in the designer )?

 

[SteveR]

UPDATE. Not sure if I've just had an Epiphany on this, but if I just add an event to the child Binding source - as below, then that seems to do the job?

Not fully checked it through, but seems to work 100%, and obviously needs some tidying up/error handling...

Private Sub ChildBindingSource_AddingNew(ByVal sender As System.Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles ChildBindingSource.AddingNew
Me.ParentBindingSource.EndEdit()
End Sub

 

[JohnH]

UPDATE. Not sure if I've just had an Epiphany on this, but if I just add an event to the child Binding source - as below, then that seems to do the job?

That's one of the two manual EndEdit calls I mentioned. In MSDN walkthrough article that is described around "To add code to commit parent records in the dataset before adding new child records".

You may or may not have to commit new parent records; it depends on the type of control that is used to bind to your data source. In this walkthrough, you use individual controls to bind to the parent table; this requires the additional code to commit the new parent record

Beth Massi did the same with grid Enter event and said "You must commit the parent row to the DataTable before adding child rows".

BindingSource.EndEdit Method "Applies pending changes to the underlying data source", this basically means that the new parent record is still only text in textboxes and needs to be added to the data source (DataTable).

 

[tzeseng_]

Found the solution here

Hope someone can help / point me in the right direction, as I have been looking this up for some while, and am stumped.

I have VB 2010 accessing Access via the normal OLEDB connection (project data source). A Form containing MASTER/DETAIL Records - created by making a dataset from both tables, setting the relationship to cascade all changes etc and with FK + relation constraints). Simply dropped the MASTER Data Source onto the form as detail records, and then the subordinate DETAIL table as a Datagridview.

I am loading the MASTER first, and applying updates MASTER then Child ( as I believe I should)

Save code - autogenerated..

Me.Validate()
Me.PurchasesBindingSource.EndEdit()
Me.PurchaseItemsBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.PURCHASESDataSet)

The form correctly displays the right detail records, and allows me to ADD new child records. BUT - If I add a new MASTER, then go straight to CHILD - I get an error immediately advising that I have violated Foreign Key Constraints (ie; my FK in the detail table is showing as -1 and has no corresponding Master Records.

Appreciate I must be doing something basically wrong, but this sort of thing worked fine back in VB6, and whilst I have a work-around - this simple conundrum is driving me mad.

Help appreciated please

Steve

This threat resolve the hierarchical update
http://www.vbforums.com/showthread.php?659052-Retrieve-Access-AutoNumber-Value-After-Insert&highlight=

 

[jmcilhinney]

This threat resolve the hierarchical update
http://www.vbforums.com/showthread.php?659052-Retrieve-Access-AutoNumber-Value-After-Insert&highlight=

That's the same link I provided in post #2, over a year ago. It's one of mine, so I'm glad you found it useful.

 

[tzeseng_]

Thank you

Yes. Thank you very much, jmcilhinney.