SQL Scope_Identity is returning 0 or -1
c#, scope-identity, sql, stored-procedures
Solution
ExecuteScalar 'Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored'
If you select @fileID (instead of return) it should work. Alternatively you could access the @fileID parameter value after you execute the query, in which case there's no real point having an ExecuteScalar, you could change it to to `ExecutenonQuery`.
Problem
I'm having issues getting the last row affected ID, it's returning 0, this means that it has an error on format being inserted. I executed the stored procedure and added the values manually, it return'd the correct ID. But when I try to do it with the code it keeps returning 0 or -1 ... I tried this last night after several hours and I'm already confused with the values it gave me. C#: ``` conn.Open(); cmd.Parameters.AddWithValue("@fileName", fileName); cmd.Parameters.AddWithValue("@filePrivacy", filePrivacy); cmd.Parameters.AddWithValue("@filePassword", filePassword); cmd.Parameters.AddWithValue("@fileDescription", fileDesc); cmd.Parameters.AddWithValue("@fileOwner", fileOwner); cmd.Parameters.AddWithValue("@fileDate", DateTime.Now); cmd.Parameters.AddWithValue("@fileExpire", DateTime.Now.AddMinutes(fileExpire)); cmd.Parameters.AddWithValue("@fileCodeText", fileType); var fileID = cmd.Parameters.Add("@fileID", SqlDbType.Int); fileID.Direction = ParameterDirection.Output; int returnfileID = (int)cmd.ExecuteScalar(); return returnfileID; ``` Stored Procedure: ``` CREATE PROCEDURE [dbo].[Upload] @fileName nvarchar(20), @filePrivacy int, @filePassword nvarchar(50), @fileDescription nvarchar(200), @fileOwner nvarchar(14), @fileDate smalldatetime, @fileExpire smalldatetime, @fileCodeText int, @fileID int out AS INSERT INTO Files ([FileName], FilePrivacy, FilePassword, FileDescription, FileOwner, FileDate, FileExpire, FileCodeText) VALUES (@fileName, @filePrivacy, @filePassword, @fileDescription, @fileOwner, @fileDate, @fileExpire, @fileCodeText) SET @fileID = SCOPE_IDENTITY() RETURN @fileID ``` SQL Table: ``` CREATE TABLE [dbo].[Files] ( [Id] INT IDENTITY (1, 1) NOT NULL, [FileName] NVARCHAR (20) NOT NULL, [FilePrivacy] INT NOT NULL, [FilePassword] NVARCHAR (50) NULL, [FileDescription] NVARCHAR (200) NULL, [FileOwner] NVARCHAR (14) NOT NULL, [FileDate] SMALLDATETIME NOT NULL, [FileExpire] SMALLDATETIME NOT NULL, [FileCodeText] INT NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC) ); ```