Saving Master-Detail bound form - primary key is identity column

[John Brown]

Hello All,

I apologise for the length of this post, but please bear with me.

I have a master table Master, and a detail table Detail. The primary key is
an identity column PK, which is a foreign key in Details.

Master fields are bound to a set of textboxes. The corresponding
Detail records are in a DataGridView which is bound :
- .BindingSource = Master table's binding source
- .DataMember = DataRelation from Master to Detail.

I can navigate through the Master records using a BindingNavigator. The
corresponding details are displayed correctly. I can update an existing
Master and/or Detail record. However, I cannot save a new record properly.

I currently have 1 Master with 2 corresponding Detail records. The primary
key PK = 1.

1) When I create a new Master record, PK = 2. I don't know where this 2 is
coming from. In any case, it can't know what the value of the identity column
is until it saves the record. I know that the program does not try to insert
this value; PK is not in the INSERT statement of the Master DataAdapter.

2) Having filled out the Master fields, I can insert a row in the Details
grid. The foreign key defaults to 2, which would be good if it were the
correct value.

3) If I move from the new Details row to the Master textboxes, I violate the
foreign key constraint. It says that the 2 in the Detail grid's foreign key
column must exist in the Master table. At this point, I have not tried to
save, so it is not a message from the database (SQL Server). I assume this
means that at the point where I am entering the Detail record, the new Master
record has not yet been inserted into the DataTable.

3) When I try to save, I violate the foreign key constraint. This time the
message is from SQL Server. I can save if there are no rows in the Details
grid, but 2 is still displayed in the PK textbox. Right now, 'dbcc
checkident(Master)' returns 16.

I think that the solution to my various problems is:

a) When I add the first row to the Details grid, the current Master row
(also new) should be inserted into the Master DataTable
(MasterBindingSource.EndEdit() maybe?)

b) When I save, I should:
- Save the Master record
- Refresh it somehow, to get the correct value of PK
- Update the foreign key in all Detail rows with the correct PK value
- Save the Details records
- Refresh the Details records (Details also has an identity column as its
primary key, although I think that this is not a problem for me right now.)

Am I right? What is the best way to accomplish all of this?

Regards,
Alias John Brown.

 

[Gregory A. Beamer]

b) When I save, I should:
- Save the Master record
- Refresh it somehow, to get the correct value of PK
- Update the foreign key in all Detail rows with the correct PK value
- Save the Details records
- Refresh the Details records (Details also has an identity column as its
primary key, although I think that this is not a problem for me right
now.)

Am I right? What is the best way to accomplish all of this?

What you want to look at is saving parent child records. This one is
probably a good one to start with:
http://msdn.microsoft.com/en-us/magazine/cc188728.aspx

Your basic idea is correct, but there are some ways to make this easier.
When you save the master record, you can retrieve the record and pull its
id. This is extremely easy with the tableAdapter methodology. You then
update the other records. Read the article. If not enough for you, do a
google on saving parent child dataset or something similar and you will find
others.


--
Peace and Grace,
Greg

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************

 

[John Brown]

What you want to look at is saving parent child records. This one is
probably a good one to start with:
http://msdn.microsoft.com/en-us/magazine/cc188728.aspx

Your basic idea is correct, but there are some ways to make this easier.
When you save the master record, you can retrieve the record and pull its
id. This is extremely easy with the tableAdapter methodology. You then
update the other records. Read the article. If not enough for you, do a
google on saving parent child dataset or something similar and you will find
others.

Thanks for the link. I searched high and low and could not find an article
on how to deal with an identity column primary key. I eventually solved it
following my original plan, but I will be sure to read the article, since my
method is inefficient (although acceptable for my situation.)

Regards,
Alias John Brown.