mutiple tables in dataset and the dataadapter fill statement

9 May 2006 12:00 AM

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

9 May 2006 6:40 AM

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

Show quoteHide quote

"Cindy H" <nonn***@nowhere.com> wrote in message
news:ePKphuvcGHA.4932@TK2MSFTNGP03.phx.gbl...
> 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
>
>

9 May 2006 1:44 PM

Cindy H

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

Show quoteHide quote

"Liz" <liz@no-spam.org> wrote in message
news:XPW7g.206757$%_1.118847@fe01.news.easynews.com...
>
> 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" <nonn***@nowhere.com> wrote in message
> news:ePKphuvcGHA.4932@TK2MSFTNGP03.phx.gbl...
> > 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
> >
> >
>
>

9 May 2006 1:02 PM

Cindy H

Thanks, will give her a try.

Show quoteHide quote

"Cindy H" <nonn***@nowhere.com> wrote in message
news:ePKphuvcGHA.4932@TK2MSFTNGP03.phx.gbl...
> 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
>
>

26 May 2006 2:05 PM

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