I have a float field in a table and I'm experimenting some updates using a DataGrid and a
MySqlCommanBuilder. If the field assumes some particular values (ie. 2.24) I get a
concurrency error when I send the UPDATE query to the server.
After some research I found that this particular value is probably not handled with the
exact value by the server on a float type field, because of the approximation applied by
the system on loating point numbers which every programmer knows of (it's late night and
I'm too lazy to try the math :-) ).
The problem is that the CommandBuilder creates the UPDATE command string including the
value of the float field in the WHERE part (... WHERE 'fld_1'=1 AND 'float_fld'=2.24).
This causes the query to not find the row and then return 0 rows which cause the
concurreny error in .NET.
I noticed that changing the field type to double solves the problem, but I think this is a
partial solution, because the operator '=' with a floating point number is always not
recommended in comparisons. I think I should remove the floating field comparison from
the command text (I read about concurrency settings for the command text, but don't
remember where they are), but won't this cause problems in case of more than one user
concurrently working on that record?
What is a 'best' practice to solve this problem?