Solved

C# 2.0 TableAdapter: Values of type double are changed/ rounded when INSERT command is executed

Posted on 2006-07-13
7
790 Views
Last Modified: 2008-01-09
I am having an issue with double values being changed/ rounded when they are inserted into a SQL Server 2000 table using the C# 2.0 TableAdapter’s “INSERT” command. I have an array of 3 elements of type double: {0.0371, 0.0495, 0.0984}. I am using code to iterate through the array and insert each of those three values into the “data” column of my “Totals” table. The “Totals” table has three columns: name (type=nvarchar/50), date (type=datetime/8), data (type=float/8). I am using a TableAdapter for this table, and the column in question is displayed in the Visual Studio 2005 designer as being type “System.Double.”

The problem is when I execute an INSERT command for the TableAdapter, the number inserted is not the same as the original: it has been changed/ rounded. For example, here’s what those three double values look like once inserted into my table:
     0.0371   becomes   0.0370999984443188
     0.0495   becomes   0.0494999997317791
     0.0984   becomes   0.098300002515316

How do I get my original values to be inserted without this change/ rounding taking place?

Here’s some code to help illustrate:
---------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.IO;
using MyDataAccess;               // Data access layer in a .dll library
using MyDataAccess.TotalsDataSetTableAdapters;             // tableadapters

namespace ImportArray
{
    public partial class Form1 : Form
    {
       
        private double[] myArray = new double[] { 0.0371, 0.0495, 0.0984 };

        public Form1()
        {
            InitializeComponent();
        }


        private void Form1_Load(object sender, EventArgs e)
        {
            InsertThisArray(myArray);
        }


        public void InsertThisArray(double[] arrayToImport)
        {
            try
            {
                TotalsTableAdapter tAdapter = new TotalsTableAdapter();

                foreach (double myData in arrayToImport)
                {
                    tAdapter.Insert(myData);
                // This INSERT command inserts a value into
                  // the “data” column of the “Totals” table.
                }

            }
            catch (Exception ex)
            {
                //handle any errors
            }
            finally
            {
                //
            }
        }
    }
}
0
Comment
Question by:crypto_monkey
  • 4
  • 3
7 Comments
 
LVL 41

Expert Comment

by:graye
ID: 17100785
I'd use the Decimal type in SQL Server...   Decimal(5,4) would be 5 digits wide, with 4 digit to the left of the decimal point.
0
 
LVL 41

Expert Comment

by:graye
ID: 17101928
...uh, I meant...  "with 4 digits to the RIGHT of the decimal point"
0
 

Author Comment

by:crypto_monkey
ID: 17102824
graye:  Can you give me more info on how I'd implement the Decimal(5,4) setting? What I mean is this: I know how to change the "type" on a column in SQL Server, but how do I specify overall length and # of digits to the right of the decimal point?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:crypto_monkey
ID: 17102886
More info on this scenario:

I found a puzzling "workaround" that will place the numbers in their fields correctly. I used a SQLConnection object and a SQLCommand object to execute an INSERT command instead of the TableAdapter. I also changed the array from type "double" to type "float." The commandText for the command object performs the exact same INSERT command thaat the TableAdapter uses, and now it works. WHY?? Especially when the "data" column in the "Totals" table is listed as being type "System.Double"?? I absolutely *don't* intend to use this workaround as my solution, especially because one *should* be able to use the INSERT command from the TableAdapter.

Any more ideas as to the reason behind my problem?
0
 
LVL 41

Accepted Solution

by:
graye earned 500 total points
ID: 17104427
Float and Doubles are consider "approximates"... whereas the Decimal type is considered precise.   The Decimal type will allow you to control the rounding (which is what you want, right?)

I was suggesting that you alter the data type for the column in the underlying database (forget about Visual Studio for a minute...)  I'd use the "Enterprise Manager" of SQL Server 2000 to alter the type for the that column to "Decimal(5,4)", or you could type the following at the SQL prompt:

    Alter table TableNameGoesHere alter ColumnNameGoesHere Decimal(5,4)
0
 

Author Comment

by:crypto_monkey
ID: 17125207
graye,

thanks for the clarification. I am not at my development machine but I will try your suggestion within the next day or so and let you know how it goes.
0
 

Author Comment

by:crypto_monkey
ID: 17140336
graye,

that worked for me. thanks for the help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now