Solved

Get Last Autoincrement ID

Posted on 2004-04-08
9
1,414 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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 42

Expert Comment

by:EugeneZ
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Accepted Solution

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Activity Monitor detail 2 28
SQL query and VBA 5 46
Requesting help with creating an SQL query to select similar records 4 15
MS SQL GROUP BY 5 11
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

807 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