J
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.
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.