也许查看此代码的其他人能够告诉我为什么 returnID 始终为 0。我正在尝试从插入的记录中检索新 ID。
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;
}
记录很好地插入到表中,但返回值不正确。我这样做正确吗?
谢谢
最佳答案
没有设置 ID 的位置,因此您可以预期该值会发生变化。你必须这样做
Select @ID = @Scope_Identity() -- If ID column is an Identity column
或者
Select @ID = @SomeGeneratedValue
试试这个
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()";
关于c# - SqlCommand 返回值参数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/12885818/