C# Scope_Identity Issue

Question

Browsed some answers and doesn't appear to be working for me.

I need the ID field of a table to be returned so I can use it in a different part of the program, I've tried using

    Convert.ToInt32(sqlComm.ExecuteScalar());

But no luck, and same for

    Convert.ToInt32(sqlComm.Parameters["ID"].Value);

And both return 0, even though the record does get inserted into the table.

I'll dump the code below, can anyone see what I'm doing wrong?

    using (SqlConnection sqlConnect = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        {
            using (SqlCommand sqlComm = new SqlCommand("up_Insert_Address", sqlConnect))
            {
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@AddressID", SqlDbType.BigInt).Direction = ParameterDirection.Output;
                sqlComm.Parameters.Add("@AddressLineOne", SqlDbType.NVarChar, 40).Value = address.AddressLineOne;   

                try
                {
                    sqlComm.Connection.Open();
                    return Convert.ToInt32(sqlComm.ExecuteScalar());
                }

                catch (SqlException)
                {
                }

                finally
                {
                    sqlComm.Connection.Close();
                }
            }
        }

And Stored Procedure:

    @AddressID   Bigint OUTPUT,
    @AddressLineOne  NVarChar(40)
    AS
     BEGIN
      BEGIN TRY
      INSERT INTO Address
      (
         AddressLineOne
      )
      VALUES 
      (
         @AddressLineOne
      )

      SET @AddressID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
       DECLARE @Err nvarchar(500)
       SET @Err = ERROR_MESSAGE()
       RAISERROR(@Err, 16, 1)
    END CATCH
   END

D Stanley · Accepted Answer · 2013-01-17T17:17:00.393

6

You should be using

Convert.ToInt64(sqlComm.Parameters["@AddressID"].Value);

after you execute the command using ExceuteNonQuery. For future reference, ExecuteScalar returns the first column of the first row in the result set returned by the query. You're not returning anything, just setting the value of an OUTPUT parameter.

Also you should DEFINITELY not swallow any SqlException. Since your command and connection are already in using blocks you don't need to add another try/catch/finally. Change it to to:

//try
//{
    sqlComm.Connection.Open();
    sqlComm.ExecuteNonQuery();
    return Convert.ToInt64(sqlComm.Parameters["@AddressID"].Value);
    // using Int64 since the SQL type is BigInt
//}

//catch (SqlException)
//{
//}

//finally
//{
//    sqlComm.Connection.Close();
//}

edited Jan 17 '13 at 17:17

answered Jan 17 '13 at 16:52

D Stanley

149,601
11
178
240

You're welcome - please see my update on removing the `catch` block. – D Stanley Jan 17 '13 at 16:59
I would recommend using `long.TryParse()` on the result (`OUTPUT` parameter) as an added measure to prevent an exception being thrown. It should never happen, but maybe if someone later changes the schema, this way existing code would be more fault-tolerant. – Erik Jan 17 '13 at 17:08
@SiLo - I disagree. The return value should ALWAYS by a `BigInt`, not a string that needs to be parsed. Casting should be fine. If there's an error on casting you WANT that exception. – D Stanley Jan 17 '13 at 17:12
@D Stanley - Did you mean to type: `Convert.ToInt32(sqlComm.Parameters["@AddressID"].Value);` in the first code example and `Convert.ToInt64(sqlComm.Parameters["@AddressID"].Value);` in the second? – Brian Jan 17 '13 at 17:16
I corrected it after I saw that the parameter is a `BigInt`. Corrected the first example. – D Stanley Jan 17 '13 at 17:17
@DStanley you are correct. I was thinking it returned as a string or object with string formatting. Since it keeps its native type, that is what you want. – Erik Jan 17 '13 at 17:19

Erik · Answer 2 · 2013-01-17T17:20:14.520

var connectionstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

var addressId = 0L; // long value (64bit)

using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("up_Insert_Address", connection))
{
  command.CommandType = CommandType.StoredProcedure;

  command.Parameters.AddWithValue("@AddressID", addressId);    
  command.Parameters.AddWithValue("@AddressLineOne", address.AddressLineOne); 

  command.Parameters["@AddressID"].Direction = ParameterDirection.Output;

  try
  {
    if(connection.State != ConnectionState.Open)
      connection.Open();

    var rowsAffected = command.ExecuteNonQuery();
    addressId = Convert.ToInt64(command.Parameters["@AddressID"].Value);
  }
  catch (SqlException)
  {
    // Handle SQL errors here.
  }
}

C# Scope_Identity Issue

2 Answers2