problems with multiple related tables on a datagrid

[bizjosh]

my datagrid displays a dataset with no problem when it has
2 tables, however when i add more related tables, it doesnt
display any values at all, any idea?

i'm using the vs.net 2003 data adapter to add the tables.
after i added the tables, they are displayed out with
relationships shown. then i will select the fields to display.
then i will fill my dataset and display it using dataview on
my datagrid.

 

[levyuk]

show us the code, and also the sql

 

[bizjosh]

codes

Private Sub frmResults_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'da1 is a data adapter that has 2 tables: tb1 & tb2
'i used vs.net to drag the oledbdataadapter to select fields using sql query builder
'the datagrid will show eg: home_name, person_id, person_name, person_age, person_job
da1.Fill(ds)

dataview = ds.Tables("tb1").DefaultView

'dg is datagrid with dataview as DataSource
dg.Datasource = dataview

Table: Fields
------------
tb1: home_id, home_name, home_type
tb2: person_id, person_name, person_age, person_job
tb3: company_id, person_id
tb4: company_id, company name

Problem: The below will display: home_name, person_id, person_name, person_age, person_job
field records with no problem. However when i re-create da1 to input tb3 & tb4 and select
the company_name field to show on the datagrid as well, it doesnt display any records. The
relationships are created in the MS access already and when i used data adapter query builder,
the relationships are there as well, am i missing any steps?

any advise will be appreciated!

 

[levyuk]

ok it would be nice if you showed the sql because from what i can see that looks fine, it is simply a matter of changing the sql.Try something like this

SELECT tb1.home_name, tb2.person_id, tb2.person_name, tb2.person_age, tb2.person_job, tb4.company_name
FROM tb1, tb2,tb3,tb4
WHERE tb2.person_id = tb3.person_id AND tb3.companyid = tb4.compandy_id

That should select the correct fields but it's hard to test without me making the tables myself. Also I noticed there is no link between the tb1 and tb2. Maybe you should put person_id into tb1 as a foreign field.

 

[bizjosh]

how do i put person_id into tb1 as a foreign field?

ok after i use the select statement in my data adapter,
i run 'da.fill(ds)'

next how do i put input this dataset 'ds' into my datagrid