How to connect a detail table

[pcerdaz]

Hello,

I want to navigate over CUSTOMERS table and show the
corresponding detail records of a ORDERS table.

I have a SelectSQL in the ORDERS table with a parameter that
must be taken from CUSTOMER table; to do this I defined
DataSource property from ORDERS with CUSTOMER.

Unfortunately the result is not good because the grid is
showing the first detail table record only and not all records
that apply.

Is there something wrong?

Thank you,
pcerdaz

[Wayne Niddery [TeamB]]

Post by pcerdaz
I want to navigate over CUSTOMERS table and show the
corresponding detail records of a ORDERS table.
I have a SelectSQL in the ORDERS table with a parameter that
must be taken from CUSTOMER table; to do this I defined
DataSource property from ORDERS with CUSTOMER.
Unfortunately the result is not good because the grid is
showing the first detail table record only and not all records
that apply.

Sounds like you hooked the datasets backwards. Customers is the master, so
the Datasource must be assigned to Customers. The Orders dataset must have
its MasterSource property assigned to that Datasource.

Note though that you really should not be using TIBTable. That component was
provided to ease transition from existing applications previously using
TTable. For new applications you should use the other datasets - most often
TIBDataset and TIBSQL, though TIBQuery can also be used.

[pcerdaz]

I'm already using TIBDataSet and not the TIBTable, but TIBDataSet doesn't have a design time MasterSource property. Because of this I put CUSTOMERS in Datasource property of ORDERS to ensure that my parameter is taking the correct value in SelectSQL property:

select * from ORDERS, CUSTOMERS
where ORDERS.IDCUSTOMER = CUSTOMERS.IDCUSTOMER
and CUSTOMERS.IDCUSTOMER = :IDCUSTOMER
order by ORDERS.IDORDER

Any idea?
Thank you,
pcerdaz

Post by Wayne Niddery [TeamB]

Post by pcerdaz
I want to navigate over CUSTOMERS table and show the
corresponding detail records of a ORDERS table.
I have a SelectSQL in the ORDERS table with a parameter that
must be taken from CUSTOMER table; to do this I defined
DataSource property from ORDERS with CUSTOMER.
Unfortunately the result is not good because the grid is
showing the first detail table record only and not all records
that apply.

Sounds like you hooked the datasets backwards. Customers is the master, so
the Datasource must be assigned to Customers. The Orders dataset must have
its MasterSource property assigned to that Datasource.
Note though that you really should not be using TIBTable. That component was
provided to ease transition from existing applications previously using
TTable. For new applications you should use the other datasets - most often
TIBDataset and TIBSQL, though TIBQuery can also be used.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"The moment the idea is admitted into society that property is not as
sacred as the laws of God and there is not a force of law and public
justice to protect it, anarchy and tyranny commence." - John Adams

[Wayne Niddery [TeamB]]

Post by pcerdaz
I'm already using TIBDataSet and not the TIBTable, but TIBDataSet
doesn't have a design time MasterSource property.

Use its *Datasource* property for this. Sorry for specifying "mastersource".

Post by pcerdaz
Because of this I
put CUSTOMERS in Datasource property of ORDERS to ensure that my
select * from ORDERS, CUSTOMERS
where ORDERS.IDCUSTOMER = CUSTOMERS.IDCUSTOMER
and CUSTOMERS.IDCUSTOMER = :IDCUSTOMER
order by ORDERS.IDORDER

If the IBDataset with the above query is linked to the datasoruce of another
IBDataset that is selecting Customer (and Customers table ID field is
"IDCUSTOMER" - in other words the name of the parameter must match the
master field name) then you should get the correct detail (order) records
for the currently selected customer record.

[pcerdaz]

I think this is another kind of problem because the Grid is showing four rows corresponding to four orders from a customer but only one of those rows has text on its fields; the other rows are activated but doesn’t have text on its fields, moreover I can navigate over these empty rows!!!

pcerdaz

Post by Wayne Niddery [TeamB]

Post by pcerdaz
I'm already using TIBDataSet and not the TIBTable, but TIBDataSet
doesn't have a design time MasterSource property.

Use its *Datasource* property for this. Sorry for specifying "mastersource".

Post by pcerdaz
Because of this I
put CUSTOMERS in Datasource property of ORDERS to ensure that my
select * from ORDERS, CUSTOMERS
where ORDERS.IDCUSTOMER = CUSTOMERS.IDCUSTOMER
and CUSTOMERS.IDCUSTOMER = :IDCUSTOMER
order by ORDERS.IDORDER

If the IBDataset with the above query is linked to the datasoruce of another
IBDataset that is selecting Customer (and Customers table ID field is
"IDCUSTOMER" - in other words the name of the parameter must match the
master field name) then you should get the correct detail (order) records
for the currently selected customer record.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
SpaceShipOne; GovernmentZero

[Wayne Niddery [TeamB]]

Post by pcerdaz
I think this is another kind of problem because the Grid is showing
four rows corresponding to four orders from a customer but only one
of those rows has text on its fields; the other rows are activated
but doesn’t have text on its fields, moreover I can navigate over
these empty rows!!!

Post by pcerdaz
select * from ORDERS, CUSTOMERS
where ORDERS.IDCUSTOMER = CUSTOMERS.IDCUSTOMER
and CUSTOMERS.IDCUSTOMER = :IDCUSTOMER
order by ORDERS.IDORDER

Since the first dataset is already selecting customer, the detail dataset
does not need to, it only needs to select from order - I'm assuming you only
need to show order fields in the that grid. Try changing this sql to:

select * from ORDERS
whereORDERS.IDCUSTOMER = :IDCUSTOMER
order by ORDERS.IDORDER

[pcerdaz]

Finally I found a piece of code that is producing this rare
problem; this piece of code make a calculation of a CalcField
of CUSTOMERS:
…
while(ORDERS->FieldByName("CodProducto")->AsInteger ==
CodProducto && ORDERS->Eof == false)
{
CodPrepar = ORDERS->FieldByName("CodPrepar")->AsInteger;
if(CodPrepar != 0 && CodPrepar != Null())
CostoProducto += QueryCost->FieldByName("Cost")->AsFloat;
ORDERS->Next(); // May be this line is the problem
}
CUSTOMERS->FieldByName("CostoProducto")->AsFloat =
CostoProducto;
…
The I don't understand why ORDERS is affected by this piece of code. In previous version (remember that I am migrating my application from Paradox to Interbase) this piece of code didn't affect the ORDERS table.

Regards,
pcerdaz

Post by Wayne Niddery [TeamB]

Post by pcerdaz
I think this is another kind of problem because the Grid is showing
four rows corresponding to four orders from a customer but only one
of those rows has text on its fields; the other rows are activated
but doesn’t have text on its fields, moreover I can navigate over
these empty rows!!!

Post by pcerdaz
select * from ORDERS, CUSTOMERS
where ORDERS.IDCUSTOMER = CUSTOMERS.IDCUSTOMER
and CUSTOMERS.IDCUSTOMER = :IDCUSTOMER
order by ORDERS.IDORDER

Since the first dataset is already selecting customer, the detail dataset
does not need to, it only needs to select from order - I'm assuming you only
select * from ORDERS
whereORDERS.IDCUSTOMER = :IDCUSTOMER
order by ORDERS.IDORDER
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.

[Wayne Niddery [TeamB]]

Post by pcerdaz
Finally I found a piece of code that is producing this rare
problem; this piece of code make a calculation of a CalcField
…
while(ORDERS->FieldByName("CodProducto")->AsInteger ==
CodProducto && ORDERS->Eof == false)
{
CodPrepar = ORDERS->FieldByName("CodPrepar")->AsInteger;
if(CodPrepar != 0 && CodPrepar != Null())
CostoProducto += QueryCost->FieldByName("Cost")->AsFloat;
ORDERS->Next(); // May be this line is the problem
}
CUSTOMERS->FieldByName("CostoProducto")->AsFloat =
CostoProducto;

While you could simplify this code by using a query to get the sum to be
assigned to CostoProducto, instead of manually looping through the orders,
there is nothing technically wrong with the above code. However, since this
dataset has data-aware controls linked to it, you will see those controls
loop through the orders (each being selected in a dbgrid) every time this
event is fired.

Post by pcerdaz
The I don't understand why ORDERS is affected by this piece of code.

It certainly should not be, and cannot be causing rows in the grid to be
blank. Something else is happening.

Did you simplify the Orders query as I suggested? Did that make any
difference?

Do you have any code in any of the DBGrid events - particularly is there any
in the drawcell events?

[pcerdaz]

The use of a Query replacing the piece of code did the
solution of the problem. I have some code in a DBGrid event
but it works well.
¡Thank you!

Post by Wayne Niddery [TeamB]

Post by pcerdaz
Finally I found a piece of code that is producing this rare
problem; this piece of code make a calculation of a CalcField
…
while(ORDERS->FieldByName("CodProducto")->AsInteger ==
CodProducto && ORDERS->Eof == false)
{
CodPrepar = ORDERS->FieldByName("CodPrepar")->AsInteger;
if(CodPrepar != 0 && CodPrepar != Null())
CostoProducto += QueryCost->FieldByName("Cost")->AsFloat;
ORDERS->Next(); // May be this line is the problem
}
CUSTOMERS->FieldByName("CostoProducto")->AsFloat =
CostoProducto;

While you could simplify this code by using a query to get the sum to be
assigned to CostoProducto, instead of manually looping through the orders,
there is nothing technically wrong with the above code. However, since this
dataset has data-aware controls linked to it, you will see those controls
loop through the orders (each being selected in a dbgrid) every time this
event is fired.

Post by pcerdaz
The I don't understand why ORDERS is affected by this piece of code.

It certainly should not be, and cannot be causing rows in the grid to be
blank. Something else is happening.
Did you simplify the Orders query as I suggested? Did that make any
difference?
Do you have any code in any of the DBGrid events - particularly is there any
in the drawcell events?
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"In a tornado, even turkeys can fly." - unknown