473,322 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Update of a Master-Detail Hierarchy of Three levels

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
Nov 21 '05 #1
0 920

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: PawelR | last post by:
Hello Group, In my apps I fill DataSet from MSAcces (file db1.mdb) oleDbDataAdapter1.Fill(fromAccessDS,"MyTable") MyTable heve 4 columns, I change in dataGrid...
4
by: Igor Kryltsov | last post by:
Hi, If you can help me to correct my mistake. To simplify my question: I have table: create table test ( name varchar(10),
1
by: Joseph Chase | last post by:
I am running version 4.1.13a-log on a Mac XServe. How can I receive a 'duplicate entry' error for an UPDATE? An update isn't creating an entry, so why this error message? ...
8
by: savvy | last post by:
I'm developing a shopping cart. I've assigned some Session values to Labels on the Master Page. The Basket panel which is small window for the basket items will be visible on every page if there...
0
by: Managed Code | last post by:
Hello All, Here is my issue and thanks in advance for any assistance. I have a base page with a dropdownlist that fires an event with the selected index. The content page catches the event and...
1
by: Dmitri | last post by:
Hi! I have a stored procedure that takes 22 minutes to run in one environment, that only takes 1 sec or so to run in another environment. Here is the exact situation: Database 1 on Server 1...
3
by: Alvin SIU | last post by:
Hi all, I am using DB2 in AIX. I have a master table and a txn table. The master table has many many records. Each month, there is a new txn table.
1
by: cmorga23 | last post by:
How do I combine the following two update query's???? Example: UPDATE py_per_stu_email SET py_eml_un = (SELECT py_email_un FROM master WHERE master.id = py_per_stu_email.id) Where py_eml_un ...
1
by: Rosy | last post by:
I have a form "A" that is set on table vessel names. I have subform "B" that is set on table vessel info. I want form "B" to update based on what vessel I choose in form "A". I know this is...
1
by: Sharma Neha | last post by:
I hav a problem in sql query.I m using C#.net and working on window form,Database is SqlSever. There is a table tracking which has key_no,status as its field. There is another table master which...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.