SqlParameter not storing false BIT value

Question

It would seem I need help getting to the bottom of what should be a simple problem. I am updating some existing code and I have a stored procedure that has nullable paramters and updates a table.

Example SP Parameters

       ,@Paid bit = NULL
       ,@Despatched bit = NULL

SP Code Example

  ,Paid = ISNULL(@Paid, Paid)
  ,Despatched = ISNULL(@Despatched, Despatched)
  ,MarkedAsPaid = ISNULL(@MarkedAsPaid, MarkedAsPaid)
  ,MarkedAsDespatched = ISNULL(@MarkedAsDespatched, MarkedAsDespatched)

C# Code

   cmd.Parameters.Add(new SqlParameter("@Despatched", 0));
   cmd.Parameters.Add(new SqlParameter("@Paid", 0));

   if (request.ShippedDate != null && request.ShippedDate != DateTime.MinValue.ToString())
   {
       cmd.Parameters["@Despatched"].Value = 1;
   }

   if (request.PaymentMethod == "PayPal" && request.CheckOutStatus == "NoPaymentFailure")
   {
       cmd.Parameters["@Paid"].Value = 1;
   }

When I run this code and the above if conditions are not met the field values remain as is (NULL) if they are met the field is updated to 1. Any idea why they are not being updated to 0 when the conditions are not met?

score 3 · Accepted Answer · edited May 23 '17 at 12:07

3

This line

cmd.Parameters.Add(new SqlParameter("@Despatched", 0));

doesn't mean that the passed value is 0, instead it represents the SqlDbType enum value. . (which is BigInt). Since 0 is implicitly convertible to any enum type, you don't see an error.

Add the parameter like:

cmd.Parameters.Add(new SqlParameter("@Despatched",SqlDbType.Bit));

and then add an else statement like:

 if (request.ShippedDate != null && request.ShippedDate != DateTime.MinValue.ToString())
 {
     cmd.Parameters["@Despatched"].Value = 1;
 }
 else
 {
    cmd.Parameters["@Despatched"].Value = 0;
 }

Or you can keep your current code and add the default value like:

cmd.Parameters.Add(new SqlParameter("@Despatched",SqlDbType.Bit){ Value = 0 });

edited May 23 '17 at 12:07

Community

1
1

answered Sep 09 '14 at 13:12

Habib

219,104
29
407
436

1

Perfect! What I find odd is that it seems to be only the `BIT` fields in the table that this is effecting. I did try the else on the `if` but still had `cmd.Parameters.Add(new SqlParameter("@Despatched",0));`. My next step was to change all the paramters to `cmd.Parameters.AddWithValue("@Despatched",0);` which was a lot of work given the amount of changes. Anyway this has solved my problem :) Thanks – Fred Sep 09 '14 at 13:28

SqlParameter not storing false BIT value

1 Answers1