Insert row and retrieve identity using stored procedures

  • Hello,

    I'm using this stored procedure:

    CREATE Procedure ProductInsert

    (

    @ProductID int OUTPUT,

    @CategoryID int,

    @Reference nvarchar (50),

    @Name nvarchar (50),

    @SmallImage nvarchar (50),

    @Image nvarchar (50),

    @OnSale bit,

    @UnitCost money,

    @Stock int,

    @Description nvarchar (3800)

    )

    As

    INSERT INTO Product

    (

    CategoryID,

    Reference,

    Name,

    SmallImage,

    Image,

    OnSale,

    UnitCost,

    Stock,

    Description

    )

    VALUES

    (

    @CategoryID,

    @Reference,

    @Name,

    @SmallImage,

    @Image,

    @OnSale,

    @UnitCost,

    @Stock,

    @Description

    )

    SELECT @ProductID = @@Identity

    GO

    I'm calling the stored procedure like this:

    SqlConnection connection = new SqlConnection(connectionString);

    SqlCommand myCommand = new SqlCommand("ProductInsert", connection);

    myCommand.CommandType = CommandType.StoredProcedure;

    SqlParameter parameterID = new SqlParameter("@ProductID", SqlDbType.Int, 4);

    parameterID.Direction = ParameterDirection.Output;

    myCommand.Parameters.Add(parameterID);

    SqlParameter parameterCategoryID = new SqlParameter("@CategoryID", SqlDbType.Int, 4);

    parameterCategoryID.Value = product.Category.Id;

    myCommand.Parameters.Add(parameterCategoryID);

    .

    .

    .

    connection.Open();

    myCommand.ExecuteNonQuery();

    connection.Close();

    return (Int32) parameterID.Value;

    but when I try to insert a row I get the following error:

    Cannot insert the value NULL into column 'ProductID', table 'e-Commerce.dbo.Product'; column does not allow nulls. INSERT fails. The statement has been terminated.

    Can someone tell me what I am doing wrong?

    Thank you.

  • The error is occuring when the stored procedure attempts to insert data into the Product table. Can you provide the table definition?

    K. Brian Kelley
    @kbriankelley

  • You might also want to use SCOPE_IDENTITY() rather than @@IDENTITY. See BOL for IDENT_CURRENT for an example of these three concepts.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply