Devart Forums

Posted: **Sun 12 Feb 2012 07:35**

Delphi XE2.
TIBCQuery 4.1

I would be grateful if someone could help with this problem.
I am trying to get back the value of the Autoinc field after an insert.
This is the code I am using. It is just a stripped down version so I can trace what is happening.

Code: Select all

procedure TForm1.BtnTestIDClick(Sender: TObject);
Var NewID : LongWord;
begin
  With DM.Qry do begin
    Close;
    SQL.Clear;
    SQL.Add('INSERT INTO Stock(StkCode, StkDesc, StkTaxable)');
    SQL.Add('VALUES (') ;
    SQL.Add(QuotedStr('Stock Code') + ',');
    SQL.Add(QuotedStr('Stock Description') + ',');
    SQL.Add('1');
    SQL.Add(')');
    SQL.Add('RETURNING StkID');
    ExecSQL;
  end;
  NewID := DM.Qry.FieldByName('StkID').AsInteger;
end;

The Query executes OK, and a new record is inserted, but on the line NewID := DM.Qry.FieldByName('StkID).AsInteger), I get an error
"Qry Field StkID not found"

I have tried "Open" instead of "ExecSQL", but this did not work either.

Thanks
Colin

Posted: **Mon 13 Feb 2012 10:18**

I think you need to prefix the field name with 'RET_'. So, try RET_StkID as field name.

Posted: **Mon 13 Feb 2012 10:47**

You receive such error because fields are created only when you execute queries that return data (the SELECT statement). To solve the problem, you should work with parameters instead of fields. Here is an example:

Code: Select all

NewID := DM.Qry.ParamByName('RET_StkID').AsInteger;

Please note that you should add the 'RET_' string to the name of a parameter that is used in the RETURNING statement.

Posted: **Fri 17 Feb 2012 03:39**

Many thanks to all. I would not have figured that out for myself.

regards Colin

Posted: **Fri 17 Feb 2012 07:15**

Feel free to ask us if you have any other questions about IBDAC.

Devart Forums

Access Autoinc value on Insert

Access Autoinc value on Insert