My decimal is being rounded up by SQL???

[Bruce Duncan]

I have a form in an ASP page that the user enters a price
for. When I send the parameter to my SQL stored Proc and
refresh the data, it gets rounded up to the nearest
integer. I can change the data just fine in SQL but not
by using my asp page. Can anyone point me in the correct
direction? Below is my code. TIA
lNewPrice = Request.Form("txtprice")
if isempty(lNewPrice) then
lNewPrice = ""
end if

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Session("strConn")
Set oCmd = Server.CreateObject("ADODB.Command")
set oCmd.ActiveConnection = oConn
oCmd.CommandText = "spEditSupply"
oCmd.commandtype = AdCmdStoredProc
set RetailParam = oCmd.CreateParameter("@NewRetail",
adDecimal, adParamInput)
RetailParam.Precision = 10
RetailParam.NumericScale = 2
oCmd.Parameters.Append RetailParam
oCmd.Parameters("@NewRetail") = lNewPrice
oCmd.Execute

** The script executes without errors. But when I
refresh the data the entered value of 44.95 is saved as
45.

-Bruce

[Aaron Bertrand - MVP]

Are you sure the stored procedure is using DECIMAL(10,2) for the parameter?

And that the table data type is defined the same?

And that you aren't applying CLng() or something before passing the data?

And did you try:

set RetailParam = oCmd.CreateParameter("@NewRetail", adDecimal,
adParamInput, 9, lNewPrice)
With RetailParam
.Precision = 10
.Scale = 2
End With
oCmd.Parameters.Append RetailParam

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"Bruce Duncan" <an*******@discussions.microsoft.com> wrote in message
news:11*****************************@phx.gbl...

I have a form in an ASP page that the user enters a price
for. When I send the parameter to my SQL stored Proc and
refresh the data, it gets rounded up to the nearest
integer. I can change the data just fine in SQL but not
by using my asp page. Can anyone point me in the correct
direction? Below is my code. TIA
lNewPrice = Request.Form("txtprice")
if isempty(lNewPrice) then
lNewPrice = ""
end if

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Session("strConn")
Set oCmd = Server.CreateObject("ADODB.Command")
set oCmd.ActiveConnection = oConn
oCmd.CommandText = "spEditSupply"
oCmd.commandtype = AdCmdStoredProc
set RetailParam = oCmd.CreateParameter("@NewRetail",
adDecimal, adParamInput)
RetailParam.Precision = 10
RetailParam.NumericScale = 2
oCmd.Parameters.Append RetailParam
oCmd.Parameters("@NewRetail") = lNewPrice
oCmd.Execute

** The script executes without errors. But when I
refresh the data the entered value of 44.95 is saved as
45.

-Bruce

Ahh...
The parameters of my stored procedure was just using
DECIMAL. I changed to DECIMAL (10,2) and it worked like
a charm.
Thanks for the heads up!

-Bruce

-----Original Message-----
Are you sure the stored procedure is using DECIMAL(10,2) for the parameter?
And that the table data type is defined the same?

And that you aren't applying CLng() or something before passing the data?
And did you try:

set RetailParam = oCmd.CreateParameter("@NewRetail", adDecimal,adParamInput, 9, lNewPrice)
With RetailParam
.Precision = 10
.Scale = 2
End With
oCmd.Parameters.Append RetailParam

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"Bruce Duncan" <an*******@discussions.microsoft.com> wrote in messagenews:11*****************************@phx.gbl...

I have a form in an ASP page that the user enters a price for. When I send the parameter to my SQL stored Proc and refresh the data, it gets rounded up to the nearest
integer. I can change the data just fine in SQL but not by using my asp page. Can anyone point me in the correct direction? Below is my code. TIA
lNewPrice = Request.Form("txtprice")
if isempty(lNewPrice) then
lNewPrice = ""
end if

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Session("strConn")
Set oCmd = Server.CreateObject("ADODB.Command")
set oCmd.ActiveConnection = oConn
oCmd.CommandText = "spEditSupply"
oCmd.commandtype = AdCmdStoredProc
set RetailParam = oCmd.CreateParameter("@NewRetail",
adDecimal, adParamInput)
RetailParam.Precision = 10
RetailParam.NumericScale = 2
oCmd.Parameters.Append RetailParam
oCmd.Parameters("@NewRetail") = lNewPrice
oCmd.Execute

** The script executes without errors. But when I
refresh the data the entered value of 44.95 is saved as
45.

-Bruce

.