Board index » delphi » Two tables, one SQL query, one DBGrid?

Two tables, one SQL query, one DBGrid?

Hello,

Delphi 3.02
MS Access 95

I am obviously describing this problem very badly!

My query is:
I have a database with two tables 'Orders' and 'ItemLn' which
are linked by a common 'Order_Number' value.
I have a DataModule with two TQuery components 'QueryOrders'
and 'QueryItemLn' each has it's own DataSource which I have
called 'dsOrdersTbl' and 'dsItemLnTbl'

I have set up the DataSource property of the DBGrid which
is used to display the result of the SQL query to DataSource
'dsOrdersTbl' (Only one DataSource!?)

When I run the SQL query (shown below) only the fields belonging
to the 'Orders' table are returned. I would like the fields from
both the 'Orders' AND the 'ItemLn' table to be returned and
displayed on the same DBGrid.

How is this done?  Can it be Done?
I am new to Delphi and must be overlooking something.

Stephen Fowler
email: s.fow...@qub.ac.uk

  QueryOrders.Close;
  QueryOrders.Sql.Clear;

  { Load SQL query }
  QueryOrders.SQL.Add('SELECT Orders.Order_Number, Orders.Date,');
  QueryOrders.SQL.Add('ItemLn.Description, ItemLn.Cost');
  QueryOrders.SQL.Add('FROM Orders, ItemLn');
  QueryOrders.SQL.Add('WHERE Orders.Order_Number = ItemLn.Order_Number');

  { Add MS Access SQL query terminator }
  QueryOrders.SQL.Add(';');

  { Run SQL query }
  QueryOrders.Open;

 

Re:Two tables, one SQL query, one DBGrid?


Hi  Stephen,

I think you should correct the start of then sql statement like:

QueryOrders.SQL.Add('SELECT *,');
...

J. Shl

Quote
>>>>>>>>>>>>>>>>>> Ursprngliche Nachricht <<<<<<<<<<<<<<<<<<

Am 26.03.99, 10:59:00, schrieb s.fow...@qub.ac.uk zum Thema Two
tables, one SQL query, one DBGrid?:

Quote
> Hello,
> Delphi 3.02
> MS Access 95
> I am obviously describing this problem very badly!
> My query is:
> I have a database with two tables 'Orders' and 'ItemLn' which
> are linked by a common 'Order_Number' value.
> I have a DataModule with two TQuery components 'QueryOrders'
> and 'QueryItemLn' each has it's own DataSource which I have
> called 'dsOrdersTbl' and 'dsItemLnTbl'
> I have set up the DataSource property of the DBGrid which
> is used to display the result of the SQL query to DataSource
> 'dsOrdersTbl' (Only one DataSource!?)
> When I run the SQL query (shown below) only the fields belonging
> to the 'Orders' table are returned. I would like the fields from
> both the 'Orders' AND the 'ItemLn' table to be returned and
> displayed on the same DBGrid.
> How is this done?  Can it be Done?
> I am new to Delphi and must be overlooking something.
> Stephen Fowler
> email: s.fow...@qub.ac.uk
>   QueryOrders.Close;
>   QueryOrders.Sql.Clear;
>   { Load SQL query }
>   QueryOrders.SQL.Add('SELECT Orders.Order_Number, Orders.Date,');
>   QueryOrders.SQL.Add('ItemLn.Description, ItemLn.Cost');
>   QueryOrders.SQL.Add('FROM Orders, ItemLn');
>   QueryOrders.SQL.Add('WHERE Orders.Order_Number =

ItemLn.Order_Number');

- Show quoted text -

Quote
>   { Add MS Access SQL query terminator }
>   QueryOrders.SQL.Add(';');
>   { Run SQL query }
>   QueryOrders.Open;

Re:Two tables, one SQL query, one DBGrid?


On 26 Mar 1999 09:59:00 GMT, s.fow...@qub.ac.uk wrote:

Quote
>Delphi 3.02
>MS Access 95

>I am obviously describing this problem very badly!

>My query is:
>I have a database with two tables 'Orders' and 'ItemLn' which
>are linked by a common 'Order_Number' value.
>I have a DataModule with two TQuery components 'QueryOrders'
>and 'QueryItemLn' each has it's own DataSource which I have
>called 'dsOrdersTbl' and 'dsItemLnTbl'

>I have set up the DataSource property of the DBGrid which
>is used to display the result of the SQL query to DataSource
>'dsOrdersTbl' (Only one DataSource!?)

>When I run the SQL query (shown below) only the fields belonging
>to the 'Orders' table are returned. I would like the fields from
>both the 'Orders' AND the 'ItemLn' table to be returned and
>displayed on the same DBGrid.

>How is this done?  Can it be Done?
>I am new to Delphi and must be overlooking something.

>Stephen Fowler
>email: s.fow...@qub.ac.uk

>  QueryOrders.Close;
>  QueryOrders.Sql.Clear;

>  { Load SQL query }
>  QueryOrders.SQL.Add('SELECT Orders.Order_Number, Orders.Date,');
>  QueryOrders.SQL.Add('ItemLn.Description, ItemLn.Cost');
>  QueryOrders.SQL.Add('FROM Orders, ItemLn');
>  QueryOrders.SQL.Add('WHERE Orders.Order_Number = ItemLn.Order_Number');

>  { Add MS Access SQL query terminator }
>  QueryOrders.SQL.Add(';');

>  { Run SQL query }
>  QueryOrders.Open;

You seemed to indicate in an earlier post that you were using the Fields
Editor to define persistent field objects, something that can only be done
at design-time. But the above code that redefines a query can only be
executed at runtime and can have no effect on the design-time environment.
So far you are only presenting apples and oranges for comparison.

Given that the query redefinition source code you present is done at
run-time, you need to specify what the SQL statement is at design-time --
when you are defining the persistent field objects. If that query only
includes the Orders table, then only the columns from the Orders table will
be in the Fields Editor list and you would only be able to define field
objects for those columns at design-time. If you only have field objects
for the columns from the Orders table and then redefine the query to also
include the ItemLn table, you will still only see the columns from the
Orders table when the query is activated. This would be because there have
been no persistent field object defined for those new columns from the
ItemLn table.

Persistent field objects can be created either at design-time using the
Fields Editor or at runtime using the normal procedures for creating
objects. You might want to take a look at the Technical Information sheet
TI2790, "Creating & Deleting TFields at Runtime," for more information (and
source example) on this subject.

Your choices appear to be:

1. Do not use persistent field objects at all. Benefit: change the query
   and you do not need to worry above creating and destroying persistent
   field objects.

2. At runtime, destroy all existing persistent field objects associated
   with that TQuery and create new ones that reflect the columns returned
   by the new query.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                 "Like a camel, I can go without a drink for
Technical Publications         seven days--and have on several horrible
INPRISE Corporation            occasions."
http://www.borland.com/delphi                                 -- Herb Caen

Re:Two tables, one SQL query, one DBGrid?


On 26 Mar 1999 09:59:00 GMT, s.fow...@qub.ac.uk wrote:

Quote
>Hello,

>Delphi 3.02
>MS Access 95

>I am obviously describing this problem very badly!

You've obviously not found the time to read our answers...

Quote
>My query is:
>I have a database with two tables 'Orders' and 'ItemLn' which
>are linked by a common 'Order_Number' value.
>I have a DataModule with two TQuery components 'QueryOrders'
>and 'QueryItemLn' each has it's own DataSource which I have
>called 'dsOrdersTbl' and 'dsItemLnTbl'

This doesn't matter. You can have another 100 queries on that
datamodule.

Quote
>I have set up the DataSource property of the DBGrid which
>is used to display the result of the SQL query to DataSource
>'dsOrdersTbl' (Only one DataSource!?)

This is what counts.  The DBGrid will only show columns (all or some)
that exist in the linked dataset.

Quote
>When I run the SQL query (shown below) only the fields belonging
>to the 'Orders' table are returned. I would like the fields from
>both the 'Orders' AND the 'ItemLn' table to be returned and
>displayed on the same DBGrid.

>  QueryOrders.Close;
>  QueryOrders.Sql.Clear;

>  { Load SQL query }
>  QueryOrders.SQL.Add('SELECT Orders.Order_Number, Orders.Date,');
>  QueryOrders.SQL.Add('ItemLn.Description, ItemLn.Cost');
>  QueryOrders.SQL.Add('FROM Orders, ItemLn');
>  QueryOrders.SQL.Add('WHERE Orders.Order_Number = ItemLn.Order_Number');

>  { Add MS Access SQL query terminator }
>  QueryOrders.SQL.Add(';');

This query is ok.

You should check if you have any persistent fields defined.  I see you
create you SQL statement at runtime.  The resulting query will show
all the fields dinamically, only if you haven't defined any persistent
fields in the component at design-time.

Double click on the QueryOrders component and make sure you either
have no fields defined, or you have all those that interest you.

Regards,

--
Marco Rocci
MicroEra srl
Turin, Italy

Other Threads