locked
How to round a number in a DataTable? RRS feed

  • Question

  • User-1833521293 posted
    I want to round the column in a DataTable to two decimals. Please refer to the following code. dsBarChart is the DataSet that contains columns Development, Research, and Sustaining.

    Dim dtGrid AsNew DataTable
    dtGrid = dsBarChart.Tables(0)

    ' Create column for the datatable
    Dim dtDevelop AsNew DataColumn("% Development", GetType(Decimal))

    dtDevelop.Expression = "Development / (Development + Research + Sustaining)"

    ' Add the columns to the datatable
    dtGrid.Columns.Add(dtDevelop)

    I tried
    dtDevelop.Expression = "Decimal.Round(Development / (Development + Research + Sustaining), 2)" and received a runtime error "The expression contains undefined function call Decimal.Round()."

    I tried dtGrid.Columns.Add(Decimal.Round(dtDevelop, 2)) and received an error "Value of type 'System.Data.DataColumn' cannot be converted to 'Decimal'.)

    Where can I round the number to two decimals? I use .net 1.1.  Thanks.

    DanYeung
    Wednesday, July 19, 2006 11:06 AM

All replies

  • User1224304770 posted
    That's because dtDevelop is a DataColumn but is used as a decimal value. However I don't know how to round the decimal in Expression Property, it seems you have to round the data after retrieving it from the table.
    Thursday, July 20, 2006 5:06 AM
  • User1224304770 posted

    I managed to build an ungly Expression to fit your case:

     

     DataTable dtGrid = new DataTable();
                DataColumn dtDevelop = new DataColumn("%Development", Type.GetType("System.Decimal"));
                dtGrid.Columns.Add("Research", Type.GetType("System.Decimal"));
                dtGrid.Columns.Add("Development", Type.GetType("System.Decimal"));
                dtGrid.Columns.Add("Sustaining", Type.GetType("System.Decimal"));

                string exp="Convert(SUBSTRING(Convert(Development/(Development + Research + Sustaining),'System.String'),1,IIF(LEN(Convert(Development/(Development + Research + Sustaining),'System.String'))<4,LEN(Convert(Development/(Development + Research + Sustaining),'System.String')),4)),'System.Decimal')";

                dtDevelop.Expression = exp;
                dtGrid.Columns.Add(dtDevelop);
                dtGrid.Rows.Add(2,1,2);
                Console.WriteLine("Column {0} is {1}",dtGrid.Columns[3].ColumnName,dtGrid.Rows[0][3]);

    Since Development/(Development + Research + Sustaining) is always between 0 and 1, the Expression will return 0.XX if the bits after the round point '.' is longer than or equal to 2, otherwise will return 0.X

    Thursday, July 20, 2006 5:53 AM
  • User1518006005 posted

    You might try using the following expression: 
    Convert(x * 100, 'System.Int32')/100 

    Saturday, July 9, 2011 3:21 AM