SqlCommand return value parameter

Question

Maybe someone else looking at this code will be able to tell me why the returnID is always 0. I am attempting to retrieve the new ID from the inserted record.

public int AddToInventory(int PartID, int QtyOnHand, int SpokenFor, int LowOrderQty, int HighOrderQty, decimal LastBuyPrice, 
                                    decimal AvgBuyPrice)

        {
        ConfigDAL config = new ConfigDAL();
        string connstr = config.GetConnString();
        SqlConnection conn = new SqlConnection(connstr);

        string query;
        query = "INSERT INTO Inventory (PartID, QtyOnHand, SpokenFor, LowOrderQty, HighOrderQty, LastBuyPrice, "
                    + "AvgBuyPrice, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy) "
                    + "Values (@PartID, @QtyOnHand, @SpokenFor, @LowOrderQty, @HighOrderQty, @LastBuyPrice, @AvgBuyPrice, "
                    + "@CreatedOn, @CreatedBy, @ModifiedOn, @ModifiedBy)";

        SqlCommand cmd = new SqlCommand(query, conn);
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Parameters.AddWithValue("@PartID", PartID);
        cmd.Parameters.AddWithValue("@QtyOnHand", QtyOnHand);
        cmd.Parameters.AddWithValue("@SpokenFor", SpokenFor);
        cmd.Parameters.AddWithValue("@LowOrderQty", LowOrderQty);
        cmd.Parameters.AddWithValue("@HighOrderQty", HighOrderQty);
        cmd.Parameters.AddWithValue("@LastBuyPrice", LastBuyPrice);
        cmd.Parameters.AddWithValue("@AvgBuyPrice", AvgBuyPrice);
        cmd.Parameters.AddWithValue("@CreatedOn", DateTime.Now);
        cmd.Parameters.AddWithValue("@CreatedBy", GlobalProp.UserName);
        cmd.Parameters.AddWithValue("@ModifiedOn", DateTime.Now);
        cmd.Parameters.AddWithValue("@ModifiedBy", GlobalProp.UserName);
        cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        int returnID = (int)cmd.Parameters["@ID"].Value;

        return returnID;
        }

The record gets inserted to the table fine, but the return value is not right. Am I doing this correctly?

Thanks

codingbiz · Accepted Answer · 2012-10-14 20:04:21Z

2

There is no where you were setting the ID, so you can expect the value to change. You have to do that with

 Select @ID = @Scope_Identity() -- If ID column is an Identity column

OR

Select @ID = @SomeGeneratedValue

Try this

query = "INSERT INTO Inventory (PartID, QtyOnHand, SpokenFor, LowOrderQty, HighOrderQty, LastBuyPrice, "
                + "AvgBuyPrice, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy) "
                + "Values (@PartID, @QtyOnHand, @SpokenFor, @LowOrderQty, @HighOrderQty, @LastBuyPrice, @AvgBuyPrice, "
                + "@CreatedOn, @CreatedBy, @ModifiedOn, @ModifiedBy);"
                + " Declare @ID int;"
                + " Select @ID = Scope_Identity()";

edited Oct 14, 2012 at 20:04

answered Oct 14, 2012 at 19:44

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan Over a year ago

Your method is telling me at the point that the line cmd.ExecuteNonQuery() runs that I must declare the scalar variable "@ID"

Pashec · Accepted Answer · 2012-10-14 19:41:59Z

2

In your SQL query add one more line:

select @ID = scope_identity()

answered Oct 14, 2012 at 19:41

Pashec

23.3k3 gold badges30 silver badges26 bronze badges

Comments

Radioleao · Accepted Answer · 2015-04-07 10:14:23Z

In my experience Direction ReturnValue is not working. It has sense because in a command like your, @ID is a script variable so is more an output parameter than a return value.

For me, it's better to use ParameterDirection.Output without declare @ID (it's declared with .Output option):

cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output;
query = "INSERT INTO Inventory (PartID, QtyOnHand, SpokenFor, LowOrderQty, HighOrderQty, LastBuyPrice, "
            + "AvgBuyPrice, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy) "
            + "Values (@PartID, @QtyOnHand, @SpokenFor, @LowOrderQty, @HighOrderQty, @LastBuyPrice, @AvgBuyPrice, "
            + "@CreatedOn, @CreatedBy, @ModifiedOn, @ModifiedBy);"
            + " Set @ID = Scope_Identity()";

Collectives™ on Stack Overflow

SqlCommand return value parameter

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related