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

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.
resourcesysAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
resourcesysConnect With a Mentor Author Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
 
BardobraveCommented:
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
 
bapcaiCommented:
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
 
resourcesysAuthor Commented:
Solves problem.
0
All Courses

From novice to tech pro — start learning today.