Insert row and retrieve identity using stored procedures

• Maria Adelina

  Newbie

  Points: 1

  More actions

  March 20, 2005 at 4:15 am

  #163984

  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.

• K. Brian Kelley

  SSC Guru

  Points: 114704

  More actions

  March 20, 2005 at 4:14 pm

  #547940

  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

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 21, 2005 at 2:19 am

  #547957

  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]