Question:
I have an ASP.Net application that allows users to enter currency amounts, which are stored in the SQL Server database with the type money. How can I support input of USD currency values, including the dollar sign and thousands separators, within the context of ASP.Net databound controls such as ListView, and declarative data access controls such as SqlDataSource?
Answer:
There are a few little tricks you need to know to support currency input in this scenario.
1. The most important, magical trick I've found is to simply declare your parameter with no Type or DbType attribute.
Example: <asp:parameter name="TuitionPerCredit" />
2. Use a RegularExpressionValidator control with a really solid, well tested expression for validating the currency input The expression I've been using is applicable to US currency, so if you're coding for another culture, the advice in #1 still applies but you'll need a regex that applies to your specific currency format
The currency regex I use is below:
\$?-?([1-9]{1}[0-9]{0,2}(\,\d{3})(.\d{0,2})?|[1-9]{1}\d{0,}(.\d{0,2})?|0(.\d{0,2})?|(.\d{1,2}))$|^-?\$?([1-9]{1}\d{0,2}(\,\d{3})(.\d{0,2})?|[1-9]{1}\d{0,}(.\d{0,2})?|0(.\d{0,2})?|(.\d{1,2}))$|^(\$?([1-9]{1}\d{0,2}(\,\d{3})*(.\d{0,2})?|[1-9]{1}\d{0,}(.\d{0,2})?|0(.\d{0,2})?|(.\d{1,2})))$
The regex above will validate entries like: $3, $3.00, $3,000, and $3000.00 It's not perfect, because it will validate something like $3000[.] (a digit at the end without the following two digits). However, I've found that SQL Server ignores the trailing period, so it's not a serious issue. The regex was posted on StackOverflow.com by the user JohnM, at the following link:
http://stackoverflow.com/questions/354044/what-is-the-best-u-s-currency-regex
Finally, one additional word of advice: If you application has many areas where currency values can be entered or edited, centralize your Regex pattern inside of a resource file. That way, whenever you need to apply a RegularExpressionValidator control, you can obtain the pattern for use in the ValidationExpression property like so:
<asp:regularexpressionvalidator>
ID="TuitionRegularExpressionValidator
ControlToValidate="InputTextBox"
ErrorMessage="The value entered in the 'Tuition' field is not a valid USD currency value."
ValidationExpression="<%$ Resources:CommonValidation, Currency %>"
runat="server" />
I am counting down to a full week off work beginning Friday, May 13th at 5:00 PM sharp. Oh yeah!