Display currency in System.Data.DataTable

Question

I'm working on some old VB code which creates a System.Data.DataTable and defines some columns. It works fine, except that I need a certain column to display as currency, not just a floating point number. How do I this?

Dim myDataTable As New System.Data.DataTable("tblRec")
myDataTable.Columns.Add("RECAMOUNT", System.Type.GetType("System.Double"))

Protected WithEvents DataGridCurrentCRLines As Global.System.Web.UI.WebControls.DataGrid
Session("CRLines") = myDataTable
DataGridCurrentCRLines.DataSource = Session("CRLines")

Changing the line to:

myDataTable.Columns.Add("RECAMOUNT", System.Type.GetType("System.Decimal"))

makes no difference, by which I mean the 1234567.89 is displayed, not 1,234,567.89

You are confusing storage and display. You can format a number as desired if you convert it to a string. So it is not the DataTable but the DataGrid which displays it. You could use the DataFormatString property of the BoundField. http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.boundfield.dataformatstring(v=vs.80).aspx — Tim Schmelter, Sep 15 '14 at 21:53
My comment was about the correct datatype to use to represent currency values not about the way in which display the value. @TimSchmelter has given you the correct hint about the problem. http://stackoverflow.com/questions/803225/when-should-i-use-double-instead-of-decimal — Steve, Sep 15 '14 at 22:02

score 0 · Accepted Answer · answered Sep 18 '14 at 14:56

@Time Schmelter's hint points in the correct direction.
First change the type to string:

myDataTable.Columns.Add("RECAMOUNT", System.Type.GetType("System.String")

then I wrote a helper method to convert a String to a currency String, It appears that one cannot convert from String directly to a currency String, but rather you have to to String -> Decimal -> Currency String

 Private Function ConvertStringToCurrencyString(ByVal inputString As String) As String

        Dim currencyString As String
        currencyString = ""
        Dim myDecimal As Decimal

        Try

            myDecimal = System.Convert.ToDecimal(inputString)
            currencyString = myDecimal.ToString("C2")

        Catch exception As System.OverflowException
            System.Console.WriteLine("ConvertStringToCurrencyString(): Overflow in string-to-decimal conversion.")
        Catch exception As System.FormatException
            System.Console.WriteLine("ConvertStringToCurrencyString(): The string is not formatted as a decimal.")
        Catch exception As System.ArgumentException
            System.Console.WriteLine("ConvertStringToCurrencyString(): The string is null.")
        End Try

        Return currencyString

    End Function

Display currency in System.Data.DataTable

1 Answers1