0

I have created table in MS SQL 2008 with one identity column(Start Value - 1 and Increment is also 1) and 4 other columns. I am accessing this DB from C# ASP.NET. Used to push data only for the non identity column. Identity column will auto increment itself.

As of now i am manually querying the column value with the remaining for columns. But I am facing problem if all the other four column values are equal i am not getting the exact value which i am looking for

Now my query is, Is there any why in C# where I can get the value of the newly created identity column whenever new record is created.

Thanks.

Improve this question

3 Answers 3

Reset to default
2

You can use 

SCOPE_IDENTITY()

Which will returns the primary key value of the recently inserted row

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

The answer to your question actually lies in SQL Server. You can run:

SELECT @@identity

after your insert to get the last inserted row identity.

http://technet.microsoft.com/en-us/library/aa933167(v=sql.80).aspx

EDIT BASED ON COMMENTS:

Consider using SCOPE_IDENTITY() as referenced here:

http://technet.microsoft.com/en-us/library/aa259185(v=sql.80).aspx

Improve this answer

7 Comments

You might consider SCOPE_IDENTITY over @@IDENTITY and build an INSERT statement that is coupled with ; SELECT SCOPE_IDENTITY().
Could do that. Assuming proper query architecture, it should not make a significant difference though.
It does not in general, but it's a good practice for scalability. We deal in transactions in the millions per day; @@IDENTITY breaks down in that type of environment.
NEVER EVER USE @@IDENTITY use SCOPE_IDENTITY(). throw in a trigger and a log table with an identity column and you will have problems.
@BrianP - Nothing to do with "proper query architecture" If someone adds a trigger to the table in the future that inserts into a table with an id column. @@IDENTITY will definitely return incorrect results as it returns last id value generated by the session. SCOPE_IDENTITY has the correct semantics here.
|
0

In SQL terms you can output the records back if you wish it. But how you might apply this to C# is up to you. Example:

INSERT INTO TABLE_A (SOMETHING, SOMETHINGELSE, RANDOMVAL3)
OUTPUT inserted.A_ID, inserted.SOMETHING, inserted.SOMETHINGELSE, inserted.RANDOMVAL3
SELECT 'ASD','DOSD', 123

But unless you're using merge, you can't use OUTPUT to print out any values from joining tables from an INSERT. But that's another matter entirely, I think.

Also, it's hardly good practice to bounce this data between the application and the DB all the time, so I'd look to alternatives if possible.

Improve this answer

1 Comment

Hi Kahn, Can u please help me with some sample query.. Thanks in advance

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.