Formatting Money for database inserts
This is a common problem and I use to come across this a lot. Here is a question from my friend Greg that I find many people scratch their head at.
Do you happen to know if there is any easy way in CF to mask against the use of commas in integer/float fields within a form? We are running into a lot of error on database insert due to the presence of commas (1,234 vs 1234). CF seems to let these through when a form is submitted, and then we get an error on insert.
So the first reaction is to say well why don't we just control what the user enters on the client side while entering a new record. I really have to points to drive home about this. I don't think you can ever rely on client side validation and what happens when you come to this record to update it? We take the numerical value from the database and format it as money to make it more user friendly.
The easiest way to get around this problem is to strip any formatting characters that might be present before a database insert. I took a quick look around to find something that would help all. I went over to trusty old CFLIB and found this function for converting to a number written by Glenn Wilson. Here is a quick example of a money string and how to convert it for a database insert.
One thing I will try and stress is getting away from the uber utility component that we all seem to create from time to time. You could easily abstract this method into a formatting library. I plan on writing an article shortly on this so stay tuned.
