Why am I getting InvalidCastException when casting object to integer?

Question

I had an odd problem, here's the setup:

ASP.NET 3.5 app / MSSQLSERVER 2008 back-end.

I called ExecuteScalar from my code, which returned an object, then I tried casting that object to int (i actually have a generic method

 T ConvertDBValue<T>(object o) {...}

, but that's not as important).

ExecuteScalar is hitting the following sproc:

...  
insert into ...
select scope_identity()

My primary key is an identity field, and returned 85. Next thing I got is InvalidCastException trying to cast 85 to int.

The solution was to explicitly create an INT variable, and assign SCOPE_IDENTITY() to it before returning it from the sproc as follows:

...  
DECLARE @x int
insert into ...
select @x = scope_identity()
select @x

Can somebody tell me what was the problem with my first approach, and why wouldn't it cast 85 to int?

Answer 1

scope_identity() actually returns a (TSQL) BIGINT (I've been caught by this before!)

So your code was trying to cast a BIGINT to an int.