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

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
            {
                //
            }
        }
    }
}
crypto_monkeyAsked:
Who is Participating?
 
grayeConnect With a Mentor Commented:
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
 
grayeCommented:
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
 
grayeCommented:
...uh, I meant...  "with 4 digits to the RIGHT of the decimal point"
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
crypto_monkeyAuthor Commented:
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
 
crypto_monkeyAuthor Commented:
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
 
crypto_monkeyAuthor Commented:
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
 
crypto_monkeyAuthor Commented:
graye,

that worked for me. thanks for the help.
0
All Courses

From novice to tech pro — start learning today.