Can 1 dataadapter connection to 2 or more data table

[fung1223]

Dear all,
I have one datasource and 5 tables in it. No, I am using a very stupid method to access the table

Dim connstr As String = "DSN=TEST"
Dim cn As New OdbcConnection(connstr)
Dim DA1 As New OdbcDataAdapter("SELECT * FROM A",CN)
Dim DA2 As New OdbcDataAdapter("SELECT * FROM B",CN)
Dim DA3 As New OdbcDataAdapter("SELECT * FROM C", cn)
Dim DA4 As New OdbcDataAdapter("SELECT * FROM D", cn)
Dim DA5 As New OdbcDataAdapter("SELECT * FROM E", cn)

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

cn.Open()
DA1.Fill(ds, "A")
DA2.Fill(ds, "B")
DA3.Fill(ds, "C")
DA4.Fill(ds, "D")
DA5.Fill(ds, "E")

Is there any method to access mulit table in one data adapter.
Thank you for all your help!!

[Iceman5]

When you create a DataAdapter you can chooce from which tables you want it to work with.
First do a connection to the database with the 5tables in it and then just drag a DataAdapter to your form and do the settings.
But then the tables should have something same so they don´t display dubletts.
If there is different information in the tables I don´t think there is another faster way.

[RichardAtherton]

as far as I am aware, providing you are using the same data access for all of the tables you can add as many tables as you like as in
dim ds1 as new dataset
dim ds2 as new dataset
Dim da1 As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
da1.TableMappings.Add("Table", "Table1")
da1.TableMappings.Add("Table", "Table2")

etc.........

then
da1.fill(ds1,"Table1")
da1.fill(ds2,"Table1")

etc............

[fung1223]

Thank you for your reply.
But there is still a problem. Where I use your method, it required me to supply a selectcommand(e.g SELECT * FROM .....) but I have mulit table in the data adapter. How to write a selectcommand with multi table? Thank you.

[fung1223]

When I write a code like this:

with da1
.TableMappings.Add("PRODUCT", "PRODUCT")
.TableMappings.Add("TOLE", "TOLE")

.SelectCommand = New OdbcCommand("SELECT * FROM PRODUCT ORDER BY CODE", cn)
.SelectCommand = New OdbcCommand("SELECT * FROM TOLE ORDER BY CODE", cn)

.Fill(ds, "PRODUCT")
.Fill(ds, "TOLE")

end with

But when I run it, it will use the TOLE rather than product, that mean can not use 2 table concurrently.

[RichardAtherton]

When I write a code like this:

with da1
.TableMappings.Add("PRODUCT", "PRODUCT")
.TableMappings.Add("TOLE", "TOLE")

.SelectCommand = New OdbcCommand("SELECT * FROM PRODUCT ORDER BY CODE", cn)
.Fill(ds, "PRODUCT")

.SelectCommand = New OdbcCommand("SELECT * FROM TOLE ORDER BY CODE", cn)

.Fill(ds, "TOLE")

end with

try this, I think what yoou have done is overwrite one select command with another, remember now that you are using one datadapter, it can only execute the last known select command which is why only TOLE gets filled and PRODUCT does not

[fung1223]

YEAH! I MAKE IT!!
Thank you very much!!
Really thank you!