SqlParameter decimal bug? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

SqlParameter decimal bug?

Author

Topic

mikica
Starting Member

9 Posts

Posted - 2007-04-25 : 10:42:47

In c# I have a command which text is:

BEGIN

SELECT * FROM MY_TABLE WHERE VALUE > @myParam1

END

to that command I added param:
Name = @myParam1
Precision = 15
Scale = 2

if I put 99.99 as Value it works.
if I put 99.001 as Value it works.
If I put 99.00 I receive error 99.00 is out of range!!!!!!!!

Why does Ado.Net does not see those .00 as decimal values?

I should be able to set value 99 and implicit conversion should add .00 to it since it is 2 scale decimal.

Can someone help me?

Is there any Dot.Net hotfix for this?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-25 : 10:55:18

We need to see actual code before we can help you, there are so many places so many things can do wrong that it is impossible to guess.

Just give us 5-6 lines of "proof of concept" code.

- Jeff
http://weblogs.sqlteam.com/JeffS

mikica
Starting Member

9 Posts

Posted - 2007-04-25 : 11:04:55

It looks like this:

//Command declaration
SqlCommand mySqlComm = (SqlCommand)_command;
mySqlComm.CommandType = CommandType.Text;
mySqlComm.CommandText = "INSERT INTO TABLE_T(COL_DEC_15_2) VALUES (@param1)";

//Parameter declaration
SqlParameter par = new SqlParameter();
par.ParameterName = "@param1";
par.Direction = ParameterDirection.Input;
par.SqlDbType = SqlDbType.Decimal;
par.Size = 9;
par.Scale = 2;
par.Value = 99.00;//DO NOT WORK
//par.Value = 99.99;-WORKS

mySqlComm.Parameters.Add(par);

mySqlComm.ExecuteNonQuery();

Thanks for help.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-25 : 11:32:32

why are reusing an existing _command and casting it to a SQLCommand? It is a different command object (i.e., a generic DBCommand)? Why? Also -- since you are re-using an existing object, are you sure that it doesn't already have parameters defined ??

Try with a freshly created sqlcommand object. If that still doesn't work, show me the code and I'll run it myself and we'll get it figured out. Also, what version of .net are you using?

- Jeff
http://weblogs.sqlteam.com/JeffS

mikica
Starting Member

9 Posts

Posted - 2007-04-25 : 11:40:12

I made a little bit bigger system. So this is just extraction of code and I forgot to replace that(SqlCommand)_command with new SqlCommand() when tried to illustrate problem to you.

I'll create now whole new small application to test same situation. Working in VS2005 so its .net 2. Comming soon results of my test.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-25 : 11:48:35

Also -- don't forget that in C# you should suffix your literal with "m" to indicate that it is a decimal value, and not something else. I am not sure what datatype "99.0" gets implicitly converted to in C# w/o the "m" present, that may be causing your issue (maybe it interprets the 99.00 as an integer but not 99.99 ?).

Always better to specify.

So, be sure to try:

par.Value = 99.00m;

- Jeff
http://weblogs.sqlteam.com/JeffS

mikica
Starting Member

9 Posts

Posted - 2007-04-25 : 12:06:12

I prepared simple application that shows error and found out your suggestion with m. It works!!!!! But:

Value = 99.00m; works
Value = Convert.ToDecimal(99.00); don't- where is a problem?

Thanks, you made me a lot of help!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-25 : 12:21:59

>>Value = Convert.ToDecimal(99.00); don't- where is a problem?

If you can post this code in context along with the full error message and exactly where it occurs, it will be helpful to determine where the problem is.

- Jeff
http://weblogs.sqlteam.com/JeffS

mikica
Starting Member

9 Posts

Posted - 2007-04-25 : 12:29:23

This is a simple application on button click code. Everithing happens here:

//Read connection string from app.config with key="Default"
string _connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["Default"].ToString();

SqlConnection _conn = new SqlConnection(_connectionstring);

SqlCommand mySqlComm = new SqlCommand();;
mySqlComm.CommandType = CommandType.Text;
mySqlComm.CommandText = "INSERT INTO TEST_TABLE(SUMA) VALUES (@param1)";

//Parameter declaration
SqlParameter par = new SqlParameter();
par.ParameterName = "@param1";
par.Direction = ParameterDirection.Input;
par.SqlDbType = SqlDbType.Decimal;
par.Size = 9;
par.Scale = 2;
par.Value = 99.00;//DO NOT WORK

//par.Value = 99.001;//WORKS inserted as 99.00
//par.Value = 99.99;-WORKS
//par.Value = 99.00m;-WORKS
//par.Value = Convert.ToDecimal(99.00);Dont

mySqlComm.Parameters.Add(par);
_conn.Open();
mySqlComm.Connection = _conn;
mySqlComm.ExecuteNonQuery();
_conn.Close();

I think I have a picture now what can and what should not be done.

mikica
Starting Member

9 Posts

Posted - 2007-04-25 : 13:05:33

Onse again thanks a lot!!!!!!!!!!!!!!!!!!!!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources