SQLParameter Bug?

[m.moebius]

I have a strange behaviour. Perhaps someone can look through if there
is anything obvious wrong.

The field ProjAdmin is a tinyint

SqlConnection con = CreateConnection(); con.Open();

string sql =" UPDATE tblEmployee " +
" SET ProjAdmin = @param WHERE (EmployerID = 1002)";

SqlCommand cmd = new SqlCommand(sql,con);
SqlParameter para = cmd.Parameters.Add("@param",0);

//uncomment to get it working
//para.Value = 0;

cmd.ExecuteNonQuery();

The above throws the following error:
Prepared statement '(@param bigint) UPDATE tblEmployee SET ProjAdmin =
@param WHERE' expects parameter @param, which was not supplied.

The funny thing is, after uncommenting par.Value = 0; all is working
fine.
Also exchanging 0 with any other number works without the
para.Value = 0 statement.
Only when updating with 0 the error is thrown.

So I would consider this a bug. I would be interested to hear what is
going on there. A short look with Reflector gave me the impression that
Add("param",value) is equal to para.name = "param"; para.Value = value;

SELECT @@VERSION
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
..net 1.1.4322

 

[Guest]

Played around with it a bit and it seems that 0 is the issue. WHen I run
other numbers, everything works without error. Try:

SqlParameter para = cmd.Parameters.Add("@param", new SqlInt64(0));

This explicitly types the bigint value so there is no confusion.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Rogas69]

Hi,

Is it tinyint or bigint? I do not deny it is a bug, but maybe related to
memory size of parameter?

Peter

 

[m.moebius]

So the boxing automagic is to blame here?
Why did you use the 8byte SQLInt? Did you just read the "bigint" inside
the error and gave it a try?

@Peter
The field-type in the table is tinyint (1byte). But the error message
brings the bigint into.
I do not know what the CLR does when having an Int32(?) in the hand and
a object as the target. I would expect that 0 and 1 would fit in the
same number of bytes, so it should be same datatype. Hm ilasm could
clarify that.

 

[W.G. Ryan eMVP]

It's not a bug. 0 is an enum value so it uses a different overload. I know
I found this in the documentation before, I'll see if I cna find the
specific link to it for you

 

[m.moebius]

It's not a bug. 0 is an enum value so it uses a different overload. I know

I found this in the documentation before, I'll see if I cna find the
specific link to it for you

Sounds reasonable. There is an overloaded method

public SqlParameter Add(
string parameterName,
SqlDbType sqlDbType
);

So the int would be used as the SqlType. Good to know.

 

[Nick Gilbert]

SqlParameter para = cmd.Parameters.Add("@param",0);

Do you realise that the 0 here is the SqlDbType and not the value? You
shouldn't be putting an int there at all (for readability reasons). Use
SqlDbType.Int32 and set the value seperately using .Value = 0

Nick...

 

[m.moebius]

No I didnt notice that unitl Ryan mentioned the overloaded method.
The API is a bit awkward on that aspect imo. It would be better to
remove all the Add-methods where a parameter value is passed. So the
use of Add(para,type).Value=value would be promoted.