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

[Simon Garton]

Yes, I have run into this, and have found references saying it's simply not possible, it's a limitation of ADO. I fiddled around with nasty routines like going to the last record after the insert and re-reading it - but that would fail concurrent inserts - and so I have ended up building and maintaining a table for my own sequences and doing them at the application level rather than the database level.

Would love to know if there is a better way ... 

Simon Garton
GIS/IT Consultant
email : simon at simongarton.co.nz 
mob : +64 21 227 4666
lat/long : 36º53'17" S, 174º46'50" E
www : http://www.simongarton.co.nz 
      http://www.treecapture.co.nz


-----Original Message-----
From: delphi-bounces at listserver.123.net.nz [mailto:delphi-bounces at listserver.123.net.nz] On Behalf Of mstokes at semantic.co.nz
Sent: Tuesday, 11 March 2008 12:29 p.m.
To: delphi at listserver.123.net.nz
Subject: [DUG] Retrieving the Last Inserted ID from MS SQL using ADO


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

_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: delphi at listserver.123.net.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-request at listserver.123.net.nz with Subject: unsubscribe