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

[Sean Cross]

If you are getting id values from a table, you could use the high/low algorithm.  Basically you get the high value from the database and generate the low value (0 -99) in your app.  When your low value gets to 100, you retrieve another high value.  The two values are combined result:= highVal * 100 + lowVal.  This is what I use now for my id values.

To get the high value, I have a table with a guid and an identity field.  I add a new row with a known guid and then select that row.  Every x attempts, I clear the table.

But it might be easier to use one of (from http://www.sqlteam.com/article/alternatives-to-identity-in-sql-server-2000):

SELECT @@IDENTITY
This is everyone's favorite function, unchanged from earlier versions of SQL Server. It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.

SELECT IDENT_CURRENT('tablename')
This new function returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

Regards

Sean Cross
IT Systems Development Manager

Catalyst Risk Management
PO Box 230
Napier 4140
DDI: 06-8340362
Mobile: 021270 3466

Visit us at http://www.catalystrisk.co.nz

Offices in Auckland, Napier, Wellington & Christchurch

Disclaimer:
"The information contained in this document is confidential to the addressee(s) and may be legally privileged. Any view or opinions expressed are those of the author and may not be those of Catalyst Risk Management. No guarantee or representation is made that this communication is free of errors, viruses or interference. If you have received this e-mail message in error please delete it and notify me. Thank you."



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