Decimal being rounded in Insert statement

Hi - when I insert a decimal amount into my SQL Server 2000 database, it
rounds it up.
My table has a column:
customer_amount decimal 9
..and has Precision set at 18, and Scale set at 2.
I am grabbing the decimal value from a textbox on my form, and passing
it as a parameter in ASP.Net:
cmd.Parameters.Add(New SqlParameter("@.customer_amount",
CType(tbConfAmount.Text, Decimal)))
My SP is:
CREATE Procedure addCustAmount
(
@.customer_amount decimal
)
AS INSERT INTO tblCustomerAmount (customer_amount)
VALUES
(@.customer_amount)
If my text box says: 23.79, the value that ends up in my database is 24.
Can anyone please advise where I'm going wrong?
Thanks, Mark
*** Sent via Developersdex http://www.examnotes.net ***Mark
select cast (23.79 as decimal)
select cast (23.79 as decimal(5,2))
"Mark" <anonymous@.devdex.com> wrote in message
news:%23mCgVUpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi - when I insert a decimal amount into my SQL Server 2000 database, it
> rounds it up.
> My table has a column:
> customer_amount decimal 9
> ..and has Precision set at 18, and Scale set at 2.
> I am grabbing the decimal value from a textbox on my form, and passing
> it as a parameter in ASP.Net:
> cmd.Parameters.Add(New SqlParameter("@.customer_amount",
> CType(tbConfAmount.Text, Decimal)))
> My SP is:
> CREATE Procedure addCustAmount
> (
> @.customer_amount decimal
> )
> AS INSERT INTO tblCustomerAmount (customer_amount)
> VALUES
> (@.customer_amount)
> If my text box says: 23.79, the value that ends up in my database is 24.
> Can anyone please advise where I'm going wrong?
> Thanks, Mark
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You don't specify a Scale for the parameter for your stored procedure, so
that defaults to 0. Decimal without Precision or Scale is decimal(18,0) by
default. Just change the datatype of the parameter to decimal(18,2). You
might have to do that in the parameter definition in ASP.Net as well btw,
but my ADO.Net is a bit rusty.
Jacco Schalkwijk
SQL Server MVP
"Mark" <anonymous@.devdex.com> wrote in message
news:%23mCgVUpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi - when I insert a decimal amount into my SQL Server 2000 database, it
> rounds it up.
> My table has a column:
> customer_amount decimal 9
> ..and has Precision set at 18, and Scale set at 2.
> I am grabbing the decimal value from a textbox on my form, and passing
> it as a parameter in ASP.Net:
> cmd.Parameters.Add(New SqlParameter("@.customer_amount",
> CType(tbConfAmount.Text, Decimal)))
> My SP is:
> CREATE Procedure addCustAmount
> (
> @.customer_amount decimal
> )
> AS INSERT INTO tblCustomerAmount (customer_amount)
> VALUES
> (@.customer_amount)
> If my text box says: 23.79, the value that ends up in my database is 24.
> Can anyone please advise where I'm going wrong?
> Thanks, Mark
>
>
> *** Sent via Developersdex http://www.examnotes.net ***