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?

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?

• c#
• datatable
• sum
• datarow

Share Improve this question Follow edited Mar 25 at 9:24 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 25 at 8:50 user3309231user3309231 19455 silver badges2020 bronze badges 8

• 1 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 Commented Mar 25 at 8:57
• Yes I'm sure that the indexes are correct – user3309231 Commented Mar 25 at 8:57
• 3 "I'm sure to be correct" famous last words :D – Rand Random Commented Mar 25 at 8:58
• 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 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 Commented Mar 25 at 9:03

 |  Show 3 more comments

3 Answers 3

Sorted by: Reset to default 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;

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";

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.