Get Last Autoincrement ID

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 & "')"
            cmd.ExecuteNonQuery()
LVL 6
robbeAsked:
Who is Participating?
 
jayrodConnect With a Mentor Commented:
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
0
 
jayrodCommented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
plqCommented:
@@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
0
 
plqCommented:
Agreed
0
 
robbeAuthor Commented:
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 !
0
 
Douglas911Commented:
Here is what I did using VB.net 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

Douglas911
0
 
jayrodCommented:
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.


i.e.


select cast(scope_identity() as int)
0
 
robbeAuthor Commented:
Thx for the side note !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.