C# float variables are losing their decimals after being inserted on mysql database

Question

I've got a really weird problem here !

I've written a C# console app to get data from predefined IP addresses, after the calculations, I want the data to be inserted to a MySQL database so I can read it and do more calculations if needed using a PHP web application,

on my lap top everything is working perfectly,

when I run the C# app on the server, the data is being handled perfectly until the last step which is inserting it, but when it inserts the data, it's like an integer, no decimals.

I used VS Studio tools and tracked my variables, they are correct even in the sql statements but on the database they're losing their decimals.

I'm using windows 10 on my laptop and the server has windows Server 2016

here's the C# code :

public static string send_to_db(int uid, float prs, float btlvl, float f1, float f2, double t1, double t2, int dState, int supState, string datetime, string stmt)
    {
        MySqlConnection connection = null;
        string feedback = "";
        double min_prs_val = 0, max_prs_val = 0;

        // voltage to percentage the btlvl

        btlvl = (btlvl * 100) / 12;

        try
        {
            connection = new MySqlConnection(stmt);
            connection.Open();
            MySqlCommand insertData = new MySqlCommand($"INSERT INTO pm_data_records (pm_detail_id, pm_pressure, pm_battery_charge, pm_flow1, pm_flow2, pm_tot1, pm_tot2, pm_door, pm_sup, pm_sent_time) VALUES('{uid}','{prs}','{btlvl}','{f1}','{f2}','{t1}','{t2}','{dState}','{supState}','{datetime}')", connection);
            insertData.ExecuteNonQuery();
            MySqlCommand updateData = new MySqlCommand($"UPDATE pm_data_last SET pm_detail_id = '{uid}', pm_pressure = '{prs}', pm_battery_charge = '{btlvl}', pm_flow1 = '{f1}', pm_flow2 = '{f2}', pm_tot1 = '{t1}', pm_tot2 = '{t2}', pm_door = '{dState}', pm_sup = '{supState}', pm_sent_time = '{datetime}' WHERE pm_detail_id = '{uid}'", connection);
            updateData.ExecuteNonQuery();

            ...

I installed visual studio on the server and copied the code from my laptop and compiled it there too (after installing the MySQL for vs files) and still no decimals,

I'm outta ideas, help me plz ! tnx

floats and doubles ... all of the are losing their decimals just on the server ! not my laptop ! :( — Arian Sakhaei, Aug 26 '18 at 11:12
Why do you do string interpolation instead of parametrized queries? You open yourself to all kind of dangerous or weird things: from SQL injections to culture dependent string representation (decimal separator). — user4003407, Aug 26 '18 at 11:19
@PetSerAl would it make any difference in this case ?! I aint a pro in `C#` programming but I dont think that's the problem here , as i explained the variables are correct even in the `sql` statement . plus this program isn't on the internet so everybody can try to hack into it, it's using specific apn and a totally different IP range — Arian Sakhaei, Aug 26 '18 at 11:24
@arian.s Are you absolutely sure that values in SQL query is indeed correct, e.g. they are `'1.2345'` but not `'1,2345'`? — user4003407, Aug 26 '18 at 11:33
@PetSerAl yes I'm absolutely sure tnx to visual studio, I traced my variables and all their values are correct even in the sql statement, as i explained everything is perfectly working on my laptop! It's just not working as good on the server! — Arian Sakhaei, Aug 26 '18 at 11:39
I see only two possibility: 1) executing the same query produce different result depending on which client initiate the query (honestly, I do not see how is that possible in your case); 2) different clients generate different queries for the same input data, thus getting different result. You should check which of this happens in your case. — user4003407, Aug 26 '18 at 12:21
@PetSerAl : I found the problem ! It was actually the number format from windows options. the format was Persian in which there's no " . " !! it's " / " instead. any way tnx for the help :)) — Arian Sakhaei, Aug 29 '18 at 11:39

score 1 · Answer 1 · edited Aug 26 '18 at 15:02

You should never build query strings with parameters using the string interpolation like this.
- This is security issue;
- Your values may not be properly formatted and escaped. So maybe the problem is there actually.

You should do something like this:

using (var command = new MySqlCommand('INSERT INTO pm_data_records (pm_detail_id, pm_pressure, pm_battery_charge, pm_flow1, pm_flow2, pm_tot1, pm_tot2, pm_door, pm_sup, pm_sent_time) VALUES (@uid, @prs, @btlvl, @f1, @f2, @t1, @t2, @dState, @supState, @datetime)', connection))
{
    command.Parameters.AddWithValue("@uid", uid);
    command.Parameters.AddWithValue("@prs", prs);
    command.Parameters.AddWithValue("@btlvl", btlvl);
    ...
    command.ExecuteNonQuery();
}

Check data types of columns in your database. Maybe they do not allow floating-point numbers.

tnx for the answer, I tested this code but unfortunately the problem's not solved yet, I'm still loosing the decimals on my database :( — Arian Sakhaei, Aug 28 '18 at 06:57

C# float variables are losing their decimals after being inserted on mysql database

1 Answers1