Lookup multiple rows from one datatable in another datatable

I have two datatables A and B with thousands or rows in each
I need to fill a column in A with values in B but I can only pick out those values using a lookup with a different column in A with B.

Basically, I need to lookup a column (e.g. length) from A with a column in B (e.g. breadth). If there are matching values, then I will select the value from another column (height) on the same row in B and copy that value into a different column (height) in A.

I have tried using for each as shown below


Dim length As String
Dim lookupvalue As String
Dim height As String
Dim breadth As String
Dim Bheight As String
Dim Newheight As String

For Each row As DataRow In A.Rows

length = row.item("length").ToString
height = row.item("height").ToString
If String.IsNullOrEmpty(height)Then
	If Not String.IsNullOrEmpty(length) Then
		lookupvalue = length
	End If

	For Each rowitem As DataRow In B.Rows
		breadth = row.item("breadth").ToString
		Bheight = row.item("Bheight").ToString
		If breadth.contains(lookupvalue) Then
			Bheight = Newheight
		End If

End If
row.item("height") = Newheight		

Catch ex As Exception

errorMessage = ex.Message

End Try

I keep getting the error “column breadth does not belong to table” and then the code breaks.

Thank you in anticipation of your help

Try this @Gidi_automator

I hope this will work

yellow highlighted should be “rowitem” instead of row.item , see blue highlighted .

Thank you very much. I tried that and it fixed it.

However, I think I may need a more efficient way to achieve this. The datatables contain thousands of rows and it is taking a very long time to run.
Please do you have a better way to achieve this?

Thank you.