Calculation to avoid negative value in database

Question

How to set a trap in my program that when I push the purchase button and the quantity to be purchased exceeds the amount remaining on my database it will give an error that there's no enough amount left. Currently it only goes to a negative value. Sql server does not support unsigned values and I do not know if what I want to do is even possible.

Here's my code.

SqlConnection con = new SqlConnection(
 @"Data Source=DESKTOP-39SPLT0\SQLEXPRESS;Initial Catalog=posDB;Integrated Security=True");

string Query = "UPDATE tblProducts SET qty = qty - @quantity where pName = @name";         
using (SqlCommand cmd = new SqlCommand(Query, con))
{
    cmd.Parameters.AddWithValue("@quantity", int.Parse(txBurger.Text));
    cmd.Parameters.AddWithValue("@name", label1.Text);              
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}
using (SqlCommand cmd = new SqlCommand(Query, con))
{                
    cmd.Parameters.AddWithValue("@quantity", int.Parse(txCheese.Text));
    cmd.Parameters.AddWithValue("@name", label5.Text);
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}

Answer 1

You could set a CONSTRAINT on the value of qty on the table. For example:

CREATE TABLE test (qty int);
ALTER TABLE test ADD CONSTRAINT PosQty CHECK (qty >= 0);
GO

INSERT INTO test VALUES(3);
GO
UPDATE test
SET qty = qty - 2; --Will work (3 - 2 = 1) 
GO
UPDATE test
SET qty = qty - 3; --Will fail (1 - 3 = -2)
GO
UPDATE test
SET qty = qty - 1; --Will work (1 - 1 = 0)
GO
--Clean up
DROP TABLE test;

Of course, this may not be suitable, depending on your scenario. you definitely need to ensure you error handle appropriately.

Answer 2

Here is how I would do this:

First, change your query:

UPDATE tblProducts 
SET qty = qty - @quantity 
where pName = @name
AND qty >= @quantity;
SELECT @@ROWCOUNT;

Then, instead of using ExecuteNonQuery use ExecuteScalar and check if the number of records modified is 0 it means that the @quantity is bigger than the value of qty.

Also, I would recommend adding a check constraint as shown in Larnu's answer.