Getting back an identity field value after an insert SQL statement

Getting back an identity field value after an insert SQL statement

Post by Robert Dufou » Thu, 17 Oct 2002 03:35:55



Got some code doing an insert for a new record in a table, first field in
the table is ID which is the primary key, an int data type and is set to be
an identity  field with identity seed 1 and increment = 1.

After the insert statement has correctly completed, how do I get back the Id
for the record that my code has just created?
I know I could do a select statement getting the top 1 value of the ID back
but this does not guarantee that in a multi-user environment someone else
has not just added a record practically at same time as me and that I might
be getting back his record Id instead of my own newly created one. Chances
of this happening are slim but I want  to be sure to use a method that
prevents this from ever happening.

Thanks for any help.

Bob

 
 
 

Getting back an identity field value after an insert SQL statement

Post by Val Mazu » Thu, 17 Oct 2002 04:19:56


Hi,

Is it SQL Server? If yes, then in case of SQL Server 7 you could retrieve


in case of SQL Server 2000 use IDENTITY_SCOPE() function

SELECT IDENTITY_SCOPE() as NewID

Also check next KBs about working with identity fields from ADO

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q195910

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q233299

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q195224

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q221931

--
Val Mazur
Microsoft MVP


Quote:> Got some code doing an insert for a new record in a table, first field in
> the table is ID which is the primary key, an int data type and is set to
be
> an identity  field with identity seed 1 and increment = 1.

> After the insert statement has correctly completed, how do I get back the
Id
> for the record that my code has just created?
> I know I could do a select statement getting the top 1 value of the ID
back
> but this does not guarantee that in a multi-user environment someone else
> has not just added a record practically at same time as me and that I
might
> be getting back his record Id instead of my own newly created one. Chances
> of this happening are slim but I want  to be sure to use a method that
> prevents this from ever happening.

> Thanks for any help.

> Bob


 
 
 

1. Getting Back Identity column value on Insert using RDS

 Does anyone know of an easy way to get back the identity column value on an
insert using
RDS from a web page ?


to work with RDS. It always
returns NULL, may be because RDS drops the connection after each query ?

Sending the insert record to a DCOM object on the server and have that do
the insert and the return the identity value seems to be the only solution
to me. Is there anyway to avoid this extra step ?

Thanks in advance for any suggestions

Sundar B

2. moving database diagrams

3. Getting the value of an autoincrement or identity field during an sql insert

4. Professional Audit Expander v2.0

5. Exporting memo fields

6. Attempt to insert duplicate identity values in table (has identity and key fields)

7. Printing Problem

8. Identity -- Getting it back on Insert

9. Getting back identity after an insert via ADO?

10. Getting image into field (SQL 7) and getting it back out

11. Learning value of identity field on SQL Server 7 after ADO-based insert

12. Getting IDENTITY value BEFORE an insert(?!)