Hello!  We're seeing some very odd results from the below code.  Under
certain conditions, ADO.NET will create commands that specify a
negative zero amount ("-0.00").  It's easy to reproduce.

This is a major issue in our application as we do lots of decimal math
and rely on SqlDataAdapter and SqlCommandBuilder to do our data
updates.  As a result, we have intermittent failure.  We compare the
supposedly "zero" value to 0 inside of a trigger.  When the value
reaches SQL Server as a "negative zero", some actions are undefined.

This is a potentially serious framework bug.  I'd like to see it
verified by someone at Microsoft.  Ideas for a workaround are welcome.
If no workaround, a T-Shirt or something would be nice!  :)

Thanks,
Jon

=========================

// Run in Visual Studio 2005 "Console Mode"
using System;
using System.Data;
using System.Data.SqlClient;

namespace BadZero {
   class Program {
      static bool IsDecimalZero(decimal input) {
         SqlConnection Connection = new SqlConnection(
            @"Data Source=localhost;Trusted_Connection=True");
         Connection.Open();
         // Send command to SQL to check the value against zero
         SqlCommand Command = new SqlCommand(
            "IF @p1 = 0 SELECT 'Equal' ELSE SELECT 'Not Equal'",
            Connection);
         SqlParameter Param = new SqlParameter("@p1",
            SqlDbType.Decimal);
         Param.Value = input;
         Command.Parameters.Add(Param);
         string Result = Command.ExecuteScalar() as string;
         // When BadZero is passed in, using SQL Profiler, we see
         // @p1 being set to:
         //    GoodZero: 0.00
         //    BadZero:  -0.00   (yes, MINUS 0.00)
         Connection.Close();
         return Result == "Equal";
      }

      static void Main(string[] args) {
         decimal GoodZero = 0.00m;
         decimal BadZero  = 0.00m - 0;
         // Look the same, but different internally
         Console.WriteLine("GoodZero:{0}   BadZero:{1}",
            decimal.GetBits(GoodZero)[3], decimal.GetBits(BadZero)[3]);

         bool GoodResult = IsDecimalZero(GoodZero);    // true
         bool BadResult  = IsDecimalZero(BadZero);     // false

         Console.WriteLine("GoodResult:{0}   BadResult:{1}",
            GoodResult.ToString(), BadResult.ToString());
      }
   }
}

Hi, recently we discovered the same bug. I do agree it's a serious issue and
in our case, an online leasing sales-system, it can even have a negative
economic impact on our  customer's business. However we can make a relatively
cheap workaround in the ORM mapper used, I understand it's hard to prevent it
on the program level. It should IMHO be fixed within the framework.

Ondřej

Ondřej Tučný wrote:

    I escalated it to MS. Haven't heard back about a solution.

        FB

And did you get a T-Shirt.

Cor

Show quoteHide quote

pdxfilter-goo***@yahoo.com wrote:

    Ok, I asked MS what's the cause and this is what I got back:

"SqlClient is still passing the decimal value it receives to SQL
Server.  The difference in behavior is because the representation for
the "negative zero" changed between .NET 1.1 and .NET 2.0.

SQL Server 2005 has some added validation for incoming input and
translates the "negative zero" to the more standard zero."

    HTH,

        Frans


Show quoteHide quote

get primary key on insert
newbie: How to get rows in specific column?
How to stop TransactionScope from starting distributed transaction
Checking for Nulls in Fields
All or nothing for DataAdapter