check if a number from a column in a table is greater than 0

Question

Sorry if this question is duplicate, but I have not found the answer. I want to check if a value of a column from a table is greater than 0, if not do something. The problem is that I do not know how to get the value from the table and check it.

My idea:

if(column table(Quantity) where ID=@ID >0)
   {
      Do something
   }

I am using asp.net, C# and SQL. Here is some of my code: //This code is when an item is added to the table with the current id the number of the item is reduced by 1.

SqlCommand command = new SqlCommand("UPDATE Items SET Quantity=Quantity-1;
command.ExecuteNonQuery();

In the Items table I have the Quantity of the Item, so lets say 5 quantity of that specific item, and everytime that is added to the gridview is reduced by 1, I do not want it to be below 0. (Now with this code it goes below 0) I have the idea as I told you above but i do not know how to do it.

Anybody has an idea how to do it?

So when quantity reaches 0, what's supposed to happen - just quit decreasing the value or? — jarlh, Jan 16 '15 at 09:09

score 1 · Answer 1 · answered Jan 16 '15 at 09:35

Just use WHERE in your query. Give me your detail story END to END. Plus, if you want to alert an error if something happens on your query execution, you can doing by store the ExecuteNonQuery result into a variable. Here is for your references

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery%28v=vs.110%29.aspx

Let me highlight some points here :

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

From here, you can add some logical processing to what you do even the query return error result.

Vojtěch Dohnal · Answer 2 · 2015-01-16T10:29:27.363

0

Just use WHERE clause within your UPDATE command:

SqlCommand command = new SqlCommand(
                     "UPDATE Items SET Quantity=Quantity-1 WHERE ID=@Id AND Quantity>0");
command.Parameters.AddWithValue("@Id", id);
int numberOfRecords = command.ExecuteNonQuery();
if (numberOfRecords==0)
    throw new Exception("Error setting quantity.");

You can verify whether the quantity has been set by checking affected number of records, should be 1 if OK.

edited Jan 16 '15 at 10:29

answered Jan 16 '15 at 09:16

Vojtěch Dohnal

7,867
3
43
105

Yes but if I want to give an error message if tries to reduce? – user4457996 Jan 16 '15 at 09:27
@user4457996 See http://stackoverflow.com/a/10059585/2224701. If returned number is 0 then nothing was updated. – Vojtěch Dohnal Jan 16 '15 at 10:11

score 0 · Answer 3 · answered Jan 16 '15 at 10:25

To get the value of the Quantity column after updating it, use the OUTPUT clause in your SQL statement, for example:

UPDATE <YOUR_TABLE> SET Quantity = Quantity - 1
OUTPUT INSERTED.Quantity
WHERE ID = <YOUR_ID>

Once your Quantity is being returned as 0, you can do whatever action you require in your software, such as showing a prompt if the user tries to decrease it any further.

There's more information on MSDN here: http://msdn.microsoft.com/en-us/library/ms177564.aspx

If you're not using SQL Server 2005 or later (which is required for OUTPUT), you could still write a simple Stored Procedure to do the same, e.g.

CREATE PROCEDURE DecreaseQuantity
    @id int
AS
BEGIN

    UPDATE <YOUR_TABLE>
    SET Quantity = Quantity - 1
    WHERE ID = @id

    SELECT Quantity
    FROM <YOUR_TABLE>
    WHERE ID = @id

END

score 0 · Answer 4 · answered Jan 16 '15 at 10:35

I would not recommend doing this kind of check in your application code.

For ensuring value ranges of columns in SQL there exists the construct of check constraints. For example, if you want to ensure you won't ever have negative values (or zero) in a column you could add the following constraint

ALTER TABLE Items
ADD CONSTRAINT CK_Items_Quantity_Range CHECK (
   Quantity > 0
)

Then if you do your UPDATE/INSERT you will receive an exception that you can react to in your application code.

Alternatively you could also use a trigger if you want some sql-action to be done when a column reaches a certain value.

check if a number from a column in a table is greater than 0

4 Answers4