Improving Your Form - a Decimal cannot fit into a Decimal

If you have ever gotten an error message like “DATABASE ERROR: [FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server] Error converting data type decimal to decimal” you might be thinking it’s a bug in FireDAC or whatever database layer you’re using.  The two types are the same so no “conversion” should be necessary and how can assigning the same type to itself fail?

In fact, in the cases I have encountered it was because the value being written to the database exceeds the range (positive or negative) of the column as defined in the database.  For example, attempting to write 100,000 into a column defined as decimal(9,4) would fail.

Ideally the database layer would provide a more precise error message, something that perhaps included the value being written with the name, type and size of the database column.  I’m sure there are some technical reasons for such obscure error messages.  No doubt multi-vendor database support is likely the major one.  If the database doesn’t provide the information no DAL can pass it on.  Crafting meaningful error messages also requires a level of skill, forethought and empathy.

This is exactly why applications really need some sort of framework for UI/Domain level validation for at least minimum and maximum values so monkey testing cannot break the app.  If every form in your application needs to implement validation logic, keeping it consistent over time and not forgetting some control(s) is problematic. So the first step to improving your form is to put all the validation and business logic elsewhere so it’s re-usable. That doesn’t mean you have to go full MVVM with domain objects. A good start is to use an ORM.

Leave a Reply