Question Query datatable with datatable as criteria

[Klingon66]

Hello All,
I have Always found what i needed on the net but now i am stuck .(this is my first question ever lol )
I will do my best to explain my problem .

I have 2 SQL databases ( lets say TAB1, TAB2 ) and i query each database with datatable ( DTB1 , DTB2 )
Both datatables have multiple columns but 1 column is holding the same data (Productnumber )

DTB1 holds Productnumber and Status = 3 ( queried from TAB1 where there are also Status 1 and 2's)

DTB2 holds Productnumber and Location ( queried from TAB2 )


Now i need to query DTB2 with DTB1 .

If a productnumber of DTB2 contains a productnumber of DTB1 result must be the productnumber .

fffff i hope it makes sense .

Would love a peace of code to help me as i dont now where to begin .
i should be able to adjust it to my needs :redface:

 

[jmcilhinney]

So basically you're saying that you want a list of product numbers that are present in both DataTables, right?

 

[Klingon66]

Yes , that's right :courage:

 

[jmcilhinney]

There are a number of ways that you can do this. The simplest is a little LINQ:

Dim productNumbers = dataTable1.AsEnumerable().
                                Select(Function(row) row.Field(Of String)("ProductNumber")).
                                Intersect(dataTable2.AsEnumerable().
                                                     Select(Function(row) row.Field(Of String)("ProductNumber")))

If you're not comfortable with LINQ though, you can stick to the more standard practices of lists and loops:

Dim productNumbers As New List(Of String)

For Each row1 As DataRow In dataTable1.Rows
    For Each row2 As DataRow In dataTable2.Rows
        Dim productNumber = CStr(row1("ProductNumber"))

        If CStr(row2("ProductNumber")) = productNumber Then
            productNumbers.Add(productNumber)

            Exit For
        End If
    Next
Next

 

[Klingon66]

Thanks for your solution .

without LINQ i will get it to work but i like challenge so i am gonna try to make it work with LINQ hehehe
Ones i understand LINQ a bit better , it seems less code .

I assume i have to ref LINQ and then import it ? ( i will google it , lol , i should find enough documentation about LINQ )

It's my last workday today , vacation at last lol ( i promise feedback when it's up and running or if i have another question .)

 

[jmcilhinney]

it seems less code .

That is one of the big attractions. LINQ can often turn rather long code into one or two lines. Be careful not to overdo it though, as sometimes complex LINQ statements can actually be harder to read because they are so dense. The aim should be to make your code as concise as possible, i.e. to make it as short as you can without sacrificing readability.

I assume i have to ref LINQ and then import it ?

You probably already have everything you need as far as references and imports go. Note that, while they all use the same basic syntax, there are numerous LINQ providers; some built into the Framework and some available separately from Microsoft or third-parties. What I've shown above uses LINQ to DataSet. If you query simple arrays or collections then you're using LINQ to Objects. If you use the Entity Framework for data access then you're using LINQ to Entities.

It can be important to know which provider is in use at any particular time because LINQ queries use deferred execution. For instance, if you call the Where method to filter data then a query is generated but not actually executed. The execution doesn't actually occur until you try to access the result, e.g. by calling ToArray, calling First or Single or enumerating with a For Each loop. If you enumerate a query twice then you execute the query twice. That's not a big deal if it's a local array but if your querying a database with EF then it's very undesirable.

 

[Klingon66]

OK thanks for the explanation .

I ran into an error trying to msgbox(productnumber) says can't convert to string but i wanna preview the results just to see if it works .

 

[jmcilhinney]

As I mentioned, LINQ queries actually defer execution until you try to access the data. The first code snippet in post #4 actually produces an unexecuted query in the form of an IEnumerable(Of String). You can access the results of that query in any way in which you can access any other IEnumerable(Of String). If you just want to preview the data then the easiest option is to call String.Join, which accepts an IEnumerable(Of String) as an argument and will join all the items it contains with a delimiter in between, e.g.

MessageBox.Show(", " productNumbers)

Other options include the use of a For Each loop.

If that doesn't address your issue, please show the actual code you're using, the exact error message reported and where in the code the error is generated.

 

[Klingon66]

Can you throw in a Trim function ?
It doesn't find a match because of whitespaces so i need a trim before compare , i tried in SQL but didn't work .

 

[Klingon66]

Got it to work now .
Final code :
'Dim productNumbers = dataTable1.AsEnumerable().
Select(Function(row) row.Field(Of String)("ProductNumber").trim).
Intersect(dataTable2.AsEnumerable().
Select(Function(row) row.Field(Of String)("ProductNumber").trim))