Get Last Autoincrement ID

Posted on 2004-04-08
Last Modified: 2008-10-08
Does anyone knows how i get the last PersoonID (auto increment identifier) from this insert in MSSQL 2000 without using the max() function ?

My vb.NET code is:

            cmd.CommandText = "INSERT INTO tblPersoon (Voornaam, Familienaam, Straat, Straatnummer, PostcodeID, Geboortedatum, Geslacht) VALUES('" & txtVoornaam.Text & "','" & txtFamilienaam.Text & "','" & txtStraat.Text & "'," & txtStraatnummer.Text & "," & PostcodeID & ",convert(Datetime, '" & Format(txtGeboortedatum.Text, "Short Date") & "', 103),'" & txtGeslacht.Text & "')"
Question by:robbe
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
LVL 43

Expert Comment

by:Eugene Z
ID: 10784302

Expert Comment

ID: 10784311
if you're using sql 2k... I would suggest creating this as a stored procedure and then returning it as scope_identity()

if you execute a select @@identity after executing the above you'll possibly get some off values as @@identity referes to any identity value accross the database.

The best model now a days for .NET is to put all queries into an SP and only give your application executeable rights on the sp's this limits sql injection and other flaws.

also this puts all of the processing on the database server which should be faster and more buff than the web/application server

Expert Comment

ID: 10784377
@@identity is specific to a connection, so you'll only get a problem if you're connection pooling or using component services for your vb dll. It does not refer across a database as jayrod suggests (sorry)

So something like

should be ok (sorry about the dodgy programming)

            cmd.CommandText = "INSERT INTO tblPersoon (Voornaam, Familienaam, Straat, Straatnummer, PostcodeID, Geboortedatum, Geslacht) VALUES('" & txtVoornaam.Text & "','" & txtFamilienaam.Text & "','" & txtStraat.Text & "'," & txtStraatnummer.Text & "," & PostcodeID & ",convert(Datetime, '" & Format(txtGeboortedatum.Text, "Short Date") & "', 103),'" & txtGeslacht.Text & "') ; select @@identity"
            set rs = cmd.ExecuteQuery()
 msgbox rs.fields(0).value
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


Accepted Solution

jayrod earned 125 total points
ID: 10784413
hmm didn't think that... from the BOL

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

using scope_identity() is a better fit in any case

Expert Comment

ID: 10784495

Author Comment

ID: 10784605
Thanks all for the quick reply !

I've awarded the points to jayrod becouse he gave a realy clear explination of the functions

Also thx for all the other suggestions !

Expert Comment

ID: 11597372
Here is what I did using and MSDE (MSSQL)

'vstr is a string
'cm is a SQL Command
'I do this just after the save

  vStr = "Select @@Identity as NewID"
  cm.CommandText = vStr
  vStr = cm.ExecuteScalar


Expert Comment

ID: 11601926
On a side note I've found that scope_identity() does not return an int :P  funky isn't it.

you have to cast it as int when returning it.


select cast(scope_identity() as int)

Author Comment

ID: 11605815
Thx for the side note !

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question