CODEWITHSUNDEEP
c#sqldatabasedecimal
Noam Yizraeli
Noam Yizraeli

Reputation: 5394

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)

Upvotes: 0

Views: 1577

Answers (3)

Steve
Steve

Reputation: 216243

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));

Upvotes: 1

Hussain Mustafa
Hussain Mustafa

Reputation: 37

It is essential to understand the working behind decimal type in SQL. DECIMAL(size,d), as the size, determines the total length of the value including the numbers after the floating point, For Instance if you have a value '500000.00' and decimal(8,2) represents that you can only have a total of 8 digits, and only 2 digits are allowed after the decimal point.

In your case, the parameter size is DECIMAL (18, 4), that means only 4 floating point digits are allowed if a number has digits more than it will be truncated by sql server.

Upvotes: 1

Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34152

you screensize variable is DECIMAL (18, 4). So it can hold a maximum 18 digit number, and you are allowed to have 4 digits after floating point, and if you have more floating point digits, it will get rounded.

Upvotes: 1

Related Questions