Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
806 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

715 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