<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>
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.Columns.Add(New DataColumn("id", GetType(Int32)))
dt.Columns.Add(New DataColumn("SUB1_TH", GetType(Int32)))
dt.Columns.Add(New DataColumn("SUB1_PR", GetType(Int32)))
dt.Columns.Add(New DataColumn("SUB1_TTL", GetType(Int32)))
dt.Columns.Add(New DataColumn("SUB1_PER", GetType(Double)))
dt.Columns.Add(New DataColumn("SUB2_TH", GetType(Int32)))
dt.Columns.Add(New DataColumn("SUB2_PR", GetType(Int32)))
dt.Columns.Add(New DataColumn("SUB2_TTL", GetType(Int32)))
dt.Columns.Add(New DataColumn("SUB2_PER", GetType(Double)))
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
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
42 Points
184 Posts
do calculation from column data and fill the result in another column of DataTable
Feb 28, 2019 05:09 PM|scala_1988|LINK
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>Participant
1061 Points
666 Posts
Re: do calculation from column data and fill the result in another column of DataTable
Feb 28, 2019 11:47 PM|jzero|LINK
See if it works for you
Contributor
3500 Points
1300 Posts
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.
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
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.