Formatting a datatable's column to show currency

Question

I am inputing a datatable into a gridview to show several different charges from this past year. I want the charges in the gridview to appear in currency format but I would also like to be able to sort the columns when the headers are clicked.
I can get the format to be currency easily if the columns are of the type string ex. dim dt as DataTable dt.Columns.Add("ChargeField ", System.Type.GetType("System.String"))

but the sorting doesn't work right when it is a string

I sort by using this code dt.DefaultView.Sort = ChargeField & " " & ASC

Sorting does work when the columns are of type double ex. dt.Columns.Add("ChargeField ", System.Type.GetType("System.Double"))

but then it isn't in currency format.

Is there a way to make the datatable/Gridview have a double/decimal column that shows its values as currency? If not, is there a better way to accomplish what I am trying to do?

Answer 1

Add the columns as double (just like you said in your post) so that they will sort properly.

dt.Columns.Add("ChargeField ", System.Type.GetType("System.Double"))

Then you can do this to format all the columns in a data grid view as currency:

DataGridView1.DataSource=dt
DataGridView1.DefaultCellStyle.Format = "c"

Alternatively, you can do it for individual columns:

DataGridView1.DataSource=dt
DataGridView1.Columns("ChargeField").DefaultCellStyle.Format = "c"

For a Gridview, you can use DataFormatString. This MSDN page might help, as well as this MSDN forum post and this ASP.net forum post

<Columns>
    <asp:BoundField DataField="ChargeField" DataFormatString = "{0:c}" />
</Columns>