sqldelphielevatedb

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 ?

Solution

  • 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.