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("@IMAG开发者_如何学CE_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..
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
http://www.dotnetmonster.com/Uwe/Forum.aspx/dotnet-distributed-apps/160/Data-Access-Application-Block-Output-Parameters
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
精彩评论