Specific cast is not valid, while retrieving scope_identity

Question

I am getting exception: "Specific cast is not valid", here is the code

con.Open();
string insertQuery = @"Insert into Tender (Name, Name1, Name2) values ('Val1','Val2','Val3');Select Scope_Identity();";

SqlCommand cmd = new SqlCommand(insertQuery, con);
cmd.ExecuteNonQuery();
tenderId = (int)cmd.ExecuteScalar();

Answer 1

Test the following first:

object id = cmd.ExcuteScalar()

Set a break point and have a look at the type of id. It is probably a Decimal and cannot directly be casted to int.

Answer 2

it needs Convert.ToInt32(cmd.ExecuteScalar());

Answer 3

Try this:-

con.Open();
string insertQuery = @"Insert into Tender (Name, Name1, Name2) values ('Val1','Val2','Val3');Select Scope_Identity();";

SqlCommand cmd = new SqlCommand(insertQuery, con);
tenderId = Convert.ToInt32(cmd.ExecuteScalar());

EDIT

It should be this as it is correctly pointed out that scope_identity() returns a numeric(38,0) :-

tenderId = Convert.ToInt32(cmd.ExecuteScalar());

Note: You still need to remove the:-

cmd.ExecuteNonQuery();

Answer 4

In the interests of completeness, there are three issues with your code sample.

1) You are executing your query twice by calling ExecuteNonQuery and ExecuteScalar. As a result, you will be inserting two records into your table each time this function runs. Your SQL, while being two distinct statements, will run together and therefore you only need the call to ExecuteScalar.

2) Scope_Identity() returns a decimal. You can either use Convert.ToInt32 on the result of your query, or you can cast the return value to decimal and then to int.

3) Be sure to wrap your connection and command objects in using statements so they are properly disposed.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        connection.Open();
        int tenderId = (int)(decimal)command.ExecuteScalar();
    }
}