Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-07-13
7
Medium Priority
?
812 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

824 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