Data Relation question

[Gary Paris]

I have two datasets. The first dataset is a selection of Contact
information with a field from a Codes table and a field from a Staff table.
When I run the Query, everything is OK. Dataset gets filled and I display
data in the Datagrid. Life is good.

I then want to fill another dataset with Events records.

Regarding the Contact and Events tables, the Contact key is "sysid". The
foreign key in the Events table is "con_id".

Here is the query for the Events table

--------------------------
SELECT event.[date], event.[time], event.[desc], event.staff,
event.client, event.con_id FROM event INNER JOIN contact ON event.con_id =
contact.sysid ORDER BY event.[date]

--------------------------

When I run the query, the dataset gets filled. I am assuming that there is
NO event record unless the keys match from the Contact table.

I then created a datarelation

Dim rel As DataRelation
rel = New DataRelation("ContactEvent", _
DS.Tables("Contact").Columns("sysid"), _
DS.Tables("Event").Columns("con_id"))

DS.Relations.Add(rel)
DG_Contact.SetDataBinding(DS, "Contact")
DG_Event.SetDataBinding(DS, "Contact.Event")

but when I run my program I get the following message:

"This constraint cannot be enabled as not all values have corresponding
parent values"

I would like to show all the contacts in the first datagrid. I would like
to show all related events in the second datagrid. How can I do this?

Thanks,

Gary

 

[Richard Myers]

Seems to me that either you're filling the event data before you're filling
the contact data which i think you have already stated is not the case or
there are Con_Id's in your event data that are not found in your contact
data.

If you are using test data make sure previous debugging/testing efforts
have not altered the integrity of your test data such that some event
con_ids do not corrrespond to contact sysids. The Sql statement you have
provided seems fine although you have not provided the SQL you use to fill
your contacts table. If you are narrowing these through criteria and not
applying the same critieria to the sys_ids you select in the SQL you have
provided you will end up with conids in your event data that are not in
your Contact sysids.

Or it might be something altogether different.
Either way this should be quite simple to debug.

Richard

 

[Gary Paris]

Wouldn't my SQL query only get Event records that were in the Contact table?
I thought the INNER JOIN only got records that matched the criteria. I'm
not 100% sure but I thought so. Anyway here is my original query for the
Contact result set:

"SELECT contact.first_name, contact.last_name, codes.[desc],
contact.con1_02_05 as City, contact.firm, contact.sysid, contact.phone1,
contact.phone2, staff.[first] + ' ' + staff.[last] as Staff FROM contact
INNER JOIN codes ON contact.ccode = codes.ccode INNER JOIN staff ON
contact.staff = staff.init WHERE (codes.type = 'C') ORDER BY
contact.last_name"

Seems to me that either you're filling the event data before you're
filling
the contact data which i think you have already stated is not the case or
there are Con_Id's in your event data that are not found in your contact
data.

If you are using test data make sure previous debugging/testing efforts
have not altered the integrity of your test data such that some event
con_ids do not corrrespond to contact sysids. The Sql statement you have
provided seems fine although you have not provided the SQL you use to fill
your contacts table. If you are narrowing these through criteria and not
applying the same critieria to the sys_ids you select in the SQL you have
provided you will end up with conids in your event data that are not in
your Contact sysids.

Or it might be something altogether different.
Either way this should be quite simple to debug.

Richard

I have two datasets. The first dataset is a selection of Contact
information with a field from a Codes table and a field from a Staff table.
When I run the Query, everything is OK. Dataset gets filled and I display
data in the Datagrid. Life is good.

I then want to fill another dataset with Events records.

Regarding the Contact and Events tables, the Contact key is "sysid". The
foreign key in the Events table is "con_id".

Here is the query for the Events table

--------------------------
SELECT event.[date], event.[time], event.[desc], event.staff,
event.client, event.con_id FROM event INNER JOIN contact ON event.con_id =
contact.sysid ORDER BY event.[date]

--------------------------

When I run the query, the dataset gets filled. I am assuming that there is
NO event record unless the keys match from the Contact table.

I then created a datarelation

Dim rel As DataRelation
rel = New DataRelation("ContactEvent", _
DS.Tables("Contact").Columns("sysid"), _
DS.Tables("Event").Columns("con_id"))

DS.Relations.Add(rel)
DG_Contact.SetDataBinding(DS, "Contact")
DG_Event.SetDataBinding(DS, "Contact.Event")

but when I run my program I get the following message:

"This constraint cannot be enabled as not all values have corresponding
parent values"

I would like to show all the contacts in the first datagrid. I would like
to show all related events in the second datagrid. How can I do this?

Thanks,

Gary