Solved

Is returning the ID of an inserted record (VB.NET/SQL Server 2005) always correct?

Posted on 2011-02-17
5
637 Views
Last Modified: 2012-05-11
Hi all,

I have a piece of code that inserts a record into a database table that has an auto ID field and then returns the ID of the record:

            Dim objDB As New Database

            Dim objTemp As Object
            objTemp = objDB.GetScalar("INSERT INTO tbl_person(personname,persondob) VALUES('James Bond','01-Jan-1960') SELECT @@IDENTITY FROM tbl_person")

            If Not objTemp Is Nothing Then

                Response.Write("ID = " & objTemp.ToString)

            Else

                Response.Write("Error!")

            End If

When the code is executed it returns the correct value.

What I would like to know is, if many users are using the same function, will it always return the correct ID?

Is ASP.NET and/or SQL Server setup to handle this, or is it down to me to surround the SQL statement with a mutex object?

All input is greatly appreciated.

resourcesys.
0
Comment
Question by:resourcesys
5 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34915301
Hi,

if you looking for latest one then use

SELECT IDENT_CURRENT('tbl_person')

If you looking for that specific person then

SELECT ID FROM TBL_PERSON WHERE PersonName = 'James Bond'


 @@IDENTITY will give current identity, so if there are multiple inserting happened same time then you might not get correct one,.


- Bhavesh
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 34915322
You can risk your data with this type of coding if you don't lock the register inserts until id is returned.

Take a look at this article for an introduction to SQL Server locking

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
0
 

Accepted Solution

by:
resourcesys earned 0 total points
ID: 34915428
Hi,

Thanks for the replies.

After doing some more searching I have found that the SCOPE_IDENTITY() function will return the ID of the newly inserted record and as it is scope specific, the returned value will always be correct.

resourcesys.
0
 
LVL 2

Expert Comment

by:bapcai
ID: 34931304
What I would like to know is, if many users are using the same function, will it always return the correct ID?
Yes, You should use "try catch" and retrun -1 if SQL statement have error. (the seed of identity from 1).

Is ASP.NET and/or SQL Server setup to handle this, or is it down to me to surround the SQL statement with a mutex object? SQL Server do.
0
 

Author Closing Comment

by:resourcesys
ID: 34949655
Solves problem.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now