We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Get Last Autoincrement ID

robbe
robbe asked
on
Medium Priority
1,493 Views
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 & "')"
            cmd.ExecuteNonQuery()
Comment
Watch Question

EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:

Commented:
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
plq

Commented:
@@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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
plq

Commented:
Agreed

Author

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

Commented:
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)

Author

Commented:
Thx for the side note !
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.