Access Autoinc value on Insert

[softsmith]

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

[tsteinmaurer]

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

[AndreyZ]

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.

[softsmith]

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

regards Colin

[AndreyZ]

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