Unable to insert decimal to sql server table through c# code

Question

I'm not able to insert decimal values into Sql Server table.

What i'm trying to do is self explanatory through these lines of code:

long fileSizeInBytes = ComponentUploadControl.FileContent.Length;
Decimal fileSizeInMB = Convert.ToDecimal(fileSizeInBytes) / (1024.0m * 1024.0m);
Decimal fileSizeInMBRounded = Math.Round(fileSizeInMB, 2);
cmd.Parameters.Add("@fileSize", SqlDbType.Decimal).Value = fileSizeInMBRounded;

The value that is getting inserted into database is stripped of the decimal places. To be more specific if the fileSizeInMBRounded is 11.73, the value that is getting inserted into database is 11,00

Please help me

Thanks in anticipation

The mistake is in the stored procedure i was specifying the parameter type as simply decimal instead it should be decimal(18,2). Any ways now it works like a charm after i corrected the mistake — Sreedhar Danturthi
– Sreedhar Danturthi, Commented May 2, 2011 at 15:17

pseudocoder · Accepted Answer · 2011-05-02 14:40:58Z

9

My suspicion is that your decimal field is set up wrong in SQL. What you want is a decimal(18,2) field which allows for 2 decimal places. My suspicion is that you declared the field as decimal(18,0) (the default), which does not allow for any decimal places.

If you can use SQL Server Profiler to verify the contents of the INSERT going to your DB, it would be easier for you to determine whether a problem like this is due to your code or something on the SQL server.

answered May 2, 2011 at 14:40

pseudocoder

4,4022 gold badges28 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sreedhar Danturthi Over a year ago

Here is the mistake when i was inserting the data using the stored procedure,Create procedure [dbo].[NewComponentInsertion] (@componentName varchar(max), @fileSize decimal, @filePath varchar(max), @price decimal, @description varchar(max), @totalDownloads int, @disableFlag int) as insert into [dbo].[Components]([Component Name], [Size (in MB)],[Total Downloads], [FilePath], [Price (in SEK)], Description, DisableFlag) values (@componentName, @fileSize, @totalDownloads, @filePath, @price, @description, @disableFlag) I was inserting the decimal plainly without specification

Sreedhar Danturthi Over a year ago

it should be instead decimal(18,2), so the altered stored procedure is ,Create procedure [dbo].[NewComponentInsertion] (@componentName varchar(max), @fileSize decimal(18,2), @filePath varchar(max), @price decimal(18,2), @description varchar(max), @totalDownloads int, @disableFlag int) as insert into [dbo].[Components]([Component Name], [Size (in MB)],[Total Downloads], [FilePath], [Price (in SEK)], Description, DisableFlag) values (@componentName, @fileSize, @totalDownloads, @filePath, @price, @description, @disableFlag)

pseudocoder Over a year ago

I see, so it was the parameter that was not declared correctly. Same concept though. Glad I could help!

Bala R · Accepted Answer · 2011-05-02 14:31:15Z

4

Try to set the SqlParameter.Scale to 2.

SqlParameter parameter = new SqlParameter("@fileSize", SqlDbType.Decimal);
parameter.Scale = 2;
parameter.Value = fileSizeInMBRounded;
cmd.Parameters.Add(parameter);

Also look into Precision property as suggested by Bruno (default is 0).

answered May 2, 2011 at 14:31

Bala R

109k23 gold badges203 silver badges212 bronze badges

1 Comment

Sreedhar Danturthi Over a year ago

as pointed out that was the error. Thanks for the advice though.

Bruno Costa · Accepted Answer · 2011-05-02 14:32:06Z

1

You should set Scale and Precision.

I think that is the problem.

answered May 2, 2011 at 14:32

Bruno Costa

2,7202 gold badges17 silver badges25 bronze badges

Comments

GenZiy · Accepted Answer · 2011-05-02 14:43:58Z

1

Your parameter looks fine. I would check the data base column data type

answered May 2, 2011 at 14:43

GenZiy

1,4573 gold badges15 silver badges20 bronze badges

1 Comment

Sreedhar Danturthi Over a year ago

You are right. Here is the mistake when i was inserting the data using the stored procedure,Create procedure [dbo].[NewComponentInsertion] (@componentName varchar(max), @fileSize decimal, @filePath varchar(max), @price decimal, @description varchar(max), @totalDownloads int, @disableFlag int) as insert into [dbo].[Components]([Component Name], [Size (in MB)],[Total Downloads], [FilePath], [Price (in SEK)], Description, DisableFlag) values (@componentName, @fileSize, @totalDownloads, @filePath, @price, @description, @disableFlag) I was inserting the decimal plainly without specification

Collectives™ on Stack Overflow

Unable to insert decimal to sql server table through c# code

4 Answers 4

3 Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related