views:

89

answers:

3

In SQL Server, ID is a not null integer, and an identity.

When I run the following code, I get an InvalidCastException on the last line:

SqlCommand cmd = new SqlCommand();
cmd.Connection = _conn;
cmd.CommandText = @"INSERT INTO [Users] (Name, Email, Password) VALUES (@name, @email, @pass); SELECT SCOPE_IDENTITY()";
cmd.Parameters.AddWithValue("@name", newUser.Name);
cmd.Parameters.AddWithValue("@email", newUser.Email);
cmd.Parameters.AddWithValue("@pass", newUser.PasswordHash);
int id = (int)cmd.ExecuteScalar();

What is ExecuteScalar() returning here? Whatever its returning has a ToString() that makes it look like a number, so this awful line of code works:

int id = Int32.Parse(cmd.ExecuteScalar().ToString());
+2  A: 

It's probably returning a boxed instance of a different numeric type, such as long.
A boxed long cannot be converted to int.

You can call GetType() on the return value to see what type it really is.

SLaks
To expand on this answer, ExecuteScalar return type is Object, which means any value type will have been boxed, you cannot cast a boxed value type into anything other than the original type
Steve Ellinger
A: 

From the end of your INSERT statement

SELECT SCOPE_IDENTITY()

It's returning the identity value of the row inserted into the [Users] table.

Dave Barker
+4  A: 

SCOPE_IDENTITY() returns a decimal in code, otherwise known as NUMERIC(38,0) in TSQL.

http://msdn.microsoft.com/en-us/library/ms190315.aspx

So if you want a direct cast, you can do (int)(decimal)cmd.ExecuteScalar();. Note that a decimal to int conversion can lose information in the strictest sense, so just be advised. But with your identity column being an integer, the conversion will be safe.

Anthony Pegram