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

## 2 replies

Last post Mar 01, 2019 04:07 AM by Ackerly Xu

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

I have a DataTable with columns,

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

<div>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. </div> <div> </div> <div>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?</div>
• ### Re: do calculation from column data and fill the result in another column of DataTable

See if it works for you

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

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

Mar 01, 2019 04:07 AM|Ackerly Xu|LINK

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

MSDN Community Support