How to insert a double value on mysql database?

Question

_{Wednesday, June 26, 2019 11:37 AM}

I want to insert a value from a numericUpDown with decimal values like "2,25" but sql doesn't use commas as decimal devider, so it inserts "2" instead of "2,25"

How can I format the numericUpDown or some other thing that helps me?

edit:

var bdCon = Conexao.Instance();
            bdCon.DatabaseName = "solintep";
            if (bdCon.IsConnect())
            {
                string query = $"INSERT INTO libelles (rubrica, data, libelle, assunto, numero, user_id) VALUES ('{cbRubrica.Text}', '{dpData.Value.ToString("yyyy/MM/dd")}', '{txtLibelle.Text}', '{cbAssunto.Text}', '{nrNumero.Value}', '{userId}');";
                var cmd = new MySqlCommand(query, bdCon.Connection);
                cmd.ExecuteNonQuery();
            }
            bdCon.Close();

All replies (9)

_{Wednesday, June 26, 2019 11:48 AM ✅Answered}

If you are formatting the value in order to insert it, you are doing something wrong. The SQL queries should always be parameterized, so when you assign the value you assign it to the parameter (you do not concatenate it in the SQL statement). The value that you assign in this way is always binary, so it doesn't have any format.

If you copy here the code that you are using for saving the data, we can tell you how to modify it so that it is parameterized. For example, if you are using OleDb for writing to the database it would look like this:

double dataToSave = ...;

string sql = "Insert into myTable(field) values (?)";
OleDbCommand cmd = new OleDbCommand(sql, connection);
command.Parameters.AddWithValue("@field", dataToSave); // Note: binary value of the double, unformatted
cmd.ExecuteNonQuery();

_{Wednesday, June 26, 2019 2:45 PM ✅Answered | 1 vote}

The code that you just edited is using string concatenation to insert the parameters into the query. This is a BAD thing for multiple reasons. Apart from the problem with the decimal separator that you already found, it also has a similar problem with date formats, and with strings that contain quotes, ans worst of all it is vulnerable to SQL injection attacks.

The remedy is to parameterize the query, which is what I have been telling to you since the first response, but you did not consider that part of the response when it was the most important of them all and everything else depended on it:

string query = $"INSERT INTO libelles (rubrica, data, libelle, assunto, numero, user_id) VALUES (@r, @d, @l, @a, @n, @u);";
var cmd = new MySqlCommand(query, bdCon.Connection);
cmd.Parameters.AddWithValue("@r", cbRubrica.Text);
cmd.Parameters.AddWithValue("@d", dpData.Value); // Pass the DateTme! No formatting into String!
cmd.Parameters.AddWithValue("@l", txtLibelle.Text);
cmd.Parameters.AddWithValue("@a", cbAssunto.Text);
cmd.Parameters.AddWithValue("@n", nrNumero.Value); // Pass the binary Value, which is already a double. No formatting into String!
cmd.Parameters.AddWithValue("@u", userId);
cmd.ExecuteNonQuery();

_{Wednesday, June 26, 2019 11:52 AM}

Alberto, you're right but the problem is that mysql doesn't accept commas as decimal devider

_{Wednesday, June 26, 2019 2:01 PM}

Well, that should not be a problem if you are passing a binary value. It doesn´t have commas anywhere.

EDIT: Let me expand on that. If you are passing to the parameter a variable of type "double", there is no comma stored inside the variable. Instead, the "double" stores internally a sequence of ones and zeroes which represent a mantissa and an exponent. The exponent is a number that represents the magnitude of the value, from which can be inferred which bits of the mantissa are the decimal places. No comma exists anywhere within these internal bits.

_{Wednesday, June 26, 2019 2:03 PM}

But the value of the numericUpDown is something like "4,50" not "4.50" so when I try to insert it only send the value "4"

_{Wednesday, June 26, 2019 2:18 PM}

No. This should be invisible for the code that accesses the database. You are inserting a double. The double does not contain any comma, it only shows a comma when you convert it to string to show it on screen. When you pass it to the parameter, it does not contain a comma.

If you show it on screen and it contains a "4" instead of "4,50" or "4.50" (one or the other would be shown depending on how you visualize it), then it means that there is a mistake in the way in which you are loading the value into the "double" variable. It doesn't have anything to do with writing it to the database.

EDIT: To clarify -- There are two steps in saving your NumericUpDown:

a) Take the value from the NumericUpdown and put it into a variable of type "double".

b) Take the "double" variable and write it into the database,

No commas are involved in step (b) if you do the database write via parameters like I showed to you earlier (not by concatenating into the SQL statement!).

So any mistakes in the conversion are happening in step (a). If you show us the code for how you are doing this step, we can try to point out why it is producing an unexpected value.

_{Wednesday, June 26, 2019 2:24 PM}

So, let me see if I understand. I just can do it if I utilize parameters! If I understood it wrong, how can I insert numbers with decimal digits?

_{Wednesday, June 26, 2019 2:48 PM}

Thank you, but I didn't ignore, I'm just new at programming and didn't understand

_{Wednesday, June 26, 2019 3:30 PM}

My apologies. I had quickly edited out the "ignore" part, but apparently you saw my answer before I removed that. Sorry about it.