Hello,
I face a problem concerning an Update of a Master-Detail Hierarchy of Three
levels.
• tblWorkOrders (Master)
• PK: workorderID
o tblWorkOrderTasks (Detail)
o PK: workorderTaskID
o FK: workorderID
tblConsumedMaterials (Detail to tblWorkOrderTasks)
PK: materialID
FK: workorderTaskID
All PK’s are DataType Integer and AutoIncrement.
The DataGrids are bound like:
Me.DataGridWorkOrders.DataMember = "tblWorkorders"
Me.DataGridWorkOrders.DataSource = Me.dsWorkOrders
Me.DataGridWorkOrderTasks.DataMember =
"tblWorkorders.tblWorkorders_tblWorkorderTasks "
Me.DataGridWorkOrderTasks.DataSource = Me.dsWorkOrders
Me.DataGridConsumedMaterials.AutoEdit = True
Me.DataGridConsumedMaterials.DataMember =
"tblWorkorders.tblWorkorders_tblWorkorderTasks.tbl WorkorderTasks_tblConsumedMaterials"
So they share the same “Base Path”.
(suppose a new workorderTaskID should be 159)
When I add a new row to DataGridWorkOrderTasks AND there were already
existing rows for that workorder, then there is no problem (the right FK is
inserted – workorderTaskID = 159)
When I add a new row to DataGridWorkOrderTasks AND there is NO row yet for
that workorder, then there is a problem because the FK is “too high”
(workorderTaskID = 160 or higher).
Performing the update (to the DB) then still doesn’t produce a problem.
BUT
If I have that situation and (after adding that new ‘first’ row to
DataGridWorkOrderTasks) add a new (of course) ‘first’ row to
DataGridConsumedMaterials, then the update will raise an error (FK
workorderTaskID in tblWorkorderTasks) because workorderTaskID = 160 does not
exist.
What seems to happen when calling the SqlDataAdapters.Update-Methods:
1. sdaWorkOrders.Update(dsWorkOrders.GetChanges(DataR owState.Added))
[workorderID existed already]
2. sdaWorkOrderTasks.Update(dsWorkOrders.GetChanges(D ataRowState.Added))
[New workorderTaskID Created 160 ->SQL Server makes that 159]
3. sdaConsumedMaterials.Update(dsWorkOrders.GetChange s(DataRowState.Added))
[New Row’s FK Set 160 -> does not exist -> FK-Error Raised]
This is purely caused because I have a hierarchy 3 levels deep combined we
three “complex” datacontrols.
TIA,
Michael