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

Looking at it, it all looks sane; you've set the direction, for example; I *assume* `INSERT_IMAGE_SQL` is set to `[dbo].[sp_insert_image]`; I wonder if maybe `SqlHelper` is distorting things, but that seems... odd. — Marc Gravell, Feb 07 '11 at 06:29
There must be something wrong with it. I'll restart my computer if I can't find a solution :) Yes private static string INSERT_IMAGE_SQL = "sp_insert_image"; Also the procedure runs smoothly, row gets inserted yet I can't get the id of it - in other words, get exception in convert.toint32.. — Pabuc, Feb 07 '11 at 06:46
you cite `sp_insert_image`, not `[dbo].[sp_insert_image]` - is there perhaps another version in your name that is getting called in preference? a `[pabuc].[sp_insert_image]` for example? — Marc Gravell, Feb 07 '11 at 06:57
No, actually there is only 1 stored procedure in this database and this is sp_insert_image — Pabuc, Feb 07 '11 at 07:02
Does the parameters update automatically? I mean do I have to do something extra to set the value of the desired sqlparam? I'm sure it is set in stored proc but I can't get it. I need to find a way of getting that value :) — Pabuc, Feb 07 '11 at 07:17

score 2 · Accepted Answer · edited Jul 12 '17 at 10:49

2

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.

edited Jul 12 '17 at 10:49

Liam

27,717
28
128
190

answered Feb 07 '11 at 07:54

Pabuc

5,528
7
37
52

hgulyan · Answer 2 · 2011-02-07T08:21:55.490

1

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

edited Feb 07 '11 at 08:21

answered Feb 07 '11 at 08:14

hgulyan

8,099
8
50
75

score 0 · Answer 3 · edited Jul 17 '15 at 11:35

0

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

edited Jul 17 '15 at 11:35

GeirGrusom

999
5
18

answered Jul 17 '15 at 10:22

Anshu

51
1
1

Getting SCOPE_IDENTITY from SQL Server on Insert

3 Answers3

Linked