# do calculation from column data and fill the result in another column of DataTable

• ### Question

• User364607740 posted

I have a DataTable with columns,

ID   |  SUB1_TH  |  SUB1_PR  |  SUB2_TH  |  SUB2_PR  |  SUB1_TTL  |  SUB1_PER  |  SUB2_TTL  |  SUB2_TTL

I have to calculate the total of SUB1_TH, SUB1_PR and set into SUB1_TTL and calculate the percentage and fill in SUB1_PER. Similarly with the SUB2 also.

It is only a sample table, my actual table contains around 50 columns and the row number is dynamic. How can I do this in DataTable?
Thursday, February 28, 2019 5:09 PM

### All replies

• User-943250815 posted

See if it works for you

```For Each myDR As DataRow In myDataTable
myDR("SUB1_TTL") = myDR("SUB1_TH") + myDR("SUB1_PR")
Next```

Thursday, February 28, 2019 11:47 PM
• User-893317190 posted

Hi scala_1988,

If your column is regular ,such as  SUB1_TH ,SUB2_TH, SUB3_TH... , you could  use for loop in a for loop.

Below is my code.

``` If Not IsPostBack Then

Dim dt As DataTable = New DataTable()
dt.Rows.Add(1, 10, 20, 0, 0, 30, 40, 0, 0)
dt.Rows.Add(2, 20, 30, 0, 0, 40, 50, 0, 0)
dt.Rows.Add(3, 60, 70, 0, 0, 80, 90, 0, 0)
Dim count = (dt.Columns.Count - 1) / 4   'get how many times you should cauculator, SUB_TH	SUB_PR	SUB_TTL	SUB_PER is one group exculde id , so divided by 4
dt = dt.AsEnumerable().Select( ' the select method could get every row of the datatable and receive the mapped row finally the result will be collection of mapped row
Function(row)
'here in the function you get every row, and set row columns you want
For index = 1 To count   ' here total group is two , so loop for two times
' set total
row("SUB" & index & "_TTL") = DirectCast(row("SUB" & index & "_TH"), Int32) + DirectCast(row("SUB" & index & "_PR"), Int32)
'set percentage
row("SUB" & index & "_PER") = DirectCast(row("SUB" & index & "_PR"), Int32) * 1.0 / DirectCast(row("SUB" & index & "_TTL"), Int32)

Next
Return row
End Function
).CopyToDataTable() 'the CopyToDataTable method convert the result to datatable

GridView1.DataSource = dt
GridView1.DataBind()

End If```

Here I use linq , but it also applies to jzero's for each.

You only need to write the same code in the body of for each.

Best regards,

Ackerly Xu

Friday, March 1, 2019 4:07 AM