Thread: Getting Primary Key Value for Detail Record

Hello,

Here is my problem I need to resolve.

I am retrieving and editing data from the Customer and Order tables. The primary key in the Customer table is CustomerID and the foreign key in the Order table is CustomerID. The relationship between these tables is a one to many. I had created one ADO dataset to store these two tables. I also had created a data relation that joins both of these tables in the dataset. In addition to this, I had set the child key constraint for this relation to be cascade update. I had assigned this dataset to the Customer and Order grid controls with the Customer grid control located at the top of the form while the Order grid control is located at the bottom of the form.

When a person adds a customer record to the Customer grid control, the person would then add an order record in the Order grid control right after adding the new customer record. For the Order grid control to save the new order record in the database, I had to do the following:

1) use the data adapter to save the new customer record in the database so that I can get the auto number primary key field in the Customer table of the database to generate a primary key value for this new customer record

2) use the reset command to clear the entire dataset that was used by both grid controls

3) retrieve the data from the Customer and Order tables in the database and then place them in the same dataset that was cleared in the previous step

4) recreate the data relation that binds these two tables

5) clear the datasource for both grid controls

6) re-assign the Customer table in the dataset to the Customer grid control

7) re-assign the Order table and data relation to the Order grid control

8) program retrieves the CustomerID value for this new customer record in the Customer grid control

9) program than assigns this CustomerID value to the foreign key column in the Order grid control for the new order record. This is done when the person starts adding the new order record in the Order grid control.


Is there a shorter way of getting the new primary key value for the customer record rather than performing all of the nine steps as stated previously? That is, can I get the new primary key value for the new customer record and then have it assigned to the new order record without having to save the new customer record in the database?


Thanks.

You don't have to do any of that. Pretty much all you have to do is add the data and, when you're done, save it. Everything else happens automatically.

The first step is to make sure the grids are bound correctly. Here's how to perform parent/child data-binding:

Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

Once that's done you are good to go. You add a parent record to the parent grid and the DataTable automatically generates a temporary ID that may or may not be the same as the permanent ID that will eventually be generated by the database. It doesn't matter either way. The child grid will display all the child records related to the currently selected parent. That parent is new so the child grid is empty. You then add a new record to the child grid. The one manual step you do need to perform is get the ID from the current parent, which you can get from the Current property of the parent BindingSource, and assign that to the foreign key field of the new child. The DataTable will also generate a temporary ID for the child record. You can keep doing that as much as you want, adding as many parents as you want and as many children to each parent as you want.

Now, when it comes time to save, you must save the parent DataTable first. All the new parent records will get inserted into the database and permanent IDs will get generated for them. If you've configured you're adapter correctly, those IDs will get propagated back to the parent DataTable. Because you have configured your DataRelation to cascade updates, those new IDs will also get propagated to the child DataTable. You can then save the child records and they will be inserted with the correct foreign key values.

One point to note is that, if you're deleting records, you must delete children before deleting parents but you must insert parents before inserting children. As a result, you may have to do your entire save in four stages instead of two.

Originally Posted by jmcilhinney

You don't have to do any of that. Pretty much all you have to do is add the data and, when you're done, save it. Everything else happens automatically.

The first step is to make sure the grids are bound correctly. Here's how to perform parent/child data-binding:

Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

Once that's done you are good to go. You add a parent record to the parent grid and the DataTable automatically generates a temporary ID that may or may not be the same as the permanent ID that will eventually be generated by the database. It doesn't matter either way. The child grid will display all the child records related to the currently selected parent. That parent is new so the child grid is empty. You then add a new record to the child grid. The one manual step you do need to perform is get the ID from the current parent, which you can get from the Current property of the parent BindingSource, and assign that to the foreign key field of the new child. The DataTable will also generate a temporary ID for the child record. You can keep doing that as much as you want, adding as many parents as you want and as many children to each parent as you want.

Now, when it comes time to save, you must save the parent DataTable first. All the new parent records will get inserted into the database and permanent IDs will get generated for them. If you've configured you're adapter correctly, those IDs will get propagated back to the parent DataTable. Because you have configured your DataRelation to cascade updates, those new IDs will also get propagated to the child DataTable. You can then save the child records and they will be inserted with the correct foreign key values.

One point to note is that, if you're deleting records, you must delete children before deleting parents but you must insert parents before inserting children. As a result, you may have to do your entire save in four stages instead of two.

I didn't use a Binding Source when connecting the controls to the dataset. I had modified my code to include BindingSource using the following code:

'Get the data. The DataSet must contain a Parent table,
'a Child table and a ParentChild relation between them

Dim data As DataSet = Me.GetDataSet()

'Bind the parent source to the parent table.
Me.BindingSource1.DataSource = data
Me.BindingSource1.DataMember = "Parent"

'Bind the child source to the relationship.
Me.BindingSource2.DataSource = Me.BindingSource1
Me.BindingSource2.DataMember = "ParentChild"

The above code did work and my Customer and Order grid controls did show the correct records using the respective binding sources while moving from one customer record to another. The above code was obtained from: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms).

When a person press a button on the Order grid control for adding a new order record to this grid control right after adding a new customer record in the Customer grid control, an event is fired by this grid which allows me to assign default values to the new order record. In this event, I had placed the following code to obtain the temporary CustomerID from the new customer record:

Dim New_Record as DataRowView
Dim str_Foreign_Key as String

New_Record = Customer_Table_Binding_Source.Current

str_Foreign_Key = New_Record.Row("CustomerID").ToString.Trim


The above code does not work because I get a null value assigned to the str_Foreign_Key variable. The Customer_Table_Binding_Source is created by assigning the dataset which has the Customer and Order tables in it. The DataMember property of the Customer_Table_Binding_Source has been assigned the name of the Customer table which is used by this dataset. What am I doing wrong using the above code? What is the correct code I should use? Please provide code example.

Even if I do not get the temporary CustomerID and have it assigned to the foreign key column of the new order record, the binding source is still able to store it. When I move to another customer record and back to the new customer record in the Customer grid control, I still see the new order record that was entered by the person in the Order grid control. Does assigning the temporary CustomerID to the new order record is really necessary given what I has just stated in this paragraph?

My real problem lies in saving the order record in the database. When I use the data adaptor to save the order table to the database, I get an error message telling me this record can not be saved in the database because no foreign key value had been assigned to this new order record. As stated in my previous post, I had set the child key constraint in the relation for these two tables as cascade update. I thought by doing this, the new primary key value for the new customer record would automatically be assigned to the foreign key column of the new order record. When saving the customer and order records to the database, I had done the following:

1) Used the data adaptor for the Customer table to save the new customer record in the database. An example of code I had used is shown as follow:

da_Customer.Update (ds_Sales_Data, "Customer")

This data adapter is called in the FormClosed event.

2) Used the data adapter for the Order table to save the new order record in the database. An example of code I had used is shown as follows:

da_Order.Update (ds_Sales_Data, "Order")

This data adapter is called in the FormClosed event. This adapter is called after the data adapter for the Customer table had been called.

The new customer record does get recorded in the database but the new order record does not. How can I solve this problem?

In you reply to my post, you had stated that the data adapter for the Customer table should be called first. I agree with this. You had also stated that the data adapter should be configured in a way that would allow the new CustomerID for the new customer record to be propagated to the new order record(s). What do you mean by this? How do I set up the data adapters for these two tables so that I can get this action to happen? Please provide code example. As I had stated previously, I had set the child key constraint to be cascade update.


Thanks.

All data is stored in an Access 2007 database.

Sounds a lot more complicated that it needs to be. The most elementary walkthrough from Microsoft that I usually recommend to people will display related data (it's even a Customer/Order type) and save it.. Take a look in my signature, click DW4, and read Walkthrough Creating a Simple Data App

Start a new project, don't be tempted to try and remodel your existing - i know you've poured hours into this code, but start with a clean sheet to do the tutorial THEN make a decision as to how you want to proceed - you'll probably throw out your old project entirely