0

I am working on C# app using SQL Server as database here is a very simple problem which I could not find out till now. I created a check constraint on my QUANTITY column like this:

QUANTITY >= 0

The quantity is updating from C# and I don't want the quantity to become less than zero but when my quantity is 2 and I subtract 2 so it should allow zero as a quantity but not less than 0 but it throws following exception :

The UPDATE statement conflicted with the CHECK constraint \"CK_ITEM_DETAILS_QUANTITY\". The conflict occurred in database \"MyDatabase\", table \"dbo.ITEM_DETAILS\", column 'QUANTITY'.\r\n The statement has been terminated.

Any idea what I'm doing wrong?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kretos
  • 91
  • 1
  • 3
  • 10
  • 2
    Use a breakpoint and check the value you're updating the `QUANTITY` field with. Are you really inserting 0 or is it null? – ovaltein Mar 11 '14 at 20:36
  • 3
    Use Sql Server profiler, you'll be able to see exactly how the query is made. Could be useful if you're using an ORM. – Crono Mar 11 '14 at 20:38
  • Need a bit more info like the full table definition and how you are you trying to insert the data (ADO.NET, entity framework, etc.) – user957902 Mar 11 '14 at 20:52
  • Can you share the UPDATE you're using? – thepirat000 Mar 11 '14 at 22:36

2 Answers2

1

Without code it is not possible to say what exactly went wrong, but one thing is for sure: You are trying to write a value where the condition QUANTITY >= 0 does not hold. You seem to mistakenly assume that you aren't, but the message is unambiguous and there are no known bugs in that area. Maybe a floating point rounding issue?

usr
  • 168,620
  • 35
  • 240
  • 369
1

I solved it i just change my condition from QUANTITY >= 0 TO QUANTITY>0 OR QUANTITY=0 and it starts working fine now. :)

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
Kretos
  • 91
  • 1
  • 3
  • 10
  • Very strange! The two should be equivalent! – Olivier Jacot-Descombes Mar 12 '14 at 20:51
  • Probably something else changed and you did not notice. I do not advocate this trial-and-error style of development. Change stuff until it happens to work. – usr Mar 12 '14 at 20:52
  • yes it is strange for me too but i was trying since 1 month with the preceding condition but it didnt work at all but now it is working after changing condition style – Kretos Mar 13 '14 at 19:00