469,315 Members | 1,609 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

How to gei Last ID

When I write new record with INSERT INTO i need to get ID (Autonumber, key
of this table) from this, just written record. How to do that in ASP.NET (VB
or C#) and SQL Server?

Thanks
Jun 27 '08 #1
5 1261

select @@identity

or

select scope_identity()
"Marko" <ma*****@hotmail.comwrote in message
news:fu**********@sunce.iskon.hr...
When I write new record with INSERT INTO i need to get ID (Autonumber, key
of this table) from this, just written record. How to do that in ASP.NET
(VB or C#) and SQL Server?

Thanks

Jun 27 '08 #2
How are you doing the INSERTs? Basically, you just want to look at
SCOPE_IDENTITY() immediately after the INSERT; you could SELECT it, you
could RETURN it, or you could SET it into an OUT variable (I favor the
latter). In older versions of SQL-Server, @@IDENTITY is a fallback, but
suffers with triggers.

Marc
Jun 27 '08 #3
In general, this is easiest when you use stored procedures, although you can
batch commands with a semi-colon (;). I would not use @@IDENTITY, as you can
end up with the wrong value on a highly used system. SCOPE_IDENTITY() is
better.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

or just read it:
http://gregorybeamer.spaces.live.com/

*************************************************
| Think outside the box!
|
*************************************************
"Marko" <ma*****@hotmail.comwrote in message
news:fu**********@sunce.iskon.hr...
When I write new record with INSERT INTO i need to get ID (Autonumber, key
of this table) from this, just written record. How to do that in ASP.NET
(VB or C#) and SQL Server?

Thanks

Jun 27 '08 #4
I would not use @@IDENTITY, as you can
end up with the wrong value on a highly used system.
This is misleading; high usage doesn't impact @@IDENTITY; @@IDENTITY is
limited to the current spid, but problems arise if an INSERT trigger
does one-or-more INSERTs - as you get the last identity on the spid,
which might be frmo an audit table. SCOPE_IDENTITY() resolves this by
getting the last identity (on the spid) for the current context - i.e.
the INSERT you just performed.

High usage does, however, affect IDENT_CURRENT(<table name>) - but this
should not really be used in transactional code - just from maintenance
scripts etc.

Marc
Jun 27 '08 #5
Not trying to mislead, so thanks for the input. I have added that to my
knowledge base.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

or just read it:
http://gregorybeamer.spaces.live.com/

*************************************************
| Think outside the box!
|
*************************************************
"Marc Gravell" <ma**********@gmail.comwrote in message
news:%2***************@TK2MSFTNGP05.phx.gbl...
I would not use @@IDENTITY, as you can
end up with the wrong value on a highly used system.

This is misleading; high usage doesn't impact @@IDENTITY; @@IDENTITY is
limited to the current spid, but problems arise if an INSERT trigger does
one-or-more INSERTs - as you get the last identity on the spid, which
might be frmo an audit table. SCOPE_IDENTITY() resolves this by getting
the last identity (on the spid) for the current context - i.e. the INSERT
you just performed.

High usage does, however, affect IDENT_CURRENT(<table name>) - but this
should not really be used in transactional code - just from maintenance
scripts etc.

Marc

Jun 27 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Scott W Gifford | last post: by
6 posts views Thread by magix | last post: by
2 posts views Thread by Kevin Burton | last post: by
23 posts views Thread by Florian Lindner | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.