Devart Forums

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

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

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.

Hello,

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

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.