Hello
I have the following 3 tables
Create Table If Not Exists TaxiCentral
(TaxiCentralID SmallInt Not NULL Auto_Increment,
TaxiCentralName VarChar(200) Not NULL,
PassWord VarChar(200) Not NULL,
IP VarChar(200) Not NULL Default '0.0.0.0',
PortNo SmallInt Not NULL Default '8180',
Primary Key (TaxiCentralID),
Unique Index (TaxiCentralName),
Unique Index (IP, PortNo))
Engine = MyIsam;
Create Table If Not Exists FirmDep
(FirmDepID SmallInt Not NULL Auto_Increment,
TaxiCentralID SmallInt Not NULL,
DepName VarChar(200) Not NULL,
TaxiPrHour Integer Not NULL default 0,
Primary Key (FirmDepID),
Unique Index (TaxiCentralID, DepName))
Engine = MyIsam;
Create Table If Not Exists DepTown
(DepTownID MediumInt Not NULL Auto_Increment,
TownID SmallInt Not NULL,
FirmDepID SmallInt Not NULL,
NumOfTaxis MediumInt Not NULL,
OrderCount SmallInt Not NULL default 0,
TotalOrders Integer Not NULL default 0,
Primary Key (DepTownID),
Unique Index (TownID, FirmDepID))
Engine = MyIsam;
I use MyDac's TMyQuery to make GUI for the three tables above. And they are in Master/Detail relationship.
In one TMyQuery I have the following properties set:
SQL
Select FirmDepID, TaxiCentralID, DepName, TaxiPrHour,
IFNULL(Sum(NumOfTaxis) ,0) as NumOfTaxis,
IFNULL(Sum(TotalOrders),0) as TotalOrders
from FirmDep left outer join DepTown using (FirmDepID) group by FirmDepID
SQL Insert
insert into FirmDep (TaxiCentralID, DepName) values (:TaxiCentralID, :DepName)
SQL Update
update FirmDep set DepName= :DepName where FirmDepID =:FirmDepID
SQL Delete
delete from FirmDep where FirmDepID= :FirmDepID
SQL refresh
Select FirmDepID, TaxiCentralID, DepName, TaxiPrHour,
IFNULL(Sum(NumOfTaxis) ,0) as NumOfTaxis,
IFNULL(Sum(TotalOrders),0) as TotalOrders
from FirmDep left outer join DepTown using (FirmDepID)
where FirmDepID = :FirmDepID
group by FirmDepID
I have also option StrictUpdate set to true. and the updatingTable set to FirmDep.
The problem is that every time I issue an Insert (which will be followed by a refresh) I get an error message (EDataBaseError) saying that the: refresh failed. Found records 0.
I have debugged this and found that the parameter :FirmDepID of the refresh SQL is set to NULL rather than the new FirmDepID. If I turn of StrictUpdate, I will no longer get the error, but the records in the GUI (TDBGrid) will still not get updated correctly (cause :FirmDepID is set to NULL rather than correct value).
I have an almost identical situation with other Master/detail tables, but this problem does not occur, i.e. the primary key parameter is set to correct value and not NULL as we have here. The only difference in the other cases is that I donot calculated fileds (i.e. SUM) in the SQL property and also I have inner joins instead of left outer join. But can this really be the cause of the error ? and can I bypass this in any way ?
Kind Regards