SQLServer saves Decimal as Integer

[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?

[Ryan LaNeve]

"gaius" <jd******@ieee.org> wrote in message
news:05****************************@phx.gbl...

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?

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

[Jon Skeet [C# MVP]]

gaius <jd******@ieee.org> wrote:

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?

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

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Jack Donahoe]

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)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!