Confused - Data Submits

[Arg81]

Adding New Records to 3 tables.

Hi Guys,

Been here before with this but no-one had a suggestive answer unfortunately.

I'm sure this is a problem I caused, but I can't see where.

I have 3 tables.
Table1 FieldID1 is linked to Table2 FieldID1.

Table2 FieldID1 is linked to Table3 FieldID1.
Table2 FieldID2 is linked to Table3 FieldID2.

When I add a new record to Table1, the FieldID is cascaded to Table2 - this is represented by a DataGrid.
When I add a record to Table2, FieldID1 is cascaded to Table3, BUT FieldID2 ISN'T.
This is shown either by using the DataGrid Relationship (+), which when clicked to display Table3 rows, FieldID2 is set to NULL.

HOWEVER, if I then click into a new row in the DataGrid and back to the one I've just added, and then click the relationship, FieldID2 is now shown correctly, and data can be successfully added to Table3.

Is this a bug in VS.Net, or something in my programming? The datagrid is set to the relationship between Table1 and Table2, and I've tried adding another DataGrid for the relationship between Table2 and Table3, but still have the problem of it being NULL unless clicked out of that record.


Thanks!
Luke

 

[Arg81]

Please can someone help!!

This problem has totally stumped me.

 

[Arg81]

Please please please please please (with an added cherry on top)

 

[gripusa]

first of all going to other issues , i should say your db desing is not pragmatic because how u can link to tables with ciruclar reference i dont understand it at all. i advice you to rethink over ur design

 

[Arg81]

I've just followed books and references - I've been doing DB design in Access and SQL for 3 years or so, so it's not my design!

What exactly do you mean by circular reference?

In more detail;

Table1 is a table which houses information about an order. I.E. OrderID, Customer, ContactName, ContactDetails, CreatedDate, RequestedDate etc etc.

Table2 is a table which houses revisions for orders. Therefore, it has OrderID, RevisionID, RevisionType, Revision, CreatedDate, CreatedBy.

Table1 and Table2 have a relationship between OrderID.

Table3 is a table which houses Employee hours (time spent) on revisions. Therefore it has a WorkID (not relevant to any relationships), OrderID and RevisionID. It also has EmployeeName and TimeSpent - more than one employee can work on a revision.

Table2 and Table3 have a relationship between OrderID and RevisionID.

Therefore in VB design, table1 is shown as textboxes, table2 as a datagrid and table3 as a datagrid.

I can't see any circular reference there at all, unless I'm missing something....

 

[gripusa]

hi if u r much experianced then i m surprised to see u stil persist on this design
look at the matter, as general Third table should have relation either One to Many or Many to One but as u are trying to design it will say like this that Table2 and Table3 are linked with each other thorugh Many to Many relation
as Many employees can link many revisions and one revision can link to many employees (as suggested by more then one employee can work on a revision)
so in this sitution u must go for a third table , please correct me if i am wrong!!!

 

[Arg81]

Ignore the bit for the user - they are only in that table as a reference, not a link / relationship.

Starting from the top.

One OrderID can have many Revisions. I.E. 1.1, 1.2, 1.3, 1.4, 1.5. however, if 1.5 was to be entered again, it would not be allowed due to integrity, the next logical step would be 1.6

Each Revision can have more than one person work on that revision. my Work Table has WorkID (auto increment, primary key), OrderID and RevisionID, EmployeeName and TimeSpent. Both OrderID and RevisionID have to appear as RevisionID could be 1,2,3,4,5 etc for any OrderID.

Therefore:

OrderID: 1

Links down into Revisions:
OrderID: 1
RevisionID: 1
Revision: Had to change the colour of the panels

OrderID: 1
RevisionID: 2
Revision: Customer didn't like the colour, changed yet again

Then for Time Spent:

WorkID: 1
OrderID: 1
RevisionID: 1
Employee: Luke
TimeSpent: 1.25

WorkID: 2
OrderID: 1
RevisionID: 1
Employee: Paul
TimeSpent: 0.75

WorkID: 3
OrderID: 1
RevisionID: 2
Employee: Luke
TimeSpent: 2.0


That hopefully makes my design make more sense, but I can't see anything wrong with the way table2 is linked to table3.

Luke

 

[gripusa]

so what i got from ur explaination , it will be a structure like that
Table 1 [OrderID] ---> Table2 [OrderID] 1--->Many
Table 2 [OrderID,WorkID] --> Table3[OrderID,WorkID] 1--->Many
and now i m looking your problem thnx for taking ur time

 

[Arg81]

There is no WorkID in Table2. RevisionID is part of the primary key along with OrderID so that integrity stops the same OrderID and RevisionID being added twice.

WorkID only appears in table3, to be honest I only added it due to needing a primary key in that table.

 

[WellsCarrie]

Are You rebinding the datagrid for table 3 after each update to the datagrid for table 2? from you inital description it apears your "refresh/rebind" of table 3's datagrid is not happening in the right place.

I have an app similar to this and had the same problem.

My fix steps were as follows

read input for new row table 2
update table 2
update table 3 (for you you should be putting in WorkID, OrderID(from new row in table 2), and RevisionID (also from new row in table 2))
Rebind table 2 data to datagrid "2"
rebind table 3 to datagrid "3"
Display to User.

Optionally you can go ahead and run the edit request for your "new" row in table 3's datagrid as well.

If this doen't help post Your code and I'll see what I can do.