Combining fields from different tables into 1 dataset

G

Guest

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
 
W

W.G. Ryan - MVP

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.
 
G

Guest

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.
 
F

Fox

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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.
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
Click to expand...
Click to expand...
Click to expand...
 
Top