SQLParameter Bug?

  • Thread starter Thread starter m.moebius
  • Start date Start date
M

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
 
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!
***************************
 
Hi,

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

Peter
 
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.
 
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
 
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
Click to expand...

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.
 
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...
 
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.
 
Back
Top