DataTable Column values

  • Thread starter Thread starter gpertea
  • Start date Start date
G

gpertea

Hi everybody I have a problem I've struggeld with for some time. I have
a DataTable with one column (say column 3) generated by an expression
like : Col1 * Col2 and the rusult has many decimal places. The column
data is of Decimal Type. Now is it possible to limit the decimal places
for this column to 2 decimal places? Thanks in advance.
 
If you mean for display, you can use the Format object to format the string
however you wish. Depending on the database used, you should also be able to
set the numeric in a particular format prior to output.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
have you tried using the round function to set the precision?

as in: ROUND((Col1 * Col2),2)

that's how i would do this in sql.

where in your form is the data goin?
thanks
rik

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
 
Gpertea,

The solution is a little cryptic but it works !! Essentially you have to
play around with the Convert function in the expression column - you convert
it to decimal first, (it is object to start with),
Then you multiply it with 100
then you convert it to integer (loose the decimal).
Then you convert it back to decimal.
Then you divide it by 100.

Important : The only gotcha is that this Convert interprets 2.6 as 3, not 2
... (like integer mathematics should), but I am sure you'd be able to come
around this problem with a little bit debugging (you have to add 0 or 0.5 to
your expression based upon if it is below or over x.5).

Here is sample code - you will have to modify the convert expression per the
above line, but the it will work.

DataSet ds1 = new DataSet();
ds1.Tables.Add(new DataTable("Table1"));
ds1.Tables[0].Columns.Add(new DataColumn("Column1"));
ds1.Tables[0].Columns.Add(new DataColumn("Column2"));
ds1.Tables[0].Columns.Add(new DataColumn("Column3"));

ds1.Tables[0].Columns[0].DataType = typeof(decimal);
ds1.Tables[0].Columns[1].DataType = typeof(decimal);
ds1.Tables[0].Columns[2].DataType = typeof(decimal);
DataColumn dc = ds1.Tables[0].Columns[2];

dc.Expression = "Convert(Convert(100 * Convert(Column1 *
Column2,System.Decimal),System.Int32),System.Decimal)/100"; // You owe me a
dinner for this one !!

DataRow dr = ds1.Tables[0].NewRow();
dr[0] = "10.22";
dr[1] = "11.11";

ds1.Tables[0].Rows.Add(dr);

Console.WriteLine(ds1.Tables[0].Rows[0][2].ToString());


Another approach I had thought of was to implement your own custom value
type and define a whole bunch of implicit cast operators, however I believe
..Expression eventually puts in the value as Object, and not as strongly
typed, and I cannot implicitly cast to be a base type (object), so that
approach will not work !!!!

Anyway, the above mentioned approach works !! Enjoy !! :-)

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
This DataTable will eventually be bounded to a Datagrid in a winform
but before that happens I will export this table to CSV in a foreach
loop and there are too many decimal places. I will try then to format
this column given the solutions from Sahil and rik. Thx
 
Back
Top