TMyQuery with 2 tables - Insert problem

[Zsolt Csillag]

Hello,

My Query joins two tables.
Roughly the sql looks like this:

SELECT AlmostEveryFieldFromTable1, OneFieldFromTable2
FROM Table1

LEFT OUTER JOIN Table2 ON ( Table1.KeyField = Table2.KeyField)

The problem is this:

If the user inserts a new row in the Query, the Autoincrement field of Table1 does not update after post in the application because it consists of two tables.

But I need to insert manually the Table2 as well (two values: this autoinc value and an other number), and to do this I need the autoincrement field value of Table1.

I tried to use SqlInsert with SQLInsert.Add('SELECT @last := LAST_INSERT_ID();'); but in this way I cannot change the value of the second field because I cannot set any parameter to SqlInsert other than the normal fields.

So the summary is:
- if I try to insert using SQLInsert,I can set the autoinc but I cannot set the second parameter (Delphi variable )
- if I try to use the QueryAfterInsert event, I can set the Delphi variable (second parameter), but I cannot set the autoinc value

Is there somehow a solution to this?

Thank you

[Zsolt Csillag]

Hello,

One more thing I tried.
If I try the following Sql statement:

Insert into Table1 (AnyField) Values ('xxxx');

Insert into Table2(tr_termek_azon,tr_raktarhely_azon)
Values(LAST_INSERT_ID(),Table2.SecondField);

Then it will insert correctly the autoincrement primary field of Table1 in the first field of Table2 USING MySql editor

If I use the same code from Delphi with the Query described in my first message, it won't get the LAST_INSERT_ID() value, it will be empty.
(I tried to use the Insert into Table2(tr_termek_azon,tr_raktarhely_azon)
Values(LAST_INSERT_ID(),Table2.SecondField) in the Post event of the Table1)

Thank you in advance

[Antaeus]

I have tested this issue. It looks like records are inserted correctly into both tables. The only problem I see is that the current record is not refreshed correctly after insertion. You will need to manually carry out refreshing. MyDAC cannot perform refreshing with such complicated queries automatically.

[siberya]

Hello,

Is it fixing to above mention refresh problem in the lasest version MYDAC?

[ViktorV]

We have made a lot of changes and optimization in the latest MyDAC version in comparison to versions released in 2007. You can use a trial version of MyDAC available at: https://www.devart.com/mydac/download.html - in order to check automatic refresh in your particular case.