How to get Identity value from SQL server after insert record

Question

I add a record in my database with an identity value. I want to get the identity value after inserting. I don't want to do that by stored procedure.

This is my code:

SQLString = " INSERT INTO myTable ";
SQLString += " (Cal1, Cal2, Cal3, Cal4) ";
SQLString += " VALUES(N'{0}',N'{1}',N'{2}',N'{3}') ";
SQLString = string.Format(SQLString, Val1, Val2, Val3, Val4);
SQLString += " Declare @_IdentityCode INT SET @_IdentityCode = @@IDENTITY RETURN @_IdentityCode";

int result;

public int DoCommand2(string sql)
{
        con = new SqlConnection();
        cmd = new SqlCommand();
        da = new SqlDataAdapter();
        cmd.Connection = con;
        da.SelectCommand = cmd;

        string cs = GlobalConstants.SqlConnectionString;
        con.ConnectionString = cs;

        cmd.CommandText = sql;
        int i = cmd.ExecuteNonQuery();
        return i;
}

but I get this error:

A RETURN statement with a return value cannot be used in this context.

I hope this is just "sample" code and isn't used in any type of production system. If you intend to use this for real, please look into using parameters in your query instead of String.Format and at least use "using" when working with your sql connection so it will close and dispose the connection. — Al W, Oct 06 '14 at 09:48

score 6 · Accepted Answer · answered Oct 06 '14 at 09:27

6

Append SELECT SCOPE_IDENTITY(); to your normal INSERT statement:

Replace the last concatenation with:

SQLString += "; SELECT SCOPE_IDENTITY();"

Then to retrieve the ID:

int ID = Convert.ToInt32(command.ExecuteScalar());

answered Oct 06 '14 at 09:27

Saeb Amini

23,054
9
78
76

score 3 · Answer 2 · answered Oct 06 '14 at 09:40

3

Take a good look at the OUTPUT clause. You can output your inserted ID from the INSERT statement. There are examples in the link I posted.

answered Oct 06 '14 at 09:40

TT.

15,774
6
47
88

you should include in your answer that `OUTPUT` works on rows so you will be able to retrieve all `inserted IDs` (not only a `inserted ID`) from a statement. – DrCopyPaste Oct 06 '14 at 09:52
@DrCopyPaste , so if we want to all ids , how can we do that ? – Vishal Sharma Dec 08 '14 at 07:43

score 2 · Answer 3 · edited Oct 06 '14 at 10:56

2

... or just run

select @@identity

edited Oct 06 '14 at 10:56

Taryn

242,637
56
362
405

answered Oct 06 '14 at 09:50

softilium

176
2
2

How to get Identity value from SQL server after insert record

3 Answers3

Linked