ApplyUpdate + Multi-Table + Dbgrid

[abak]

Hello,

After editing a dbgrid,
IbcQuery.applyUpdates works fine for one Table.

I have a dbgrid displaying records from a multi table.
Select T1.id1, T2.id2, T3.id3, T1.F1, T2.F2, T3.F3
From myTable T1, myTable2 T2, myTable3 T3 ...

How can i do ApplyUpdates in a dbgrid with a multi-select as above ?
Thx

[ViktorV]

Such behavior of IBDAC is correct. If several tables participate in a query, IBDAC allows update data in one only table. To specify the name of the table to update data in, use the TIBCQuery.UpdatingTable property. If this property is not set, IBDAC uses the first table specified in SELECT to update it by default. See more details about this property in the IBDAC help: http://www.devart.com/ibdac/docs/Devart ... gTable.htm
To have a possibility to modify all data in your query, perform the following steps:
- for the TIBCQuery component call Fields Editor and add all the fields in it
- set the ReadOnly property to False for all the fields
- in TIBCQuery.SQLUpdate you should create a correct execute block including queries for updating all the tables taking part in the select query.
Example:

Code: Select all

  IBCQuery.SQL.Text := 'select emp.empno, emp.ename, emp.job, dept.deptno, dept.dname  from emp inner join dept on (dept.deptno = emp.deptno)';
  IBCQuery.SQLUpdate.Text := 
    'EXECUTE BLOCK (Old_EMPNO INTEGER = :Old_EMPNO, EMPNO INTEGER = :EMPNO, ENAME VARCHAR(255) = :ENAME, JOB VARCHAR(255)= :JOB, Old_DEPTNO INTEGER = :Old_DEPTNO, DEPTNO INTEGER = :DEPTNO, DNAME VARCHAR(255) = :DNAME) ' +
    'AS ' +
    'BEGIN ' +
    'UPDATE EMP '+
    'SET '+
      'EMPNO = :EMPNO, ENAME = :ENAME, JOB = :JOB '+
    'WHERE '+
      'EMPNO = :Old_EMPNO; '+
    'UPDATE DEPT '+
    'SET '+
      'DEPTNO = :DEPTNO, DNAME = :DNAME '+
    'WHERE '+
      'DEPTNO = :Old_DEPTNO; '+
    'END';

[abak]

Oh...very interesting !
Thx a lot ViktorV.

And congratulation for the new release 5.5.

[ViktorV]

Feel free to contact us if you have any further questions about IBDAC.

[abak]

Hello VictorV,
I just test it today and i get this error : "Update Failed. Found 2 records"
I tried many solutions but nothing !
Any Help
Thx.
___________
IBDAC 5.6.20
FireBird 2.5.5.26952
Delphi XE3 Pro
Win 7 Pro 64 bits

[ViktorV]

This error is generated if the number of records touched by an update query is not equal to 1. More details about the TIBCDataSetOptions.StrictUpdate property can be found in the IBDAC documentation: https://www.devart.com/ibdac/docs/?deva ... update.htm
You can set the StrictUpdate property to False to prevent generating of the mentioned error.

[abak]

Ah...
Thank you very much ViktorV.
Now it works fine
Wish all the best to devart team
and Thx for the 5.6.21 !

[ViktorV]

It is good to see that the problem has been solved. Feel free to contact us if you have any further questions about IBDAC.