none
Get ID of inserted record. RRS feed

  • Question

  • Hello,

    I have the following code:

    using (SqlConnection conn = new SqlConnection(connectionString))    {
      SqlCommand InsertCurrencyCommand = new SqlCommand();
    
      InsertCurrencyCommand.CommandText =
      "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
      "VALUES(@CurrencyCode, @Name)";
    
       InsertCurrencyCommand.Connection = conn;
    
       conn.Open();
       InsertCurrencyCommand.ExecuteNonQuery();
       conn.Close();
    }
    

    How do I get the ID (Int Primary Key) of the record just inserted?

    Thank You,

    Miguel

    Monday, November 1, 2010 5:27 PM

Answers

  •  

    Hi Moura,

    Easy to do, just grab the value of the ID into a variable at the sqlserver side. Then request the variable like so:

    cmd.CommandText="INSERT INTO Foo (Bar) ('val'); DECLARE @ID INT; SET @ID=SCOPE_IDENTITY()";
    SqlParameter p=new SqlParameter();
    p.Name="@ID";
    p.Size=4;
    p.Direction=ParameterDirection.Output;
    cmd.Parameters.Add(p);
    cmd.ExecuteNonQuery();
    int idOut=(int)p.Value;

    Please note, this is only the mechanism, I would never use this code in production, but it serves the purpose for the question.

    Kind regards,
    Tom de Koning


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Proposed as answer by Tom de Koning Monday, November 1, 2010 9:35 PM
    • Marked as answer by liurong luo Monday, November 8, 2010 10:36 AM
    Monday, November 1, 2010 9:35 PM