CODEWITHSUNDEEP
c#sql
Udantha
Udantha

Reputation: 77

How to deduct quantity from stock 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();

Upvotes: 1

Views: 6461

Answers (1)

Steve
Steve

Reputation: 216363

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

   @"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

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.

Upvotes: 1

Related Questions