Sum of multiple datatable columns

Question

I want to get the sum of only two columns for each data row in datable and display it in datagridView. I tried to use link query:

SqlCommand cmd = new SqlCommand("SELECT * FROM Student", con);

DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());

dataGridView1.Rows.Add(dt.Rows.Count);

foreach(DataRow row in dt.Rows)
{
    dataGridView1.CurrentRow.Cells["Column1"].Value = row["FullName"].ToString();
    dataGridView1.CurrentRow.Cells["Column2"].Value = row["Maths"].ToString();
    dataGridView1.CurrentRow.Cells["Column3"].Value = row["Physics"].ToString();                
    dataGridView1.CurrentRow.Cells["Column4"].Value = dt.AsEnumerable().Sum(r => double.Parse(r[1].ToString()) + double.Parse(r[2].ToString()));
}

This code throws this exception:

The format of the input string is incorrect.

Any suggestions?

Are you sure its column 1 and column 2? You know that the first column is at index 0? Maybe it would be easier if you would use the string overload, so find the column by it's name instead of the index. — Tim Schmelter
– Tim Schmelter, Commented Mar 25 at 8:57
Put a breakpoint on the problematic line. Run to it. When you hit the breakpoint, go to the Immediate Window. Type in ?r[1].ToString(). What is displayed? Type in ?r[2].ToString(). What is displayed? — mjwills
– mjwills, Commented Mar 25 at 8:59
Where this r is coming from? What if you change r[1] to row["Maths"] and r[2] to row["Physics"]? — Zohar Peled
– Zohar Peled, Commented Mar 25 at 9:03

Rand Random · Accepted Answer · 2025-03-25 09:41:50Z

2

I believe what you want to have is this:

SqlCommand cmd = new SqlCommand("SELECT *, Maths + Physics as MySum FROM Student", con);

DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());

dataGridView1.ItemsSource = dt;

answered Mar 25 at 9:41

Rand Random

7,48710 gold badges48 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dmitrii Bychenko · Accepted Answer · 2025-03-25 15:49:43Z

2

If you don't want to modify SQL query, you can try adding computable column to the data table:

using var cmd = new SqlCommand("SELECT * FROM Student", con);

using var reader = cmd.ExecuteReader();

DataTable dt = new DataTable();
dt.Load(reader);

dt.Columns.Add("MySum", dt.Columns["Maths"].DataType).Expression = "Maths + Physics";

edited Mar 25 at 15:49

answered Mar 25 at 9:53

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Comments

marc_s · Accepted Answer · 2025-03-25 09:25:14Z

0

Your code

dt.AsEnumerable().Sum(r => double.Parse(r[1].ToString()) + double.Parse(r[2].ToString()))

will query the entire DataTable instead of summing the current row, all rows in the column are summed.

edited Mar 25 at 9:25

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 25 at 9:10

luoberto

111 bronze badge

2 Comments

user3309231 Mar 25 at 9:26

How to Sum the current row?

mjwills Mar 25 at 9:29

@user3309231 Stare long and hard at row["Physics"].ToString() and see if you can work it out.

Collectives™ on Stack Overflow

Sum of multiple datatable columns

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related