Why is the decimal SqlParameter getting mangled?

Question

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

Stubbed

Have you tried just doing a select against the table afterwards and seeing what you get back from it? – Wingfooted

If the generated SQL actually runs against the database it throws the following error: "Error converting data type int to decimal." – Stubbed

Could it be an issue with locale? Perhaps it's interpreting the dot as a numeric separator like they use in the UK as we use a comma in the US. Since your value is a string that is being parsed it's possibly being parsed as 1 instead of 0.00001. – Augustaugusta

Answer 1

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;

Uri

It doesn't make any difference to the outcome – Stubbed

What if you set the precision and scale first, then the value? – Faires

Answer 2

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;
  }
}

Bernita

That article seems to be for SQL Server 2000, I'm actually using SQL Server 2008. I'll update the post to reflect this – Stubbed

Answer 3

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

Accoutre

The value is in string form due to its origin which isn't shown in the simplified code sample. I've checked the value in the debugger and it appears as I've shown. I've also tried explicitly parsing the value into a decimal first but I still get the same problem. – Stubbed