Data Relation question

microsoft.public.dotnet.languages.vb

13 Apr 2005 7:46 PM

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

13 Apr 2005 8:58 PM

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

Show quoteHide quote

"Gary Paris" <y***@somewhereovertherainbow.com> wrote in message
news:uXkt%23FGQFHA.3788@tk2msftngp13.phx.gbl...
> 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
>
>
>

13 Apr 2005 9:25 PM

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"

Show quoteHide quote

"Richard Myers" <f***@address.com> wrote in message
news:%23foTXxGQFHA.688@TK2MSFTNGP14.phx.gbl...
> 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" <y***@somewhereovertherainbow.com> wrote in message
> news:uXkt%23FGQFHA.3788@tk2msftngp13.phx.gbl...
>> 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
>>
>>
>>
>
>

28 Oct 2004 8:42 PM

Richard Myers

"Gary Paris" <y***@somewhereovertherainbow.com> wrote in message
news:%23VQlC9GQFHA.3716@TK2MSFTNGP14.phx.gbl...
> Wouldn't my SQL query only get Event records that were in the Contact
table?

Yes. But as i suspected you are using criteria and a join to pull contact
data from the database but you are not using this same criteria when
pulling contacts related to events. Your previous SQL statement is grabbing
all contacts if they have an event but the SQL below is only pulling staff
contact data where code = c.

Therefore you will have event records in Table2 of your dataset which have
contact.sysids for contactS which are not staff with a code.type of =c.
Which is why you are getting the error re:child records (event table
contacts=all) not being in Parent Table (contact who are staff with a code
= c).

Richard

Show quoteHide quote

> 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"
>
>
>
> "Richard Myers" <f***@address.com> wrote in message
> news:%23foTXxGQFHA.688@TK2MSFTNGP14.phx.gbl...
> > 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" <y***@somewhereovertherainbow.com> wrote in message
> > news:uXkt%23FGQFHA.3788@tk2msftngp13.phx.gbl...
> >> 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
> >>
> >>
> >>
> >
> >
>
>