SQLServer saves Decimal as Integer

  • Thread starter Thread starter gaius
  • Start date Start date
G

gaius

I am using the Data Access Application Block for a data
interface tier in my application. All data goes to and
from the database (MSDE) without a problem, except for
decimal numbers which get stored as integers. I can set
the numbers to decimal values using the Server Explorer,
but the next time I save them they are converted to
integers again.

I use the following command to update the database:
SqlHelper.ExecuteNonQuery
(conn2,CommandType.StoredProcedure,strCmd,sqlParms);

The decimal numbers are passed into the method as
follows:
sqlParms[5] = new SqlParameter("@WeightPerPiece",
SqlDbType.Decimal, 8);
sqlParms[5].Value = 7.5;

This is always stored as 8. What is wrong?
 
gaius said:
I am using the Data Access Application Block for a data
interface tier in my application. All data goes to and
from the database (MSDE) without a problem, except for
decimal numbers which get stored as integers. I can set
the numbers to decimal values using the Server Explorer,
but the next time I save them they are converted to
integers again.

I use the following command to update the database:
SqlHelper.ExecuteNonQuery
(conn2,CommandType.StoredProcedure,strCmd,sqlParms);

The decimal numbers are passed into the method as
follows:
sqlParms[5] = new SqlParameter("@WeightPerPiece",
SqlDbType.Decimal, 8);
sqlParms[5].Value = 7.5;

This is always stored as 8. What is wrong?
Click to expand...

Have you called your stored procedure using Query Analyzer to make sure the
problem doesn't lie with the sproc itself? Quite often I've seen sprocs
written where a decimal parameter was defined incorrectly (wrong
scale/precision) or accidentally defined as an int.

If you can run the stored procedure from QA and the value is stored
correctly, then you can point the finger at the C# code. But given what
you've shown above, I'd definitely look again at the sproc itself.

Good luck,
Ryan LaNeve
 
gaius said:
I am using the Data Access Application Block for a data
interface tier in my application. All data goes to and
from the database (MSDE) without a problem, except for
decimal numbers which get stored as integers. I can set
the numbers to decimal values using the Server Explorer,
but the next time I save them they are converted to
integers again.

I use the following command to update the database:
SqlHelper.ExecuteNonQuery
(conn2,CommandType.StoredProcedure,strCmd,sqlParms);

The decimal numbers are passed into the method as
follows:
sqlParms[5] = new SqlParameter("@WeightPerPiece",
SqlDbType.Decimal, 8);
sqlParms[5].Value = 7.5;

This is always stored as 8. What is wrong?
Click to expand...

Have you tried setting the value to 7.5m instead of 7.5? That would be
the C# decimal value instead of the double value. Just a guess
though...
 
Thanks Ryan, you were right. The problem was in the definition of the
ContainerWeight varialbe in the stored procedure. I had specified it as
follows:
@ContainerWeight decimal

I needed to provide the scale and precision as follows:
@ContainerWeight decimal(7,3)
 
Back
Top