JustSomeNewbie - 6 months ago 85

C# Question

I'm having some problem with my c# code.

What I'm trying to do is to add new row to my dataTable and then calculate some things and insert into specific cells.

Here is what I'v got :

`object sumAmount;`

object sumDest;

object sumWhole;

DataRow row = dataTable.NewRow();

row["number"] = "1";

dataTable.Rows.Add(row);

sumAmount = dataTable.Compute("Sum(amount)", "");

dataTable.Rows[dataTable.Rows.Count - 1][all] = Int32.Parse(sumAmount.ToString());

sumDest = dataTable.Compute("Sum(dest)", "");

dataTable.Rows[dataTable.Rows.Count - 1][columnNumber] = Int32.Parse(sumDest.ToString());

sumWhole = dataTable.Compute("Sum(wholeAmount)", "");

dataTable.Rows[dataTable.Rows.Count - 1][allTogether] = Int32.Parse(sumWhole.ToString());

It works correctly for the first time.

But when i change some cell value then I'm recalculating this with this same code (part with dataTable =) and when I'm doing it the cell value isnt replaced, this added.

Example:

When I had this :

`10 3 13`

6 1 7

2 0 2

18 4 22 << This is SUM

And I insert 1 in some cell in 2nd column im expecting this :

`10 3 13`

6 1 7

2 1 3

18 5 23

But what im getting is :

`10 3 13`

6 1 7

2 1 3

38 9 45

As you can see these values just being added, but I don't get it why?

Can someone help me finding bug in this code?

Answer

Its not a spreadsheet, so it doesnt know to exclude the last row from the formula. It looks like the 3rd column is also a Total? If so, *that* can be an expression which the `DataTable`

maintains:

```
dtXYZ = new DataTable();
dtXYZ.Columns.Add("X", typeof(Int32));
dtXYZ.Columns.Add("Y", typeof(Int32));
dtXYZ.Columns.Add("Z", typeof(Int32));
dtXYZ.Columns["Z"].Expression="X+Y";
```

The columns are typed so that `X+Y`

adds. Add some data probably like you already are:

```
dtXYZ.Rows.Add(new object[]{10, 3});
dtXYZ.Rows.Add(new object[]{6, 1});
dtXYZ.Rows.Add(new object[]{2, 0});
dtXYZ.Rows.Add(dtXYZ.Compute("sum(X)", ""),
dtXYZ.Compute("sum(Y)", ""));
```

The last column does need to be computed, the `Expression`

will take care of that. One way to use `Compute`

and not include the grand total row, would be to remove and readd it each time:

```
int nRows = dtXYZ.Rows.Count - 1;
dtXYZ.Rows.RemoveAt(nRows);
dtXYZ.Rows.Add( dtXYZ.Compute("sum(X)", ""),
dtXYZ.Compute("sum(Y)", ""));
```

But rather than doing that just to be able to use `Compute`

, you can do the summing yourself with some extension methods and post the new value:

```
int nRows = dtXYZ.Rows.Count - 1;
dtXYZ.Rows[nRows ]["X"] = dtXYZ.AsEnumerable()
.Take(nRows)
.Sum(x => x.Field<int>("X"));
dtXYZ.Rows[nRows ]["Y"] = dtXYZ.AsEnumerable()
.Take(nRows)
.Sum(x => x.Field<int>("Y"));
```

Or even a loop would work. Either way, remember you dont have to fiddle with the last column if it is based on an `Expression`

- in fact you cant.