roBeforeEdit bug

[pedja2]

Hello,

Create tables:

CREATE TABLE `table1` (
`Code` tinyint(4) NOT NULL default '0',
`Description` char(50) default NULL,
PRIMARY KEY (`Code`)
) ;

INSERT INTO `table1` (`Code`, `Description`) VALUES
(1,'Desc 1');

CREATE TABLE `table2` (
`Code` tinyint(4) NOT NULL default '0',
`Description` char(50) default NULL,
PRIMARY KEY (`Code`)
);

INSERT INTO `table2` (`Code`, `Description`) VALUES
(1,'Desc 2');


Put as SQL in Myquery:
SELECT t1.Code,Count(t2.Code) AS Totaal FROM table1 t1
LEFT JOIN table2 AS t2 ON t1.Code=t2.Code
GROUP BY t1.Code

Set roBeforeEdit:=True;

After calling Myquery.Edit you get error :
#42S22Unknown column 'Totaal' in 'field list'

Regards,
Pedja

[Dimon]

This problem is connected with executing the RefreshRecord method.
To solve the problem you should set the TMyQuery.SQLRefresh property to the following SQL query:

Code: Select all

SELECT t1.Code,Count(t2.Code) AS Totaal FROM table1 t1 
LEFT JOIN table2 AS t2 ON t1.Code=t2.Code 
where t1.Code = :code
GROUP BY t1.Code