Decimal Rounding When C#Decimal Inserted into sql

Question

4.00/5 (2 votes)

See more:

Can someone explain why my decimal is rounding when I insert it into sql database. Right before inserting it isn't rounded. I do not want it rounding, needs to be exact.

C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace StaffHours.cs
{
    public partial class James : Form
    {
        decimal james;
        public James()
        {
            InitializeComponent();
        }

        private void James_Load(object sender, EventArgs e)
        {
            string connectionstring;
            {
                if (Variables.SQLConnections.Default.LocalConnectionorInternetConnection == "Local")
                {

                    connectionstring = Variables.SQLConnections.Default.SQLConnectionRosterLocal;
                    SqlConnection sqlconnection = new SqlConnection(connectionstring);
                    
                    {
                        //monday
                        string select_monday = "select [Hours - Monday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where Monday = 'james'";
                        SqlCommand monday = new SqlCommand(select_monday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drmonday = monday.ExecuteReader();
                        while (drmonday.Read())
                        {
                            if (drmonday.HasRows == true)
                            {
                                james = (drmonday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();
                        //tuesday
                        string select_tuesday = "select [Hours - Tuesday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where Tuesday = 'james'";
                        SqlCommand tuesday = new SqlCommand(select_tuesday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drtuesday = tuesday.ExecuteReader();
                        while (drtuesday.Read())
                        {
                            if (drtuesday.HasRows == true)
                            {
                                james = james + (drtuesday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();
                        //wednesday
                        string select_wednesday = "select [Hours - wednesday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where wednesday = 'james'";
                        SqlCommand wednesday = new SqlCommand(select_wednesday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drwednesday = wednesday.ExecuteReader();
                        while (drwednesday.Read())
                        {
                            if (drwednesday.HasRows == true)
                            {
                                james = james + (drwednesday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();
                        //thursday
                        string select_thursday = "select [Hours - thursday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where thursay = 'james'";
                        SqlCommand thursday = new SqlCommand(select_thursday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drthursday = thursday.ExecuteReader();
                        while (drthursday.Read())
                        {
                            if (drthursday.HasRows == true)
                            {
                                james = james + (drthursday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();
                        //friday
                        string select_friday = "select [Hours - friday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where friday = 'james'";
                        SqlCommand friday = new SqlCommand(select_friday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drfriday = friday.ExecuteReader();
                        while (drfriday.Read())
                        {
                            if (drfriday.HasRows == true)
                            {
                                james = james + (drfriday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();
                        //saturday
                        string select_saturday = "select [Hours - saturday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where saturday = 'james'";
                        SqlCommand saturday = new SqlCommand(select_saturday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drsaturday = saturday.ExecuteReader();
                        while (drsaturday.Read())
                        {
                            if (drsaturday.HasRows == true)
                            {
                                james = james + (drsaturday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();
                        //sunday
                        string select_sunday = "select [Hours - sunday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where sunday = 'james'";
                        SqlCommand sunday = new SqlCommand(select_sunday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drsunday = sunday.ExecuteReader();
                        while (drsunday.Read())
                        {
                            if (drsunday.HasRows == true)
                            {
                                james = james + (drsunday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();

                        //updates users hours
                        MessageBox.Show(james.ToString());
                        SqlConnection con2 = new SqlConnection(connectionstring);
                        SqlCommand sqlcommand3 = con2.CreateCommand();
                        sqlcommand3.CommandText = "UPDATE [dbo].['" + "HoursTotalTable_" + Variables.HoursTableVariables.Default.hoursdate + Variables.SiteVariables.Default.sitename + "'] SET [HoursWorked] = @hoursworked WHERE StaffNumber = 52637";
                        sqlcommand3.Parameters.Add("@hoursworked", SqlDbType.Decimal).Value = james;
                        con2.Open();
                        sqlcommand3.ExecuteNonQuery();
                    }
                }
                else if (Variables.SQLConnections.Default.LocalConnectionorInternetConnection == "internet")
                {
                    connectionstring = Variables.SQLConnections.Default.SQLConnectionRosterInternet;
                    SqlConnection sqlconnection = new SqlConnection(connectionstring);

                    {
                        //monday
                        string select_monday = "select [Hours - Monday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where Monday = 'james'";
                        SqlCommand monday = new SqlCommand(select_monday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drmonday = monday.ExecuteReader();
                        while (drmonday.Read())
                        {
                            if (drmonday.HasRows == true)
                            {
                                james = (drmonday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();
                        //tuesday
                        string select_tuesday = "select [Hours - Tuesday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where Tuesday = 'james'";
                        SqlCommand tuesday = new SqlCommand(select_tuesday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drtuesday = tuesday.ExecuteReader();
                        while (drtuesday.Read())
                        {
                            if (drtuesday.HasRows == true)
                            {
                                james = james + (drtuesday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();
                        //wednesday
                        string select_wednesday = "select [Hours - wednesday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where wednesday = 'james'";
                        SqlCommand wednesday = new SqlCommand(select_wednesday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drwednesday = wednesday.ExecuteReader();
                        while (drwednesday.Read())
                        {
                            if (drwednesday.HasRows == true)
                            {
                                james = james + (drwednesday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();
                        //thursday
                        string select_thursday = "select [Hours - thursday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where thursay = 'james'";
                        SqlCommand thursday = new SqlCommand(select_thursday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drthursday = thursday.ExecuteReader();
                        while (drthursday.Read())
                        {
                            if (drthursday.HasRows == true)
                            {
                                james = james + (drthursday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();
                        //friday
                        string select_friday = "select [Hours - friday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where friday = 'james'";
                        SqlCommand friday = new SqlCommand(select_friday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drfriday = friday.ExecuteReader();
                        while (drfriday.Read())
                        {
                            if (drfriday.HasRows == true)
                            {
                                james = james + (drfriday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();
                        //saturday
                        string select_saturday = "select [Hours - saturday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where saturday = 'james'";
                        SqlCommand saturday = new SqlCommand(select_saturday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drsaturday = saturday.ExecuteReader();
                        while (drsaturday.Read())
                        {
                            if (drsaturday.HasRows == true)
                            {
                                james = james + (drsaturday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();
                        //sunday
                        string select_sunday = "select [Hours - sunday] from dbo.['" + "RosterTable_" + Variables.RosterVariables.Default.rosterdate + Variables.SiteVariables.Default.sitename + "'] where sunday = 'james'";
                        SqlCommand sunday = new SqlCommand(select_sunday, sqlconnection);
                        sqlconnection.Open();
                        SqlDataReader drsunday = sunday.ExecuteReader();
                        while (drsunday.Read())
                        {
                            if (drsunday.HasRows == true)
                            {
                                james = james + (drsunday.GetDecimal(0));
                            }
                        }
                        sqlconnection.Close();

                        //updates users hours

                        SqlConnection con2 = new SqlConnection(connectionstring);
                        SqlCommand sqlcommand3 = con2.CreateCommand();
                        sqlcommand3.CommandText = "UPDATE [dbo].['" + "HoursTotalTable_" + Variables.HoursTableVariables.Default.hoursdate + Variables.SiteVariables.Default.sitename + "'] SET [HoursWorked] = @hoursworked WHERE StaffNumber = 52637";
                        sqlcommand3.Parameters.Add("@hoursworked", SqlDbType.Decimal).Value = james;
                        con2.Open();
                        sqlcommand3.ExecuteNonQuery();
                    }
                }
                this.Close();
            }
        }
    }

Posted 31-Oct-13 21:38pm

Member 10316721

Add a Solution

Comments

Kenneth Haugland 1-Nov-13 3:44am

Perhaps is cast as a Double? And please cut code down to the relevant section. :-)

Member 10316721 1-Nov-13 5:11am

thought it may help if you saw it all (might make more sense). Sorry

3 solutions

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)

Rakesh Meel · Answer 1 · 2013-10-31T22:26:00

Solution 1

visit here..

http://social.msdn.microsoft.com/Forums/en-US/145d2b1c-793a-4d3e-8cbf-ac9bcc6dd273/rounding-c-decimal-values-to-sql-decimal-or-numeric-fields?forum=adodotnetentityframework[^]

Posted 31-Oct-13 22:26pm

Rakesh Meel

Member 10316721 · Answer 2 · 2013-10-31T23:10:00

Solution 2

I have looked at that site. Didn't answer what I am after.

Posted 31-Oct-13 23:10pm

Member 10316721

sankarsan parida · Answer 3 · 2013-11-01T03:01:00

DECIMAL(18,0) will allow 0 digits after the decimal point.

SQL

You should use is as follows:

DECIMAL(m,a)
m is the number of total digits your decimal can have.

a is the max number of decimal points you can have.

See this LINK for more details
http://developer.mimer.com/documentation/Mimer_SQL_Reference_Manual/Syntax_Rules4.html[^]