Solved

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

Posted on 2006-07-13
7
792 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Installing IIS after Visual Studio 2015 Community Edition 5 46
Programmatically signing Word macros 4 61
Problem to error 4 45
C# Json POSt to Rest API 4 37
Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

920 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