I’m using .NET 3.5, Visual Studio Team System 2008 and SQL Server 2005 and i’m trying to return the new id that is created after an INSERT statement. When i call ExecuteScalar, it returns zero. I’ve googled the problem and it seems that you need to cast the scope_identity to int before returning it.
Here are 3 solotions for the problem:
1. SELECT CAST(SCOPE_IDENTITY() AS INT) instead of just SELECT SCOPE_IDENTITY()
2. Declare a variable to hold the scope identity as follows:
DECLARE @NewID INT
SET @NewID = SELECT SCOPE_IDENTITY()
3. Have an output parameter in your stored procedure