Data-control properties and events
lazarus / 16-Databases

----------------------------------------------------------------------
SteveF · 2010-11-15 15:33

As a newbie migrating over from MS Access & VB6, I’ve got some
questions about properties, settings, and use of data controls.

The scenario is a fairly typical parent/child relationship between two
tables, like Orders being in one table and OrderItems being in
another.  I want to display the Order data in individual controls on a
form, and the OrderItems in a DBgrid on the same form, but only show
the matching OrderItems for the Order being displayed.

In Access, there are properties that can be set to automagically link
its DBgrid equivalent to the parent data so that whenever the parent
data changed, the DBgrid data changed with it.  I’ve looked at the
properties for the DBgrid and the other controls that link the grid to
its data table, but don’t see anything that will do this.  Am I
correct?  If not, please let me know how to get this working.

VB6 does not support an automatic link; the data for the grid must be
explicitly filtered via SQL each time the user moves to a different
master record.  If I use a DBnavigator to move around the parent
records, which event should I use to do this filtering?

----------------------------------------------------------------------
José Mejuto · 2010-11-15 17:05

Quote from: SteveF on November 15, 2010, 04:33:04 pm[...]
The scenario is a fairly typical parent/child relationship between two
tables, like Orders being in one table and OrderItems being in
another.  I want to display the Order data in individual controls on a
form, and the OrderItems in a DBgrid on the same form, but only show
the matching OrderItems for the Order being displayed.
[...]

Hello,

Simplified example:

Code: [Select]QueryOrders.SQL.Text:='Select IDORDER,DATEORDER from
TORDERS';
QueryItems.SQL.Text:='SELECT IDITEM,QUANTITY form TITEMS where
TITEMS.IDORDER=:IDORDER';

You must link those 2 TSQLQuery to 2 DataSources and in QueryItems
link the DataSource property with the DataSource associated to the
QueryOrders.

In other words:

QueryOrders.DataBase:=WhateverDB.
DataSourceQueryOrders.DataSet:=QueryOrders;

QueryItems.DataBase:=WhateverDB;
DataSourceQueryItems.DataSet:=QueryItems;
QueryItems.DataSource:=DataSourceQueryOrders;

----------------------------------------------------------------------
SteveF · 2010-11-15 19:20

OK, this is more-or-less what I expected -- the Items SQL must be
explicitly refreshed with the relevant OrderID whenever the OrderID
changes.

So that returns me to my second question: if I have a DBnavigator
attached to QueryOrders, which Event should I use to refresh the Items
SQL?

TIA,
Steve

----------------------------------------------------------------------
Zoran · 2010-11-16 05:56

Quote from: SteveF on November 15, 2010, 08:20:39 pmOK, this is more-
or-less what I expected -- the Items SQL must be explicitly refreshed
with the relevant OrderID whenever the OrderID changes.

So that returns me to my second question: if I have a DBnavigator
attached to QueryOrders, which Event should I use to refresh the Items
SQL?

TIA,
Steve


No, you got it wrong. All you have to do is set the SQL and the
properties as shown above.
Then you do NOT have to take care about the parameters -- notice the
last line -- you set the detail query's DataSource property -- this is
all, this property is used only to tell the query that it has a
master. Just try it. Just set everything literally as in JoshyFun's
post and try.

See what confuses you -- normally a DataSource is tied to a query, but
not the other way -- the query's DataSource property is not used. The
query's DataSource property is used only to tell that this query has
its master, you than set it to a DataSource which is tied to another
query component -- its master.

Another aproach is to manually substitude the parameters in detail
query. In that case you do NOT use detail query's DataSource property.
Then you could use the master query's AfterScroll event to substitude
the query parameters.

----------------------------------------------------------------------
SteveF · 2010-11-18 13:47

OK,  I’m not really following this setup.  Instead of doing the setup
in code, I want to set the properties of the components in the Object
Inspector.  Assume the following component names:

TSQL Connection: ConnectOrders
TSQLTransaction: TransOrders
TSQLQuery for orders: QueryOrders
TSQLQuery for order items: QueryItems

TDatasource for orders: DSorders   linked to QueryOrders
TDatasource for items: DSitems   linked to QueryItems

And, the visible controls:
TDBGrid for orders:      GridOrders   linked to DSorders   
TDBGrid for items:      GridItems   linked to DSitems   

How should the properties for these components be set to achieve the
automatic master-detail link?

On a related topic, how and where did you learn these techniques? 
Where else can I access more detailed information about all this?  For
example, in the SQL string above:
'SELECT IDITEM,QUANTITY form TITEMS where TITEMS.IDORDER=:IDORDER';

What is the significance of the colon in front of the final IDORDER,
and why does putting it there make it work?

Steve

----------------------------------------------------------------------
Zoran · 2010-11-19 08:29

Of course you can set all this in Object Inspector.
First, set ConnectOrders connection so that it can connect to
database.

Then set in Object Inspector:

ConnectOrders.Transaction := TransOrders;

The first query (will be master) you can set normally:
QueryOrders.SQL.Text:='Select IDORDER,DATEORDER from TORDERS';
QueryOrders.DataBase := ConnectOrders;
DSOrders.DataSet := QueryOrders;
GridOrders.DataSource := DSOrders;

Now set the QueryItems. The colon means that the query is parametrized
-- you can supply and change the parameter "IDORDER" in runtime.
Parametrized queries are not used only for what we are doing now, but
in this automatic master-detail relationship, Lazarus automatically
supplies the parameter with the field of same name from master query.
QueryItems.SQL.Text:='SELECT IDITEM,QUANTITY form TITEMS where
TITEMS.IDORDER=:IDORDER';

QueryItems.DataBase := ConnectOrders;

The following property is telling that QueryItems has its Master, and
parameter "idorder" is automatically supplied with the field of the
same name from master dataset (for that purpose we could not name the
parameter differently - that is how lazarus knows with which field
from master to supply this parameter):
QueryItems.DataSource := DSOrders;

DSItems.DataSet := QueryItems;
GridItems.DataSource := DSItems;

That's all.

----------------------------------------------------------------------
SteveF · 2010-11-22 13:51

Zoran,

Thanks for your assistance so far.  I see I need to learn more about
parameterized queries!

Some progress has been made.  I set the properties as you outlined; I
at least am not getting any compile errors.  After I set up the Items
DataSource link to DSOrders, I can even see that the Items data grid
now only displays the relevant items for the first order in the master
table.

However, when I compile & run, only the master rows are displayed. 
The order items grid remains blank no matter which master record is
displayed.  What’s needed now to display the order items?

Thanks again,
Steve