Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get Last Autoincrement ID

Posted on 2004-04-08
9
Medium Priority
?
1,432 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()
0
Comment
Question by:robbe
[X]
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
9 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 10784302
0
 
LVL 3

Expert Comment

by:jayrod
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
0
 
LVL 8

Expert Comment

by:plq
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
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 3

Accepted Solution

by:
jayrod earned 500 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
0
 
LVL 8

Expert Comment

by:plq
ID: 10784495
Agreed
0
 
LVL 6

Author Comment

by:robbe
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 !
0
 

Expert Comment

by:Douglas911
ID: 11597372
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
 
LVL 3

Expert Comment

by:jayrod
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.


i.e.


select cast(scope_identity() as int)
0
 
LVL 6

Author Comment

by:robbe
ID: 11605815
Thx for the side note !
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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