So far the dataset just shows
Type, Benefit, Change

I want it to show
Type, Benefit, Change, Description

This should be a pretty elementary question - and I've spent 2 days on it
already.
I just want to add one field from another table into my dataset, should be
easy, but maybe it's the way it is related that is making it so difficult
(there are no totally unique fields in either table, so it needs a multi-join)

I have 2 tables in the dataset already:
Membership
BENFT

To get the Desc Field from the BENFT table, I need to relate 2 fields to
Membership
Membership.Benefit = BENFT.BNFT
Membership.Change = BENFT.BCHG


'This is what I tried so far, but it's not working
daBenefits.Fill(DsBenefits1, "Membership")

Dim relBenefit As New DataRelation("relBen",
DsBenefits1.Tables(0).Columns("Benefit"),
DsBenefits1.Tables(1).Columns("BNFT"))
Dim relBenChg As New DataRelation("relChg",
DsBenefits1.Tables(0).Columns("Change"),
DsBenefits1.Tables(1).Columns("BCHG"))

DsBenefits1.Relations.Add(relBenefit)
DsBenefits1.Relations.Add(relBenChg)


daBenefitDesc.Fill(DsBenefits1, "BENFT")



'DGMembers is the DataGrid
DGBenefits.DataSource =
DsBenefits1.Tables("Membership").DefaultView
DGBenefits.DataBind()

Thank you

Note: Membership is actually a union that I have filled for each piece into
the dsBenefits1

Jonefer: Have you considered using DataSet.Merge? This would entail two
datasets (so you'd have to move/copy the datatable) but I think it'd give
you what you wanted.
"jonefer" <(E-Mail Removed)> wrote in message
news:855BAAC2-0C12-4FAE-8BE6-(E-Mail Removed)...
> So far the dataset just shows
> Type, Benefit, Change
>
> I want it to show
> Type, Benefit, Change, Description
>
> This should be a pretty elementary question - and I've spent 2 days on it
> already.
> I just want to add one field from another table into my dataset, should be
> easy, but maybe it's the way it is related that is making it so difficult
> (there are no totally unique fields in either table, so it needs a
> multi-join)
>
> I have 2 tables in the dataset already:
> Membership
> BENFT
>
> To get the Desc Field from the BENFT table, I need to relate 2 fields to
> Membership
> Membership.Benefit = BENFT.BNFT
> Membership.Change = BENFT.BCHG
>
>
> 'This is what I tried so far, but it's not working
> daBenefits.Fill(DsBenefits1, "Membership")
>
> Dim relBenefit As New DataRelation("relBen",
> DsBenefits1.Tables(0).Columns("Benefit"),
> DsBenefits1.Tables(1).Columns("BNFT"))
> Dim relBenChg As New DataRelation("relChg",
> DsBenefits1.Tables(0).Columns("Change"),
> DsBenefits1.Tables(1).Columns("BCHG"))
>
> DsBenefits1.Relations.Add(relBenefit)
> DsBenefits1.Relations.Add(relBenChg)
>
>
> daBenefitDesc.Fill(DsBenefits1, "BENFT")
>
>
>
> 'DGMembers is the DataGrid
> DGBenefits.DataSource =
> DsBenefits1.Tables("Membership").DefaultView
> DGBenefits.DataBind()
>
> Thank you
>
> Note: Membership is actually a union that I have filled for each piece
> into
> the dsBenefits1
>
>
>

Bill, please help me out. Something as simple as:

SELECT Benefit, Type, Charge, MRN, [GROUP], SGR, BENFT.BNFT_DESC
FROM Membership INNER JOIN BENFT ON (Charge = BENFT.BCHG) AND (Benefit =
BENFT.BNFT);

...should STILL be simple in ADO.NET. Perhaps you think I am asking for
something more difficult?

It's pretty trivial in Access: pull two tables in, connect the two related
fields:

Yet, I've spent two days reading and looking at different posts and sites.
If ADO.NET is really supposed to help make working with databases simpler, I
haven't been convinced yet. I'm talking about adding one field from another
table to a dataset.

Could you or someone just give me the steps? Because I can't search anymore
sites in google to find the answer to this question. I'll read more about it
after I digest how to do something so simple.


"W.G. Ryan - MVP" wrote:

> Jonefer: Have you considered using DataSet.Merge? This would entail two
> datasets (so you'd have to move/copy the datatable) but I think it'd give
> you what you wanted.
> "jonefer" <(E-Mail Removed)> wrote in message
> news:855BAAC2-0C12-4FAE-8BE6-(E-Mail Removed)...
> > So far the dataset just shows
> > Type, Benefit, Change
> >
> > I want it to show
> > Type, Benefit, Change, Description
> >
> > This should be a pretty elementary question - and I've spent 2 days on it
> > already.
> > I just want to add one field from another table into my dataset, should be
> > easy, but maybe it's the way it is related that is making it so difficult
> > (there are no totally unique fields in either table, so it needs a
> > multi-join)
> >
> > I have 2 tables in the dataset already:
> > Membership
> > BENFT
> >
> > To get the Desc Field from the BENFT table, I need to relate 2 fields to
> > Membership
> > Membership.Benefit = BENFT.BNFT
> > Membership.Change = BENFT.BCHG
> >
> >
> > 'This is what I tried so far, but it's not working
> > daBenefits.Fill(DsBenefits1, "Membership")
> >
> > Dim relBenefit As New DataRelation("relBen",
> > DsBenefits1.Tables(0).Columns("Benefit"),
> > DsBenefits1.Tables(1).Columns("BNFT"))
> > Dim relBenChg As New DataRelation("relChg",
> > DsBenefits1.Tables(0).Columns("Change"),
> > DsBenefits1.Tables(1).Columns("BCHG"))
> >
> > DsBenefits1.Relations.Add(relBenefit)
> > DsBenefits1.Relations.Add(relBenChg)
> >
> >
> > daBenefitDesc.Fill(DsBenefits1, "BENFT")
> >
> >
> >
> > 'DGMembers is the DataGrid
> > DGBenefits.DataSource =
> > DsBenefits1.Tables("Membership").DefaultView
> > DGBenefits.DataBind()
> >
> > Thank you
> >
> > Note: Membership is actually a union that I have filled for each piece
> > into
> > the dsBenefits1
> >
> >
> >
>
>
>

HI

just try
SELECT Benefit as benefit, Type as type, Charge as Charge, MRN as mrn,
[GROUP] as gp, SGR as sgr, BENFT.BNFT_DESC AS Description
FROM Membership INNER JOIN BENFT ON (Charge = BENFT.BCHG) AND (Benefit =
BENFT.BNFT);

"jonefer" <(E-Mail Removed)> wrote in message
newsE000F81-B96B-4889-9F37-(E-Mail Removed)...
> Bill, please help me out. Something as simple as:
>
> SELECT Benefit, Type, Charge, MRN, [GROUP], SGR, BENFT.BNFT_DESC
> FROM Membership INNER JOIN BENFT ON (Charge = BENFT.BCHG) AND (Benefit =
> BENFT.BNFT);
>
> ..should STILL be simple in ADO.NET. Perhaps you think I am asking for
> something more difficult?
>
> It's pretty trivial in Access: pull two tables in, connect the two related
> fields:
>
> Yet, I've spent two days reading and looking at different posts and sites.
> If ADO.NET is really supposed to help make working with databases simpler,
> I
> haven't been convinced yet. I'm talking about adding one field from
> another
> table to a dataset.
>
> Could you or someone just give me the steps? Because I can't search
> anymore
> sites in google to find the answer to this question. I'll read more about
> it
> after I digest how to do something so simple.
>
>
> "W.G. Ryan - MVP" wrote:
>
>> Jonefer: Have you considered using DataSet.Merge? This would entail two
>> datasets (so you'd have to move/copy the datatable) but I think it'd give
>> you what you wanted.
>> "jonefer" <(E-Mail Removed)> wrote in message
>> news:855BAAC2-0C12-4FAE-8BE6-(E-Mail Removed)...
>> > So far the dataset just shows
>> > Type, Benefit, Change
>> >
>> > I want it to show
>> > Type, Benefit, Change, Description
>> >
>> > This should be a pretty elementary question - and I've spent 2 days on
>> > it
>> > already.
>> > I just want to add one field from another table into my dataset, should
>> > be
>> > easy, but maybe it's the way it is related that is making it so
>> > difficult
>> > (there are no totally unique fields in either table, so it needs a
>> > multi-join)
>> >
>> > I have 2 tables in the dataset already:
>> > Membership
>> > BENFT
>> >
>> > To get the Desc Field from the BENFT table, I need to relate 2 fields
>> > to
>> > Membership
>> > Membership.Benefit = BENFT.BNFT
>> > Membership.Change = BENFT.BCHG
>> >
>> >
>> > 'This is what I tried so far, but it's not working
>> > daBenefits.Fill(DsBenefits1, "Membership")
>> >
>> > Dim relBenefit As New DataRelation("relBen",
>> > DsBenefits1.Tables(0).Columns("Benefit"),
>> > DsBenefits1.Tables(1).Columns("BNFT"))
>> > Dim relBenChg As New DataRelation("relChg",
>> > DsBenefits1.Tables(0).Columns("Change"),
>> > DsBenefits1.Tables(1).Columns("BCHG"))
>> >
>> > DsBenefits1.Relations.Add(relBenefit)
>> > DsBenefits1.Relations.Add(relBenChg)
>> >
>> >
>> > daBenefitDesc.Fill(DsBenefits1, "BENFT")
>> >
>> >
>> >
>> > 'DGMembers is the DataGrid
>> > DGBenefits.DataSource =
>> > DsBenefits1.Tables("Membership").DefaultView
>> > DGBenefits.DataBind()
>> >
>> > Thank you
>> >
>> > Note: Membership is actually a union that I have filled for each piece
>> > into
>> > the dsBenefits1
>> >
>> >
>> >
>>
>>
>>

I went ahead and tried your suggestion and got the following error
"Join expression not supported"

because I'm trying to make this join AFTER filling a dataset with 9 pieces
of a union query. I'm working with an OleDB Jet 4.0 (access database)

where exactly did you want me to put that sql command?



"Fox" wrote:

> HI
>
> just try
> SELECT Benefit as benefit, Type as type, Charge as Charge, MRN as mrn,
> [GROUP] as gp, SGR as sgr, BENFT.BNFT_DESC AS Description
> FROM Membership INNER JOIN BENFT ON (Charge = BENFT.BCHG) AND (Benefit =
> BENFT.BNFT);
>
> "jonefer" <(E-Mail Removed)> wrote in message
> newsE000F81-B96B-4889-9F37-(E-Mail Removed)...
> > Bill, please help me out. Something as simple as:
> >
> > SELECT Benefit, Type, Charge, MRN, [GROUP], SGR, BENFT.BNFT_DESC
> > FROM Membership INNER JOIN BENFT ON (Charge = BENFT.BCHG) AND (Benefit =
> > BENFT.BNFT);
> >
> > ..should STILL be simple in ADO.NET. Perhaps you think I am asking for
> > something more difficult?
> >
> > It's pretty trivial in Access: pull two tables in, connect the two related
> > fields:
> >
> > Yet, I've spent two days reading and looking at different posts and sites.
> > If ADO.NET is really supposed to help make working with databases simpler,
> > I
> > haven't been convinced yet. I'm talking about adding one field from
> > another
> > table to a dataset.
> >
> > Could you or someone just give me the steps? Because I can't search
> > anymore
> > sites in google to find the answer to this question. I'll read more about
> > it
> > after I digest how to do something so simple.
> >
> >
> > "W.G. Ryan - MVP" wrote:
> >
> >> Jonefer: Have you considered using DataSet.Merge? This would entail two
> >> datasets (so you'd have to move/copy the datatable) but I think it'd give
> >> you what you wanted.
> >> "jonefer" <(E-Mail Removed)> wrote in message
> >> news:855BAAC2-0C12-4FAE-8BE6-(E-Mail Removed)...
> >> > So far the dataset just shows
> >> > Type, Benefit, Change
> >> >
> >> > I want it to show
> >> > Type, Benefit, Change, Description
> >> >
> >> > This should be a pretty elementary question - and I've spent 2 days on
> >> > it
> >> > already.
> >> > I just want to add one field from another table into my dataset, should
> >> > be
> >> > easy, but maybe it's the way it is related that is making it so
> >> > difficult
> >> > (there are no totally unique fields in either table, so it needs a
> >> > multi-join)
> >> >
> >> > I have 2 tables in the dataset already:
> >> > Membership
> >> > BENFT
> >> >
> >> > To get the Desc Field from the BENFT table, I need to relate 2 fields
> >> > to
> >> > Membership
> >> > Membership.Benefit = BENFT.BNFT
> >> > Membership.Change = BENFT.BCHG
> >> >
> >> >
> >> > 'This is what I tried so far, but it's not working
> >> > daBenefits.Fill(DsBenefits1, "Membership")
> >> >
> >> > Dim relBenefit As New DataRelation("relBen",
> >> > DsBenefits1.Tables(0).Columns("Benefit"),
> >> > DsBenefits1.Tables(1).Columns("BNFT"))
> >> > Dim relBenChg As New DataRelation("relChg",
> >> > DsBenefits1.Tables(0).Columns("Change"),
> >> > DsBenefits1.Tables(1).Columns("BCHG"))
> >> >
> >> > DsBenefits1.Relations.Add(relBenefit)
> >> > DsBenefits1.Relations.Add(relBenChg)
> >> >
> >> >
> >> > daBenefitDesc.Fill(DsBenefits1, "BENFT")
> >> >
> >> >
> >> >
> >> > 'DGMembers is the DataGrid
> >> > DGBenefits.DataSource =
> >> > DsBenefits1.Tables("Membership").DefaultView
> >> > DGBenefits.DataBind()
> >> >
> >> > Thank you
> >> >
> >> > Note: Membership is actually a union that I have filled for each piece
> >> > into
> >> > the dsBenefits1
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Jonefer,

Did I show you this (simple) sample already?

http://www.vb-tips.com/default.aspx?...3-8a9529b2b38b

I hope this helps,

Cor