CODEWITHSUNDEEP
c#sql-server
Greg Gum
Greg Gum

Reputation: 38069

How to return the ID of the Inserted Record to C#

I have this stored procedure:

Insert into dbo.file_row (file_sub_type) values (@file_sub_type)
DECLARE @result int;
SET @result = SCOPE_IDENTITY()
RETURN @result;

This works fine to return the id in SSMS. However, when I call it from C#, it returns -1.

var connection = GetSqlConnection();
connection.Open();

SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "InsertInto_file_row";
command.Parameters.Add(new SqlParameter("@file_sub_type", fileType));

int result = command.ExecuteNonQuery();
connection.Close();
return result;

I don't see what I am doing wrong. I just need the Id of the inserted record.

Greg

Upvotes: 0

Views: 191

Answers (4)

Joel Coehoorn
Joel Coehoorn

Reputation: 416111

Check the docs on ExecuteNonQuery():

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

(Emphasis mine)

If you want to get information back, you have a couple options:

  1. Change RETURN to SELECT and ExecuteNonQuery() to ExecuteScalar()
  2. Use an OUTPUT parameter

Upvotes: 5

lojolis
lojolis

Reputation: 1

public int AddProductCategory(string newName, string connString)
    {
          string sql =
    "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
    + "SELECT CAST(scope_identity() AS int)";
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.AddWithValue("@Name", newName);
               
                con.Open();
                latestInsertedId = (int)cmd.ExecuteScalar();
                con.Close();
            }
            return latestInsertedId ;
        }
    }

Upvotes: 0

gmail user
gmail user

Reputation: 2783

This will help you. The function returns the new Identity column value if a new row was inserted, 0 on failure. It is from MSDN

static public int AddProductCategory(string newName, string connString)
{
   Int32 newProdID = 0;
   string sql =
    "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
    + "SELECT CAST(scope_identity() AS int)";
   using (SqlConnection conn = new SqlConnection(connString))
  {
    SqlCommand cmd = new SqlCommand(sql, conn);
    cmd.Parameters.Add("@Name", SqlDbType.VarChar);
    cmd.Parameters["@name"].Value = newName;
    try
    {
        conn.Open();
        newProdID = (Int32)cmd.ExecuteScalar();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}
return (int)newProdID;

}

Upvotes: 0

JtM
JtM

Reputation: 233

To add on to Joel's response Try ExecuteScalar instead

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored. (Overrides DbCommand.ExecuteScalar().)

Upvotes: 0

Related Questions