ASP.Net & SQL Server 2005

How to Store Currency in SQL Database

If you want to store currency in your DB table with two decimals, use the decimal data type instead of currency. Then, in the properties for that data type, set the Precision to the number of digits to the LEFT of the decimal (I used 5) and then set the Scale to the number of digits to the RIGHT....I changed it from 0 to 2. See screen shot below.

Now, when we enter our data, it is stored in 2 decimal places instead of four.

Format Grid Output

If you do need to store data as money or smallmoney type or currency, you can format the output in your grid or control by using a Regular expression....{0:c2}. This sets the display to 2 decimal places and includes a $ sign. The data in the DB table will still hold four decimals but you can display it however you want.

In your GridView control, click the smart tag, choose edit columns.

Select the cost field (in this case). In the DataFormatString property, insert {0:c2} to display the $ sign and two decimals. Even if you do store data as money, this formatting function will display it with two decimals!

To display money without the $ sign, use {0:N} where N = number. If you put quotes around the expression "{0:N}", then the data will have " " around the numbers in your table. Oh yea, the 0 in these expressions just refers to the parameters index of a list of parameters. Since there is only one value in a cell, this is always 0.

I tried using 0:N2 and 0:N and got the same good results! Seems like two decimals is the default of N.

In the table below, you can see that a C,D,E,F,G,N can be used to format stuff in many formats. Hope this helps!