Getting SCOPE_IDENTITY from SQL Server on Insert

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:

  • How to use sp_MSforeachtable to update a column which is present in all tables of a database?
  • Python - pyodbc call stored procedure with parameter name
  • Select count(*) from result query
  • search all tables, all columns for a specific value SQL Server
  • Can a sql server table have two identity columns?
  • Insert value from combo box to SQL Server
  • [dbo].[sp_insert_image]
        -- Add the parameters for the stored procedure here
        @IMAGE_ID int OUT,
        @IMAGE image
        INSERT INTO images

    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..

  • With EF6 do I need to use COMMIT and ROLLBACK inside a Stored Procedure?
  • Populating multiselect list with data MVC 3 .NET
  • Where can i find “Microsoft.SqlServer.BatchParser.dll”?
  • Preventing SQL Injection in ASP.Net
  • How to handle Foreign key for optional field in .NET
  • Why empty cell throws an error during SQL stored procedure execution
  • 3 Solutions collect form web for “Getting SCOPE_IDENTITY from SQL Server on Insert”

    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.

    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

    You put Like this….

        -- Add the parameters for the stored procedure here
        @IMAGE_ID int OUT,
        @IMAGE image
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.