CODEWITHSUNDEEP
asp.netsql-server
nag
nag

Reputation: 930

SQL server @@Identity issue

I'm using @@Identity to get identity of a particular row after successful insert on a table, but sometimes I'm getting someother identity other than what it created for a particular row.

Sample:

ID    UserName    Age    Location
1     Andy        22     USA
2     Robert      24     Canada
3     James       26     Mexico

From above sample while getting identity for user 'Robert' some other created 'James' and I'm getting the lastest identity as '3' instead of '2'.

All this is happening in a ASP.NET website user registration, it's giving the latest identity for each connection object and the same connection object is using for all registrations.

Please some one help me this out how to get the exact identiy when some other people using same connection object to get the identity?

If i use SCOPE_IDENTITY() for the below query, I'm getting DBNULL exception while convertion.

cmd.Connection = conn;
                conn.Open();
                int intStatus = cmd.ExecuteNonQuery();
                if (intStatus == 1)
                {
                    SqlCommand cmd1 = new SqlCommand("Select SCOPE_IDENTITY()", conn);
                    id_package = Convert.ToInt32(cmd1.ExecuteScalar());
                }

Upvotes: 1

Views: 68

Answers (4)

James Z
James Z

Reputation: 12318

I don't know anything about asp.net, but it at least looks to me like you're creating a new database session to get the identity and that's why you get nulls. You'll have to fetch it in the same session where you have created the row (or use output clause).

Upvotes: 0

Fabio
Fabio

Reputation: 32445

Use OUTPUT Clause

INSERT INTO YourTable (UserName, Age, Location)
OUTPUT inserted.ID
VALUES ("Andy", 22, "USA")

If ID is identity column, statement will return inserted ID by this query

Upvotes: 1

M.Ali
M.Ali

Reputation: 69514

use SCOPE_IDENTITY() it will return the last identity value generated in your session.

@@IDENTITY will return the last generated identity value in ANY session.

Also if you are inserting multiple rows you can use the OUTPUT clause too to get all the newly generated identity values in your session.

Upvotes: 3

Jasqlg
Jasqlg

Reputation: 183

I think you are looking for SCOPE_IDENTITY(). This will give you the identity value generated from your query or session

Upvotes: 0

Related Questions