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

Answer 1

@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