While adding a row to the table, how can I return the auto increment value of the row which is added currently in sql server 2008 c#?

Question

It is my function in order to add a row to a table but I don't know how to return the id of it (Thıs Column's name is Group_id)

public Boolean CreateGroup(string Group_Name, string Description, string category, string username)
    {
        Boolean res = false;



        var sql = "INSERT INTO Group ( Group_Name, Description, User_Name) "
        + "VALUES ( @Group_Name, @Description, @User_Name )";

        SqlCommand cmd = new SqlCommand(sql, Connect());

        cmd.Parameters.Add("@Group_Name", SqlDbType.VarChar, 25).Value = Group_Name;
        cmd.Parameters.Add("@User_Name", SqlDbType.VarChar, 15).Value = username;
        cmd.Parameters.Add("@RSS_Description", SqlDbType.VarChar, 1500).Value = Description;

        try
        {


            cmd.ExecuteNonQuery();
            res = true;
            closeConnection();
        }
        catch (Exception)
        {
            res = false;
        }

        return res;

    }

possible duplicate of [Best way to get identity of inserted row?](http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) — Pondlife, Apr 02 '12 at 10:52

score 1 · Answer 1 · answered Apr 02 '12 at 10:51

Add to your commandtext SELECT SCOPE_IDENTITY();, then call ExecuteScalar and get the result value.

var sql = "INSERT INTO Group ( Group_Name, Description, User_Name) " +
"VALUES ( @Group_Name, @Description, @User_Name ); " +
"SELECT SCOPE_IDENTITY();";

Arion · Accepted Answer · 2012-04-02T14:37:54.970

Change this:

var sql = "INSERT INTO Group ( Group_Name, Description, User_Name) "
        + "VALUES ( @Group_Name, @Description, @User_Name )";

To this:

var sql = "INSERT INTO Group ( Group_Name, Description, User_Name) "
        + "VALUES ( @Group_Name, @Description, @User_Name );"
        "SELECT SCOPE_IDENTITY() AS Group_id;";

And change this:

cmd.ExecuteNonQuery();

To this:

var yourId= cmd.ExecuteScalar()

score 0 · Answer 3 · answered Apr 02 '12 at 10:55

You can also use the OUTPUT clause to retrieve inserted values.

Your SQL query could look like this:

INSERT INTO Group ( Group_Name, Description, User_Name)
output inserted.Id
VALUES ( @Group_Name, @Description, @User_Name )

(assuming the PK column is named 'Id' - please adjust the name if otherwise)

If you use this you'll have to use a DataAdapter to get the data that's returned.

The beauty of this is that if you insert more records, all the newly inserted Ids will be returned. Also, you can retrieve more columns (for example: inserted.Description)

While adding a row to the table, how can I return the auto increment value of the row which is added currently in sql server 2008 c#?

3 Answers3