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

[Leigh Wanstead]

I agree with John

I am using store procedure to insert record and select id

		SELECT @Id = (SELECT SCOPE_IDENTITY())

		SELECT @Id

Regards
Leigh
www.smootharm.com

-----Original Message-----
From: delphi-bounces at listserver.123.net.nz
[mailto:delphi-bounces at listserver.123.net.nz]On Behalf Of John Davys
Sent: Tuesday, March 11, 2008 2:08 PM
To: 'NZ Borland Developers Group - Delphi List'
Subject: RE: [DUG] Retrieving the Last Inserted ID from MS SQL using ADO


If you do a query to do the insert and then do another query to get the
result of the scope_insert() then these do not execute within the same sql
server scope (batch) i.e. the query doing the scope_identity() call does not
see the id created from the previous insert (see SQL Server Books Online for
the SCOPE_IDENTITY() function).

I suggest a better approach would be to use a stored procedure to do the
insert and then return the new id as an output parameter from the stored
proc.

Regards
John

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

_______________________________________________
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