CODEWITHSUNDEEP
c#sqlasp.netsql-serversql-server-2008-r2
Nickso
Nickso

Reputation: 805

Save a decimal value into a numeric field SQL

I have the following in my code behind:

protected void SaveValue_Click(object sender, EventArgs e)
{
        ArticleDBHQ article = new ArticleDBHQ();
        article.Weight = Convert.ToDecimal(tbWeight.Text);
}

In my ArticleDBHQ class the member variable Weight is as follows:

public class ArticleDBHQ : Entity
{
    private decimal? weight;

    public decimal? Weight
    {
        get { return weight; }
        set { weight = value; }
    }
}

In my data access layer, when I want to save this to my database, I call a stored procedure with a SqlCommand like this:

SqlCommand cmd = new SqlCommand("MODIFY_ARTICLE");

I pass the value:

cmd.Parameters.Add("@weight", SqlDbType.Decimal).Value = (!Entity.Weight.HasValue ? SqlDecimal.Null : Entity.Weight.Value);

In my stored procedure, Weight is defined as follows:

    @weight DECIMAL, 
     ...
    p301_weight = @weight

And in the table p301 the column weight is defined as numeric.

I can't change the type of the table, how can I save a decimal value to a numeric? As I understand (and I proved it) that it saves only the integer part, so instead of saving 0,5 is saving 1 (plus is rounding up).

Maybe a convert? But how and where.

EDIT: field weight on table p301 is declared as numeric(9, 3)

Upvotes: 1

Views: 3545

Answers (1)

HasaniH
HasaniH

Reputation: 8392

You need to set the precision and scale on your decimal and numeric declarations in your stored procedure. If you don't when you assign 0.5 to your decimal it will already be converted to 1 and your numeric will also be one. See here

So your stored procedure should look like:

 @weight DECIMAL(9,3), 
 ...
 p301_weight = @weight

The precision and scale you use should match what's set on the column in the table. If the table can only do whole numbers then you'll have to do something like what @Shawn C suggested and convert it to a whole number by multiplying then dividing to go the other way.

Upvotes: 4

Related Questions