Link to home
Start Free TrialLog in
Avatar of matrix_aash
matrix_aashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

ASP Parameters with Decimals

Hi All,

I asked a question about SQL stored procedure with decimals and assigning numericscales and precision and got an answer of:

set p = eqCmd.CreateParameter(, 14, , 9, strData)
p.numericScale = 2
p.Precision = 10
eqCmd.Parameters.Append p


eqCmd = your command object
strData = the data you want to input

But I am unsure of how to implament it with my code below. The ones that are decimal are the parameter rows where the type value is 14.
e.g.
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("valuedonations", 14, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("over200don", 14, 2, -1, "0")

Can anyone help?
<%
Dim rsReport
Dim rsReport_cmd
Dim rsReport_numRows
 
Set rsReport_cmd = Server.CreateObject ("ADODB.Command")
rsReport_cmd.ActiveConnection = MM_connCCO_STRING
rsReport_cmd.CommandText = "{call dbo.MEP_Reporting(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}" 
rsReport_cmd.Prepared = true
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("dateenable", 5, 1, -1, setDateEnable)
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("datestart", 133, 1, -1, setDateStart)
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("region86", 5, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("region87", 5, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("region88", 5, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("region86don", 14, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("region87don", 14, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("region88don", 14, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("sourcere", 5, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("sourcencco", 5, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("sourcecm", 5, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("sourceredon", 14, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("sourcenccodon", 14, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("sourcecmdon", 14, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("addrchange", 5, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("totaldonations", 5, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("valuedonations", 14, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("over200", 5, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("under200", 5, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("over200don", 14, 2, -1, "0")
rsReport_cmd.Parameters.Append rsReport_cmd.CreateParameter("under200don", 14, 2, -1, "0")
%>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of matrix_aash

ASKER

Thanks, what does precision and numericscale actually mean?
For one of the values it is meant to be 8701.50 and with the precision it displays 201
Stuff like that is why I personally prefer FLOAT types to the Decimal types.  Still, if you specify correctly it should do fine.

From BOL:
p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

The precision & scale you are specifying should be fine.  You might want to check the definition in the underlying DB as that may be what's messing you up.
I just changed the Decimal value in my stored procdure from (18,0) to (10,2) but still coming up the same. Does the stored procedure below look right (its only a very small clip from the procedure)?

ALTER PROCEDURE [dbo].[PROCEDURENAME]
      @valuedonations decimal(10, 2) output
AS
BEGIN
      SET NOCOUNT ON;
        set @valuedonations = (SELECT SUM(ROUND(DONAMOUNT,2)) FROM MEPDATA)
END
When i execute the stored procedure the value of them are all right, i even get the right decimal points as one says 8611.50
So ... you're saying the procedure, when executed in Sql Management Studio, does what's expected.  But when called from your VB / ASP, you get funny answers?
Yea exactly whats happening
may i see the code where you're trying to read the values you get back in the ASP?
Sure, see code below. THat is one section. The rest is very similar.
<td>£<%=rsReport_cmd.Parameters("sourcecmdon").Value %> (<%If cint(rsReport_cmd.Parameters("valuedonations").Value) = 0 Then Response.Write("0") Else Response.Write(Round((cint(rsReport_cmd.Parameters("sourcecmdon").Value)/cint(rsReport_cmd.Parameters("valuedonations").Value))*100, 2)) End if %>%)</td>

Open in new window

I found out how to do it now. 1 more question, its outputing as something like 1542.5
Is there any code i can put it to always have 2 digits after the decimal point as it is currency (1542.50)

Thanks
Got it. Thanks for your help!!!!