Add Serial Number to an already existing Datatable

Hi Devs, pls I will like to add S/N alues to a datatable e.g 1,2,3,4,5,6,7,8,9 10 etc but the challenge is I am using the code below and the resulting S/N output is not in the right sequential order.Pls assist.Regards

For Each row as Datarow in dtTest.rows
If (ColumnAmount = ColumnItemAmountInReasons ) Then
DT.rows.Add(New Object(){dtTest.rows.indexOf(row), Name, MobileNumber})
End If
Next

Could you explain with an example? It’s not clear where and when the serial number should be added.

Hi @RPA-botDev

Can you try this

Dim sn As Int32 = 0
For Each row As Datarow In dtTest.rows
	If (ColumnAmount = ColumnItemAmountInReasons ) Then
		sn = sn +1
		DT.rows.Add(New Object(){sn, Name, MobileNumber})
	End If
Next
1 Like

So I have 2 datatables, I am doing for dtTest and DT, DT has column names – “Id” “RouteNumber” “AccountNumber” with values and DT has columns “S/N”, “Name”, “MobileNumber” without row values.
So I am to looping through dtTest with

For Each row and then I am using add Data row as

DT.rows.Add(New Object(){dtTest.rows.indexOf(row), row.item(“Name”), row.item(MobileNumber)}) but the issue is S/N column is populating with 0, 1, 10, 100. I want it to populate with 1,2,3,4 in chronological order.Hope it’s clearer.Regards

Then the suggestion from @kumar.varun2 should work fine. Depending on the column type of “S/N”, you might also need to convert sn to a string first.

DT.rows.Add(New Object(){sn.ToString, row.item(“Name”), row.item(MobileNumber)})

1 Like

The method didn’t work well for me, my s/n rows populated are 1, 10 ,100 3, 4, … not in sequential order. What could have caused it pls

Is there another alternative to it?

Because you are not adding serial number, you are adding IndexOf() which can be in random order.

1 Like

Check my code please for errors - Dim sn As Int32 = 0
For Each row1 As DataRow In dtCape.Rows
ColumnAmount = row1.item(“Amount”).ToString

    For Each row2 As DataRow In dtTest.Rows
        ColumnItemAmount = row2.item("ItemAmount").ToString  
				
         sn = sn + 1
   If (ColumnAmount = ColumnItemAmount) Then
		DT.rows.Add(New Object(){sn.ToString, row2.item(“Name”), row2.item(MobileNumber)})
    End If
        Next
    Next

@RPA-botDev ,

Maybe it should have been changed to the below :

For Each row1 As DataRow In dtCape.Rows
	ColumnAmount = row1.item("Amount").ToString

    For Each row2 As DataRow In dtTest.Rows
        ColumnItemAmount = row2.item("ItemAmount").ToString  
				
         
		If (ColumnAmount = ColumnItemAmount) Then
			sn = sn + 1
			DT.rows.Add(New Object(){sn.ToString, row2.item("Name"), row2.item("MobileNumber")})
		End If
    Next
Next
1 Like

I discovered the issue, which is, I added sort dataTable after the code, so when sorting it in ascending order, it changes the order of the serial number. So is there a way I can sort in the code above instead of using sort data table activity after the code so as to solve the issue please.

You can test to add this line after your code to sort by “S/N”.

DT = DT.AsEnumerable.OrderBy(Function(r) CInt(r("S/N").ToString)).CopyToDataTable

1 Like

Thanks for your contribution ptrobot, one more pls, it has sorted as implemented.Please would like to also sort the second column r(“Name”) in ascending order with the serial number as 1,2,3 correctly, how do I implement that please?

Unfortunately, you can’t sort by both serialnumber and name columns independently. One of them will always end of out of order.

What you can do is to sort by name first and then update the column “S/N” later.
E.g. if you add this after your code:

' Sort by Name
DT = DT.AsEnumerable.OrderBy(Function(r) r("Name").ToString).CopyToDataTable

' Update serial numbers
sn = 0
For Each row As DataRow In DT.Rows
	sn = sn + 1
	row("S/N") = sn.ToString	
Next
1 Like

Yea, it worked as you suggested @ptrobot

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.