Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)..

share|improve this question
2  
As a side note, sorting monetary values (or related things like tax rates/values) in floating point types is generally considered a bad idea. To avoid potential rounding anomalies you should use fixed point types like DECIMAL/NUMERIC instead. –  David Spillett Oct 7 '13 at 8:44

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.