My decimal is being rounded up by SQL???

Discussion in 'ASP General' started by Bruce Duncan, Feb 17, 2004.

  1. Bruce Duncan

    Bruce Duncan Guest

    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
     
    Bruce Duncan, Feb 17, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:114a001c3f596$7cb6cc40$...
    > 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, Feb 17, 2004
    #2
    1. Advertising

  3. Bruce Duncan

    Guest Guest

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

    wrote in message
    >news:114a001c3f596$7cb6cc40$...
    >> 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

    >
    >
    >.
    >
     
    Guest, Feb 17, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ven
    Replies:
    3
    Views:
    1,330
  2. Gary Wessle

    rounded to the nth decimal

    Gary Wessle, Mar 17, 2007, in forum: C++
    Replies:
    4
    Views:
    318
    Mark P
    Mar 20, 2007
  3. Gilbert Fine
    Replies:
    8
    Views:
    904
    Zentrader
    Aug 1, 2007
  4. Vitaliy
    Replies:
    1
    Views:
    478
    Peter Otten
    May 29, 2008
  5. valpa
    Replies:
    11
    Views:
    1,522
    Steven D'Aprano
    Mar 24, 2009
Loading...

Share This Page