CODEWITHSUNDEEP
.netsql-servervb.netstored-proceduressql-server-2008-r2
Sam Makin
Sam Makin

Reputation: 1556

Incorrect value being passed in to SQL from .NET sqlcommand

Why when I run the below code, which should insert a value of 122387593.6 into the table, does it insert a value of 122387593.59999999?

I have traced the call made by .net and can see the value is being passed in as 122387593.59999999 to SQL, yet while debugging the parameter value is returned as 122387593.6?!

I have not been able to work out why .NET is doing this - any help is greatly appreciated.

   ---SQL Code

   CREATE TABLE [dbo].[tblNum](
[Num] [numeric](28, 8) NOT NULL
   ) ON [PRIMARY]


   CREATE PROCEDURE spNumInsert (@Num numeric(28,8))
   AS
   BEGIN
   INSERT INTO tblNum VALUES(@Num)
   END
   GO

   --.NET Code

    Dim a = Double.Parse("122387593.6", Globalization.CultureInfo.InvariantCulture)

    Dim con As SqlConnection = New SqlConnection("Server=server;Database=database;Trusted_Connection=True;")
    Dim com As SqlCommand = New SqlCommand("spNumInsert", con)
    com.CommandType = CommandType.StoredProcedure
    com.Parameters.Add(New SqlParameter("@Num", a))

    con.Open()
    com.ExecuteNonQuery()
    con.Close()
    con.Dispose()

Upvotes: 3

Views: 459

Answers (4)

gbn
gbn

Reputation: 432521

Don't use Double.Parse. You need to use Decimal.

Your object a is already 122387593.59999999 because this is how 122387593.6 must be represented in the floating point standard, which is what double is.

Upvotes: 3

Piotr Stapp
Piotr Stapp

Reputation: 19828

The problem is with precision of double in .NET. If you checkout variable a after this line

Dim a = Double.Parse("122387593.6", Globalization.CultureInfo.InvariantCulture)

You will notice that it has already wrong value. You can try to use decimal it is more precise.

Upvotes: 1

Shafqat Masood
Shafqat Masood

Reputation: 2570

use function

  ROUND ( numeric_expression , length [ ,function ]  )

Upvotes: -3

Marc Gravell
Marc Gravell

Reputation: 1063774

double means IEEE754. IEEE754 cannot express every single value. It is quite possible that 122387593.6 does not exist in IEEE754, and that 122387593.59999999 is the nearest approximation.

If you need values that match what us meat-headed humans tend to agree on as the "exact" approximation (note the contradiction in terms), you should be using decimal, not double.

To be clear: both decimal and double are forced to approximate - there is only so much you can do to fit such a range of possible values into finite bytes. But the way they approximate is different. decimal is appropriate for discrete measurements like money; double is appropriate for continuous measurements.

Upvotes: 10

Related Questions