Why does
scIns.Parameters.Add(new SqlParameter("@v30", 0.00m));
lead to a "@v30 undefined" error, but
decimal dZero = 0.00m;
scIns.Parameters.Add(new SqlParameter("@v30", dZero));
works OK?
-
3Are you sure it's the only difference? It shouldn't matter whether or not you use an intermediate variable...Thomas Levesque– Thomas Levesque2011-06-30 20:02:40 +00:00Commented Jun 30, 2011 at 20:02
-
2Can you show us the entire piece of code?Abe Miessler– Abe Miessler2011-06-30 20:04:11 +00:00Commented Jun 30, 2011 at 20:04
-
Yes, the mentioned error is not in the posted code but must be somewhere elseRahul– Rahul2011-06-30 20:05:48 +00:00Commented Jun 30, 2011 at 20:05
-
Actually, that is the only change in the code.SeaDrive– SeaDrive2011-06-30 20:36:56 +00:00Commented Jun 30, 2011 at 20:36
-
@SeaDive, what are you using to Execute the command? Please show a bit more code.Jethro– Jethro2011-06-30 21:02:04 +00:00Commented Jun 30, 2011 at 21:02
Add a comment
|
2 Answers
SqlParamter has two different overloads with two parameters, first being string and second being SqlDbType or object.
In the first case when 0.00m gets passed in it gets converted to Int64, and in the second case because the value passed in is of type decimal it then used the SqlDbType.Decimal.
Check out this Link
Update :
Found another stackoverflow Question that talks about this and has a detailed answer. :)
2 Comments
Matti Virkkunen
It seems there's a quirk in C#'s overload resolution that I was not aware of. A literal zero of any numeric type matches and gets implicitly converted into any
enum type (whether the enum even contains a zero value or not). Other values, however, don't get converted.
Jethro
I thought there was something funny like that, I was busy going through System.Data with reflector trying to see if the SqlParameter class does any conversions. Thanks for the update Matti!
If you look in the debugger, you will see that in the first case, the parameter's DbType is Int64, and in the second case it's Decimal.
Try using one of the other SqlParameter ctor overloads where you explicitly specify the SqlDbType.
Hope that helps,
John
5 Comments
Matti Virkkunen
How could this even happen? The constructors are passed the exact same value in both cases.
SeaDrive
Thanks. I guess I see what's going on, and how to fix it, but it seems more trouble than the original workaround. I also had trouble with a object value of "".
SeaDrive
As I understand it, what JohnD is pointing out is that the constructor that takes the arguments of (string, object) basically guesses at the type of the object. Somehow, the decimal 0.00m was changed to an int (by C# or by SqlServer?), so the SqlCommand expected an int, and rejected a decimal. Apparently, using the typed variable obviates the problem. One can also use other constructors that pass the SqlDBType as a separate argument.
Matti Virkkunen
@SeaDrive: No, no. The compiler doesn't just go and randomly change the types of values without a reason. See pastebin.com/dX8iat06
Jethro
@Matti Virkkunen, please see my answer below and look at the link to get a better exp of why this happens.