CODEWITHSUNDEEP
.netsqldecimalsqlparameter
LaserJesus
LaserJesus

Reputation: 8550

Why is the decimal SqlParameter getting mangled?

I'm sending a decimal value to a sproc in the following way:

SqlParameter meh = new SqlParameter("Foo", SqlDbType.Decimal);
meh.Value = "0.00001";
meh.Precision = ((System.Byte)(12));
meh.Scale = ((System.Byte)(9));

When I profile the database to see what's being run against it, I see the parameter like so:

....,@Foo decimal(12,9),....,@Foo=10000,....

It seems to be completely mirrored from the decimal point, how do I fix this? Note that I've also tried converting the string to an actual decimal first and using it to set 'meh.Value' but it still has the same problem.

Updated

I've noticed, as in the example above, that the original value has it's decimal point shifted to the right by 9 positions, exactly the same value as the scale. What would cause this?

Updated Again

I should note that I'm using Sql Server 2008

Upvotes: 5

Views: 2965

Answers (3)

user153923
user153923

Reputation:

I've encountered this problem as well.

The closest documented attempt by Microsoft to acknowledge the problem (that I've found) is here:

http://support.microsoft.com/?kbid=892406

Not a great answer, but help for your sanity.

EDIT: I had a similar problem and found out just yesterday that updating the SqlParameter all day long did nothing because it was lost whenever I added that parameter to my SqlCommand. Instead, edit the SqlParameter within the SqlCommand to set the precision you need:

void parameterCheck(SqlCommand cmd, object value, int index, SqlDbType dataType) {
  cmd.Parameters[index].Value = value;
  if (dataType == SqlDbType.Decimal) {
    cmd.Parameters[index].Precision = 12;
    cmd.Parameters[index].Size = 9;
  }
}

Upvotes: 3

Tim
Tim

Reputation: 5421

Why are you wrapping the decimal value in quotation marks, turning it into a string?

Upvotes: 0

user547541
user547541

Reputation:

Why are you using System.Byte explicitly for SqlParameter Precision and Scale? The code should just be like this :

SqlParameter meh = new SqlParameter("Foo", SqlDbType.Decimal);
meh.Value = 0.00001;
meh.Precision = 12;
meh.Scale = 9;

Upvotes: 4

Related Questions