Out-of-Range and Overflow Handling for float values

Question

am using mysql 5.5 log version with sqlmode='' .I have many columns with float(9,3) as the type. Say for ex one column taxvalue (9,3)

Currently if the user enter the value above 999999.000 MYSQL will save its maximum value,ie 1000000.000 and it will not throw any error.

If I set the sqlmode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' my insert procedure will throw error, if the value for taxvalue is greater than 999999.000

Am using this column in a user entry page and that application is written in C#. I wanted to show a validation message to user if he tries to enter the value above 999999.000.

what is the best method I can use to make sure,user is getting a validation message before he clicks the save button.?I have tried with regular expression but its not giving me the desired result because user can enter any value with the range of (9,3)..

Answer 1

In my view, you're on the right track and validating it in C# before sending it to the database is the better way, so you're able to show the changes before a submit.

A regular expression should indeed be able to solve your validating issue, but depending on how you allow the number to be entered plays a part in how complex it should be. Say do you allow thousand-seperator, decimal points, negative numbers, the number zero and so on. And such cases always make Regular Expression a daunting task if not rather familiar with the syntax.

So I'd say that in your situation, the very easiest way would be to simply check whether the entered number is indeed a number and then cast it to a decimal and simply check whether it's value is between -1000000.000 and 1000000.000 (not inclusive). Decimal.TryParse and an if-statement or two would be all that's needed for this.