weird problem with query...decimal.parse rounding in stored procedure, but not in query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

i have a stored procedure which pulls the value from column 1 based on the value of column 2. the value from column 1 is then output to the webpage which displays it in a label.

i have input variable bc and output variable fileName. if, for example, i type in 10.00 for the bc value, the fileSymbol spits out the correct value, in this case it's '0'.

if i use 9.00 as the input, the fileSymbol is correct.

if i use 9.7, however, the sproc rounds up to 10 and then gives me the INCORRECT fileSymbol

conversely, if i use 9.3 as the input, the sproc treats this as 9.0 and again, i get the incorrect symbol.

i'm very unsure of how to fix this.

here's my code and thank you for any help...

stored proc:

<pre lang="SQL">
ALTER PROCEDURE getFilename
(
	@bc decimal,
	@fileSymbol varchar(50) output
)

AS
SELECT @fileSymbol=fileSymbol
FROM tempSymTab
WHERE base = @bc

webpage:

ASP.NET

<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    
    <div>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <br />
        <br />
                        <asp:Label ID="Label1" runat="server"></asp:Label>  
        <br />
        <br />
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
    </div>
    </form>
</body>

codebehind (yes it's sloppy, i'm trying to test and make sure i get the right value first)

C#

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            string connSTR = ConfigurationManager.ConnectionStrings["connStr"].ToString();

            SqlConnection conn = new SqlConnection(connSTR);

            Class1 c1 = new Class1();



            SqlCommand cmd = new SqlCommand("dbo.getFileName", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@bc", SqlDbType.Decimal).Value = decimal.Parse(TextBox1.Text);

            SqlParameter parm2 = new SqlParameter("@fileSymbol", SqlDbType.VarChar);
            parm2.Size = 50;
            parm2.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(parm2);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            string outputValue = cmd.Parameters["@fileSymbol"].Value.ToString();
            Label1.Text = outputValue;
        
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string connSTR = ConfigurationManager.ConnectionStrings["connStr"].ToString();

        SqlConnection conn = new SqlConnection(connSTR);

        Class1 c1 = new Class1();
        
        

        SqlCommand cmd = new SqlCommand("dbo.getFileName", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@bc", SqlDbType.Decimal).Value = decimal.Parse(TextBox1.Text);

        SqlParameter parm2 = new SqlParameter("@fileSymbol", SqlDbType.VarChar);
        parm2.Size = 50;
        parm2.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(parm2);
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        string outputValue = cmd.Parameters["@fileSymbol"].Value.ToString();
        Label1.Text = outputValue;
        

    }

Posted 27-Nov-13 7:53am

memberxxxxxxxxxxxxxxxxx

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-11-27T08:06:00

Solution 1

Check this:

SQL

DECLARE @bc VARCHAR(10)

SET @bc = '9.1'
SELECT CONVERT(DECIMAL, @bc) As NewBC
SELECT CONVERT(DECIMAL(8,0), @bc) As NewBC
SELECT CONVERT(DECIMAL(8,2), @bc) As NewBC

Results:

9
9
9.1

Do you see the difference?

Please, have a look here: Data Types (T-SQL)[^]

Posted 27-Nov-13 8:06am

Maciej Los

Comments

memberxxxxxxxxxxxxxxxxx 27-Nov-13 14:27pm

honestly, i'm not sure. when building the table, i set the bc column as decimal(5,3) so that means 5 total numbers with 3 after the decimal for precision right? none of my bc values are more than 2 decimal places.

also, i just ran the program to the cursor and stopped here:
var dec = Convert.ToDecimal(TextBox1.Text);
cmd.Parameters.AddWithValue("@bc", SqlDbType.Decimal).Value = dec;*******cursor

i see that the var "dec" = 9.1 so it should give me the proper fileSymbol.

i then ran the cursor to here:

string outputValue = cmd.Parameters["@fileSymbol"].Value.ToString();
Label1.Text = outputValue;
and i see that the outputVal = "Q" which is incorrect.

Maciej Los 27-Nov-13 14:38pm

Have a look at your SP body and the declaration of @bc variable ;)

memberxxxxxxxxxxxxxxxxx 27-Nov-13 14:46pm

thank you very much!!!
so if i leave off the (5,3)...sql automatically uses precision 0? out of curiosity...why does the query "9.1" work in queryview, but not in runtime?

once again, thank you

Maciej Los 27-Nov-13 14:49pm

You're very welcome ;)