如何从库存C＃sql中扣除数量

Once the product is bought it should reduce from stock quantity

一旦产品被购买,它应该减少库存数量

double qun;

qun = Convert.ToDouble(dataGridView1.Rows[0].Cells[3].Value) - Convert.ToDouble(textBox2.Text);

sqlconnection = new SqlCeConnection(ConnectionString);
sqlcommand = new SqlCeCommand();
sqlconnection.Open();
sqlcommand.Connection = sqlconnection;
sqlcommand.CommandText = (@"UPDATE ItemStock_Info SET Quantity =@qun WHERE [Item_Number]='"+ textBox1.Text +"'");
sqlcommand.Parameters.Add("@qun", qun);

sqlcommand.ExecuteNonQuery();
sqlconnection.Close();

1 个解决方案

#1

If the variable qun is the quantity sold then you should subtract it from the value in the database table not simply assign that value back overwriting your stock value

如果变量qun是已售出的数量,那么您应该从数据库表中的值中减去它,而不是简单地将该值分配回来覆盖您的库存值

   @"UPDATE ItemStock_Info 
     SET Quantity = Quantity - @qun 
     WHERE ...  "

A note to you. Why use a parameter for the quantity and not for the where condition? This should be avoided at all costs and parameters should be added in a different way

给你的一张纸条。为什么使用参数作为数量而不是where条件?应不惜一切代价避免这种情况,并应以不同的方式添加参数

sqlcommand.CommandText = (@"UPDATE ItemStock_Info 
                          SET Quantity = Quantity - @qun 
                          WHERE [Item_Number]=@num";
sqlcommand.Parameters.Add("@qun", SqlDbType.Float).Value = qun;
sqlCommand.Parameters.Add("@num", SqlDbType.NVarChar).Value = textBox1.Text;
sqlcommand.ExecuteNonQuery();

I should add a warning here. I guess that you don't want to subtract the quantity sold if this change your Quantity value to a level below zero. Being this a SQL Server CE database, it is probably safe to assume that none could change the Quantity value behind your back and you have already tested this condition before the subtract operation, but in a multiuser environment I suggest you to use a database that has better support for concurrency on these fields.

我应该在这里添加警告。我想如果将您的数量值更改为零以下的水平,您不想减去已售出的数量。作为一个SQL Server CE数据库,可以安全地假设没有人可以改变你背后的Quantity值,你已经在减法操作之前测试了这个条件,但在多用户环境中我建议你使用一个数据库更好地支持这些领域的并发性。

#1