Decimal value gets rounded when entering DB

I’m trying to upload a decimal value into a database.

Before the upload (while debugging C#) it’s decimal but when inserted to the database it’s the decimal is rounded so there aren’t any numbers but zero after the decimal point. Why?

variable declaration:

decimal screen_size = 0;
cmd.Parameters.Add(new SqlParameter("@devicename", SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlParameter("@batterylife", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@price", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@BasemarkX", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@ImageURL", SqlDbType.VarChar, int.MaxValue));
cmd.Parameters.Add(new SqlParameter("@year", SqlDbType.Int)); 
cmd.Parameters.Add(new SqlParameter("@screensize", SqlDbType.Int));

assignment:

screen_size = decimal.Parse(Regex.Match(screenSize, @"d+(.d+)?").Value);

uploading:

cmd.CommandText =
   "UPDATE Phones " +
   "SET price = @price, BasemarkX = @BasemarkX, year = @year, batterylife = @batterylife, screensize = @screensize " +
   "WHERE devicename = @devicename";

    //set parameters values
    cmd.Parameters["@devicename"].Value = model;
    cmd.Parameters["@batterylife"].Value = batterylife;
    cmd.Parameters["@price"].Value = price;
    cmd.Parameters["@BasemarkX"].Value = bench;
    cmd.Parameters["@year"].Value = year;
    cmd.Parameters["@ImageURL"].Value = imgURL;
    cmd.Parameters["@screensize"].Value = screen_size;

    cmd.ExecuteNonQuery();

DB:

CREATE TABLE [dbo].[Phones] (
[Id]          INT             IDENTITY (1, 1) NOT NULL,
[devicename]  NVARCHAR (50)   NULL,
[batterylife] INT             DEFAULT ((0)) NULL,
[price]       INT             DEFAULT ((0)) NULL,
[BasemarkX]   INT             DEFAULT ((0)) NULL,
[year]        INT             DEFAULT ((0)) NULL,
[ImageURL]    NVARCHAR (MAX)  NULL,
[screensize]  DECIMAL (18, 4) DEFAULT ((0)) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

clarification: I’m trying to upload a decimal number with usually one digit of each end of the dot max two at each side (the test in which it didn’t work was with only one digit on each end)

Answer

The problem is caused by your definition of the parameter @screensize
You have declared it to be of type SqlDbType.Int and, even if, you can set the Value property of the parameter to a value of a different type (like a decimal), when you send that parameter to the database engine it will be rounded to the nearest integer and you see that value in your database table.

When using parameters is of uttermost importance to give them the datatype expected in the database table.

Declare that parameter as

cmd.Parameters.Add(new SqlParameter("@screensize", SqlDbType.Decimal));

Advertisement

Answer