[DUG] Retrieving the Last Inserted ID from MS SQL using ADO

[mstokes at semantic.co.nz]

Hello Everyone,

I am having a few issues trying to return the last inserted id after executing an insert statement using the TADOQuery component.

I can retrieve this value with ease by running the following SQL statement when using the SQL Query Analyzer which is part of the the SQL Server Enterprise Manager tool.


  function GetNewID : integer;
  begin
    with TADOQuery.Create(nil) do try
      Connection := dm.conFARST;
      SQL.Add('select scope_identity() as ''newid''');
      Open;
      Result := FieldByName('newid').AsInteger;
    finally
      free;
    end;
  end;

The problem that I am having is that when I execute this query within the ADO query component, it always returns the value of 0.
The issue seems to be with the Delphi 7 ADO components.


Has anyone come across this issue before?
Any feedback would be appreciated.


Cheers,

Mike Stokes