SqlCommand return value parameter
Solution 1
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()";
Solution 2
In your SQL query add one more line:
select @ID = scope_identity()
Solution 3
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()";
Ryan
I have been developing and consulting in software since 2002.
Updated on July 26, 2022Comments
-
Ryan almost 2 years
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
-
Ryan over 11 yearsYour method is telling me at the point that the line cmd.ExecuteNonQuery() runs that I must declare the scalar variable "@ID"