0

I am writing Insurance Managment System as project at University. This is my MySQL commadn:

 string lifeQuery = "insert into lifeinsurance values( null, '" + surname.Text + "." + pesel.Text + "', " + double.Parse(lifeInsSumTB.Text) + ", '" + double.Parse(lifeInsPriceTB.Text)
             + ");";

But te problem is that in UWP double is with ',' and to MySQL i need to have it with '.'. When I try to do this like this: '25,453' it says data truncated. Without ' ', like this 25,453 it says that column count doesn't match value count at row 1, because it interets it as two different values 25 and 453. So my question is: How do I insert this double value to my table?

Jolof
  • 41
  • 6
  • please avoid concatenating SQL strings with + because of SQL injection. You can use parameters to pass your variable to SQL string. Also, it helps you to avoid syntax errors. – Mert Akkanat May 28 '20 at 08:13

2 Answers2

2

This problem is caused by the implicit conversion to a string when you call double.Parse and then concatenate the result back into the sql text. This requires the compiler to represent the double value as a string and it will use the current culture to do the conversion. Of course the result is not what MySql expect to be a double value.
Moreover using string concatenation to build sql commands leads to Sql Injection hacks. A very nasty problem that you should avoid. Always.

So let's try to add some code to resolve these problems

 // A parameterized string without any concatenation from user input
 string lifeQuery = @"insert into lifeinsurance 
        values( null, @surname, @sum, @price)";
 MySqlCommand cmd = new MySqlCommand(lifeQuery, connection);

 // Add the parameters with value for each placeholder in string
 cmd.Parameters.AddWithValue("@surname", surname.Text + "." + pesel.Text);

 // Parse the user input as a double using the current culture to correctly
 // interpret the comma as decimal separator.
 // Note that here I have no check on the correctness of the input. If your
 // user cannot be trusted to type a valid double number then you should use
 // the double.TryParse approach separating these lines from the actual check
 cmd.Parameters.AddWithValue("@sum", double.Parse(lifeInsSumTB.Text, CultureInfo.CurrentCulture));
 cmd.Parameters.AddWithValue("@price", double.Parse(lifeInsPriceTB.Text, CultureInfo.CurrentCulture));
 cmd.ExecuteNonQuery();
Steve
  • 213,761
  • 22
  • 232
  • 286
0

Like other said - there are better ways to send over data with Sql. That being said this answer focuses on addressing your specific problem.

I think your problem may be your language/culture settings.

Try this:

Console.WriteLine(double.Parse("19.2323244").ToString("G", CultureInfo.InvariantCulture));

Output:

19.2323244

https://learn.microsoft.com/en-us/dotnet/api/system.globalization.cultureinfo?view=netcore-3.1#Invariant

sommmen
  • 6,570
  • 2
  • 30
  • 51
  • Correct but this is a really bad answer. Do not teach this because in this way the code is still exposed to Sql Injection and this is a lot worse than the original problem – Steve May 28 '20 at 07:19