Michael Riley - AKA Gunny Michael Riley - AKA Gunny - 6 months ago 24
SQL Question

How to Return the ID Value When Inserting a Record Using ElevateDB

With SQL server you can simply return

@@Identity
to get the
ID
value of the last insert.

Is there a way to do this using ?

Answer

ElevateDB provides a LASTIDENTITY function which returns the last identity value assigned to the specified column in the specified table.

SELECT LASTIDENTITY('Customer', 'CustNo') AS LastCustNo


You can also obtain the same information using the INSERT statement.

EDBQuery1.SQL.Clear;
EDBQuery1.SQL.Add('INSERT INTO Table1 (ID, Text1)');
EDBQuery1.SQL.Add('VALUES(:ID, :Text1)');
EDBQuery1.Prepare;
EDBQuery1.ParamByName('Text1').AsString:='Some text';
EDBQuery1.ExecSQL;
ShowMessage(EDBQuery1.ParamByName('ID').AsString);

INSERT statements automatically set all parameters to IN/OUT so that any generated/computed column values (including IDENTITY columns) can be retrieved via the same parameters after the INSERT is executed.

The above example is from the Support Forum.