mutiple tables in dataset and the dataadapter fill statement

[Cindy H]

Hi

I'm using an access database and vb.net

I have been using the following type of function to get data from one table.

This is working great -

Public Shared Function GetAllLotsForSale() As DataSet

Dim dsProperties As New DataSet

Dim cmdProperties As New OleDbCommand

Dim sSelect As String = "SELECT Address, City, Status, Price, Description,
LotSize, Display, PathToImage, PathToTour FROM LotsForSale WHERE Display =
'y' ORDER BY Address"

cmdProperties.CommandText = sSelect

cmdProperties.Connection = MyConnection()

Dim daProperties As New OleDbDataAdapter

daProperties.SelectCommand = cmdProperties

daProperties.Fill(dsProperties, "LotsForSale")

Return dsProperties

End Function

Now, I would like to do a select with more then one table for example:

"Select customer,customerID, order.Orderid from Order, Customer where
customer.customerID = order.customerID.

The problem is I don't know how to handle this.

The dataadapter fill statement only wants one table name.

Does any one know how I should do this?

Thanks,

CindyH

 

[Liz]

fill the dataset as many time as you require, one fill after the next, in
sequence ... and then create relations between the datatables as needed ...
unless you wanted to create a DataSet with one table based on a JOIN, in
which case just adjust your SELECT statement ... as far as the DS is
concerned, it's one table ... with one name

 

[Cindy H]

Thanks, will give her a try.

 

[Cindy H]

Using the one table with the join worked nice - thanks a lot.

 

[jmbledsoe]

I've run into this problem of filling multiple tables (and sending
updates back to the database) a whole lot of times. I've been using a
tool called the DataSet Toolkit to manage fills and updates of the
tables in my DataSet. It takes care of a lot of the details of
organizing the operations so that you don't have to. Check it out and
let me know if it works for you.

http://www.hydrussoftware.com

John B.
http://johnsbraindump.blogspot.com