String "0.080" to double makes 80.00

Question

I have this problem in c#, I want to convert a string to double.

textBoxKostOnderhoud.Text = "0.08";

kost.OnderhoudKost = double.Parse(textBoxKostOnderhoud.Text);

This makes 80.00 in my database and I don't know why. Is there any solution for this problem?

this is how I add my values to the database (mysql)

public bool insert(Kost kost)
{
    string query = "INSERT INTO kost (wagenId, onderhoudKost, tolKost, bedrijfsVerzekering, autoVerzekering, ladingVerzekering, wegenBelasting, eurovignet, accountantKost, telefoonKost, documentenEnVergunningen, onvoorzien, overige, andere) VALUES('" + kost.WagenId + "', '" + kost.OnderhoudKost + "', '" + kost.TolKost + "', '" + kost.BedrijfsVerzekering + "', '" + kost.AutoVerzekering + "', '" + kost.LadingVerzekering + "', '" + kost.WegenBelasting + "', '" + kost.Eurovignet + "', '" + kost.AccountantKost + "', '" + kost.TelefoonKost + "', '" + kost.DocumentenEnVergunningen + "', '" + kost.Onvoorzien + "', '" + kost.Overige + "', '" + kost.Andere + "')";

    if (this.OpenConnection())
    {
        //Create Command
        MySqlCommand cmd = new MySqlCommand(query, connection);
        //Create a data reader and Execute the command
        cmd.ExecuteReader();

        //close Connection
        this.CloseConnection();

        return true;
    }
    else
    {
        return false;
    }
}

sql

CREATE TABLE IF NOT EXISTS `kost` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `wagenId` int(11) NOT NULL,
  `onderhoudKost` double(10,2) NOT NULL,
  `tolKost` double(10,2) NOT NULL,
  `bedrijfsVerzekering` double(10,2) NOT NULL,
  `autoVerzekering` double(10,2) NOT NULL,
  `ladingVerzekering` double(10,2) NOT NULL,
  `wegenBelasting` double(10,2) NOT NULL,
  `eurovignet` double(10,2) NOT NULL,
  `accountantKost` double(10,2) NOT NULL,
  `telefoonKost` double(10,2) NOT NULL,
  `documentenEnVergunningen` double(10,2) NOT NULL,
  `onvoorzien` double(10,2) NOT NULL,
  `overige` double(10,2) NOT NULL,
  `andere` double(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `wagenId` (`wagenId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=64 ;

Please provide complete code sample that reproduces the observed behavior. How do you verify the value is `80.00`? — Ivaylo Strandjev, Mar 22 '13 at 13:23
what if you have "0,080"? It could be a culture problem but I'm not sure since you didn't give much informations. — , Mar 22 '13 at 13:23
And if you use the Convert.ToDouble method? EDIT: Nevermind, doesn't work. — Eric Smekens, Mar 22 '13 at 13:25
Make sure you are adding variable d instead of variable s into the database provided that the datatype in the database is a similar type to double. — SpaceApple, Mar 22 '13 at 13:25
I'm using doubles in my MYSQL database and if I try 0,080 I'll get 0.000 in the db. — Mankeeey, Mar 22 '13 at 13:27

Jon Skeet · Answer 1 · 2013-03-22T13:51:30.530

I suspect you're using a culture where the decimal separator is "," and the grouping separator is ".".

You can specify the culture to use when parsing:

double d = double.Parse(s, CultureInfo.InvariantCulture);

Whether this is appropriate or not depends on the context, very often - where is the string coming from? If it's a user, do you know what their culture is? If it's not from a user, is the data source inherently textual, or is there a way you could get the value without any string conversions?

Note that if this is for a currency value, you should almost certainly be using decimal instead of double.

EDIT: Now that you've included the SQL, we can see why the value isn't getting propagated to the database properly:

// Note properly broken, but this is all on one line. (Ick to start with.)
string query = "INSERT INTO kost (wagenId, onderhoudKost, tolKost, 
   bedrijfsVerzekering, autoVerzekering, ladingVerzekering, wegenBelasting, 
   eurovignet, accountantKost, telefoonKost, documentenEnVergunningen, onvoorzien, 
   overige, andere) VALUES('" + kost.WagenId + "', '" + kost.OnderhoudKost + "', '" 
   + kost.TolKost + "', '" + kost.BedrijfsVerzekering + "', '" +
   kost.AutoVerzekering + "', '" + kost.LadingVerzekering + "', '" + 
   kost.WegenBelasting + "', '" + kost.Eurovignet + "', '" + kost.AccountantKost + 
   "', '" + kost.TelefoonKost + "', '" + kost.DocumentenEnVergunningen + "', '" + 
   kost.Onvoorzien + "', '" + kost.Overige + "', '" + kost.Andere + "')";

You're converting all your values to strings in your SQL, and hoping that your database is going to parse them again in the same way. As it happens, it's not going to - again because of your default culture.

More importantly, you've got a massive SQL injection attack vulnerability here.

Don't do this. Use parameterized SQL instead, where you put placeholders in your SQL, and then specify the parameter values separately. Benefits:

Protects against SQL injection attacks
Keeps your SQL statement much cleaner by separating the code (the SQL) from the data
Reduces the number of conversions you end up doing, so you have fewer places to worry about culture etc

See the documentation for the MySQL driver you're using for the exact format to use for parameters (I believe there may be some slightly different forms depending on the driver) - but fundamentally this is something to apply to all your database access, whatever database you're talking to and from whatever language you're using.

This is working when i'm debugging the code. The variable has 0.08. But still when i check my db it has 8.00 — Mankeeey, Mar 22 '13 at 13:35
@Mankeeey: Well perhaps the next problem is in how you're storing the value in the database? We don't have any idea what your code looks like for that. (If you're not using parameterized SQL, that's quite possibly the problem.) — Jon Skeet, Mar 22 '13 at 13:37
@Mankeeey: Right. As I thought. **Don't do that.** Will edit my answer. — Jon Skeet, Mar 22 '13 at 13:48
+1 for _"a massive SQL injection attack vulnerability here"_ — Tim Schmelter, Mar 22 '13 at 13:57

score 4 · Answer 2 · answered Mar 22 '13 at 13:26

4

You can use CultureInfo.InvariantCulture in double.Parse to ignore the current culture:

double d = double.Parse(s, CultureInfo.InvariantCulture);

Demo

output:
0.08

answered Mar 22 '13 at 13:26

Tim Schmelter

450,073
74
686
939

score 1 · Answer 3 · answered Mar 22 '13 at 13:48

1

Since you are concatenating strings to create the SQL command text, your double d might be converted to string as "0,080" which MySql might interpret as an 80.

answered Mar 22 '13 at 13:48

Lucas

17,277
5
45
40

1

Good point. I'd at least try taking off the quotes for this parameter in the SQL string. Parameterized SQL would be better. Showing us the field definition for this field in the database might help. – Michael Sallmen Mar 22 '13 at 13:51

String "0.080" to double makes 80.00

3 Answers3