Re: Selecting Data

From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 06/03/04 

Date: Thu, 3 Jun 2004 18:03:47 -0400

That's actually a Join, just doesn't use the Join Syntax. Yep, you'll need
to do two seperate queries. Remember that the dataset doesn't care where it
got its data from. One table could be a .csv file another an excel file and
possibly another child table that didn't come from anywhere, that you built
programmatically. As such, it's only concerned with Local data, the data in
the Datatables. The adapter's job is to move data around for you in between
places.. so you could have one adapter for instance move your data from a
Oracle DB to a DataSet, then another move that data from the dataset to an
excel ***, SQL Server db or back to the oracle db. The Adapter moves
everything. So you'll need another proc or select statement.

Did you build the adapter yourself? Normally if you use the Configuration
wizard, it will warn you of these types of issues..nothing that does
autogeneration can populate and update if a Join is in effect - it can
populate but not update. in this instance, the other table in the join is
being rejected. As such you need two adapters, or you'll need to change
your adapters select command each time. Too much work to be worth it. You
can roll your own logic that might handle updates but it's not worth it.
Moreoever, you won't pull over redundant data if you use a relation like you
would if you used the join. Perfomance wise the two selects will probably
be faster all around.

HTH,

Bill

HTH,

Bill 

-- 
W.G. Ryan MVP Windows - Embedded
http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
"eric" <eriic@spam.com> wrote in message
news:OaSwSlaSEHA.3768@TK2MSFTNGP11.phx.gbl...
> William,
>
> No I am not using "join" in my query.  My query is similar to this:
>
> CREATE PROCEDURE GetOrders
>
>  @OrderID varchar(10)
>
>
>
> AS
>
> SELECT *
>
> FROM Orders O, OrderDetails Od
>
> WHERE O.OrderID = @OrderID AND Od.OrderID = O.OrderID
>
> GO
>
>
>
> I am trying to load it into a typed dataset with tables: Order and Order
> Details with a data relation using OrderID.  I thought that the data
> relation would take care of the mapping of data.  So, do I need to do two
> seperate queries to fill each table or can I use one query like above to
> load data into both tables?
>
>
>
> Thanks
>
> "William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
> news:uoDMnCaSEHA.1128@TK2MSFTNGP11.phx.gbl...
> > Does your Select Statement have the word JOIN in it?  If so, this is the
> > problem.  The dataset is rejecting what doesn't fit.  You need to do two
> > separate queries filling the parent table with its adapater and the
child
> > with its adapter.  If you have a typed dataset with the Relation
defined,
> > then that should be it b/c the DataRelation is already defined.
> >
> > In general, avoid SQL Statements with the word JOIN in them in ADO.NET
> > unless you are positive you want to do otherwise.
> >
> > -- 
> >
> > W.G. Ryan, eMVP
> >
> > http://forums.devbuzz.com/
> > http://www.knowdotnet.com/williamryan.html
> > http://www.msmvps.com/WilliamRyan/
> > http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
> > "eric" <eriic@spam.com> wrote in message
> > news:%23dyfT4ZSEHA.1732@TK2MSFTNGP09.phx.gbl...
> > > I am currently struggling working with data in multiple tables.  Is it
> > > better to have a single join based query versus seperate queries?  I
am
> > > trying to fill a typed dataset with two tables with a one to many
> > > relationship which is similiar to Order-Order Details relationship.
> When
> > I
> > > pass my dataset to be filled in my data access layer using a single
> joing
> > > based query it only loads the data in the parent table in the dataset
> and
> > > not the order details table (child).  When I display it in a datagrid
it
> > > shows the data in the order details table however only with the order
> > table
> > > not in the order details table.  What am I doing wrong?
> > >
> > > Thanks
> > >
> > >
> >
> >
>
>