Getting SCOPE_IDENTITY from SQL Server on Insert

Question

I guess it is too late and I'm too tired to see what I'm doing wrong. Here is what I'm trying:

int imageId = imageDal.AddImage(new SqlParameter[]
        {
            new SqlParameter("@IMAGE_ID", 
        SqlDbType.Int, Int32.MaxValue, ParameterDirection.Output,
        true, 0, 0,"IMAGE_ID", DataRowVersion.Current,DBNull.Value),

        new SqlParameter("@IMAGE", 
        SqlDbType.Image, 11, ParameterDirection.Input,
        true, 0, 0,"IMAGE", DataRowVersion.Current,image)
        });

public int AddImage(SqlParameter[] spParams)
{
    SqlHelper.ExecuteNonQuery(BaseDAL.ConnectionStringImages, INSERT_IMAGE_SQL, spParams);
    return Convert.ToInt32(spParams[0].Value);
}

Stored Procedure:

[dbo].[sp_insert_image]
    -- Add the parameters for the stored procedure here
    @IMAGE_ID int OUT,
    @IMAGE image
AS
BEGIN
    INSERT INTO images
    (IMAGE)
    VALUES
    (@IMAGE)
    SELECT @IMAGE_ID = SCOPE_IDENTITY();
END
GO

I get DBNull as spParams[0].Value. I've tried setting value of @IMAGE_ID to a constant in my stored procedure yet it didn't change anything so the problem isn't with my stored procedure (that is what I think).

When I execute the procedure from sql management studio, I see the inserted_id returning..

Answer 1

I ended up with executescalar and returning the SCOPE_IDENTITY() directly from SP.

If there is another way of doing it and getting the value from the sqlparameter, I'd love to hear that out.

Answer 2

I suppose, the problem is in ExecuteNonQuery method. It returns object array, instead of sqlparam's array.

Just have a ref on the output sqlparam object and get the value from that ref.

Good Luck!

P.S. There's another solution. Check the link

http://www.dotnetmonster.com/Uwe/Forum.aspx/dotnet-distributed-apps/160/Data-Access-Application-Block-Output-Parameters

Answer 3

You put Like this....

[dbo].[sp_insert_image]
    -- Add the parameters for the stored procedure here
    @IMAGE_ID int OUT,
    @IMAGE image
AS
BEGIN
    INSERT INTO images (IMAGE) VALUES (@IMAGE)
    SET @IMAGE_ID  = SCOPE_IDENTITY();
END
GO