How to Datatable select with multiple FirstOrDefault variables in VB.NET

Question

I want to do datatable select multiple but it doesn't work yet. Please guide.
For 2 variables, CodeProduct and Barcode, I've succeeded but to add 2 variables, ColorCode and Size, I still fail

Public dt As New DataTable()
    Protected Overrides Sub OnLoad(e As EventArgs)
        MyBase.OnLoad(e)
        'dt = New DataTable
        dt.Columns.AddRange({
        New DataColumn("No", GetType(Integer)),
        New DataColumn("CodeProduct", GetType(String)),
        New DataColumn("Barcode", GetType(String)),
        New DataColumn("ColorCode", GetType(String)),
        New DataColumn("Size", GetType(String)),
        New DataColumn("Qty", GetType(Integer))
    })
        Grid.DataSource = dt
    End Sub

 Private Sub process()
        Dim Barcode = TextBox1.Text.Trim()
        Dim CodeProduct = TextBox2.Text.Trim()
        Dim ColorCode = "test"
        Dim Size = "test"
        Dim row As DataRow = Nothing
        If dt.Rows.Cast(Of DataRow).Any() Then
            'row = If(dt.Select($"Barcode = '{Barcode}'").FirstOrDefault(), dt.Select($"Codeproduct = '{CodeProduct }'").FirstOrDefault())
            row = dt.Select($"Barcode = '{Barcode}'" And $"Codeproduct = '{CodeProduct }'").FirstOrDefault()
        End If
    End Sub

The argument you pass to the `Select` method is simply a SQL `WHERE` clause. Such a clause is simply a Boolean expression. How do you usually construct a Boolean expression using multiple criteria? Using `AND` and `OR` operators. You should have read the relevant documentation and learned that for yourself. The `Select` documentation provides a link to the `Expression` documentation and that provides this example: `(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'`. ALWAYS read the documentation first. — jmcilhinney, Jul 24 '23 at 12:25
@jmcilhinney ,Thank you for your reply .From your comment I did not find the documentation link you meant — roy, Jul 24 '23 at 14:28
I'm not really 100% sure what your code is even attempting to do. At a guess, along the same lines as other comments, thinking something like `dt.Select($"Barcode = '{Barcode}' AND "Codeproduct = '{CodeProduct }' AND .....").FirstOrDefault()` is what you're asking for? — Hursey, Jul 25 '23 at 00:01
There's simply no excuse for "not finding" the documentation. All you have to do is click the method name in the VS code window and press F1 to be taken directly to the documentation for that method. Context-sensitive Help has been thing in VS for decades and Windows even longer. You can also get to the documentation in general via the Help menu in VS. Everyone should have a bookmark/favourite in their browser to the search page too. [Here](https://learn.microsoft.com/en-au/dotnet/api/)'s the Australian one from my browser. — jmcilhinney, Jul 25 '23 at 01:49
@Hursey , Thank you for your response. so I want to match from textbox to datagridview via datatable to qty column if same then just add value in "Qty" column. `row = If(dt.Select($"Barcode = '{Barcode}'").FirstOrDefault(), dt.Select($"Codeproduct = '{CodeProduct }'").FirstOrDefault())`. I tried 2 columns from your previous recommendation code like this no error then tried the code from you — roy, Jul 25 '23 at 02:49
@Hursey, and make like this `row = dt.Select($"Barcode = '{Barcode}'" And $"Codeproduct = '{CodeProduct }'").FirstOrDefault()` but There is an error like this `Conversion from string "Barcode = '1000'" to type 'Long' is not valid.` — roy, Jul 25 '23 at 02:49
@Hursey , `row = If(dt.Select($"Barcode = '{Barcode}'").FirstOrDefault(), dt.Select($"Codeproduct = '{CodeProduct }'").FirstOrDefault())` if i use this code then no error but i want to add 2 more columns datatable in this code — roy, Jul 25 '23 at 02:54
@jmcilhinney , `The Select documentation provides a link to the Expression documentation and that provides this example: (LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'` from my code `row = If(dt.Select($"Barcode = '{Barcode}'").FirstOrDefault(), dt.Select($"Codeproduct = '{CodeProduct }'").FirstOrDefault())` to `row = dt.Select($"Barcode = '{Barcode}'" And $"Codeproduct = '{CodeProduct }'").FirstOrDefault()` Please guide from you — roy, Jul 25 '23 at 02:58
Please don't flood comments with code, update your question with extra details. Doing this just makes the whole thing hard to read. Also not 100% sure what the question is now, it looks like in your last comment you've _almost_ got the answer. Join conditions using logic operators in a single select (You've got an extra '$' in there btw) — Hursey, Jul 25 '23 at 03:03
You don't need to ask us whether you should use specific code or not. You should try that code for yourself and then you know. If it works, you can post your own answer to the question. If it doesn't work, you can update the question and show us what you tried and tell us what happened when you tried it. As suggested, DO NOT post fat wads of code in comments. It's too hard to read and no one should have to read the comments in order to understand the question anyway. If the parameters of your question change, change the question to include ALL the relevant information. — jmcilhinney, Jul 25 '23 at 03:24
If you are getting _Conversion from string "Barcode = '1000'" to type 'Long' is not valid_ errors. Honestly, if you had read the error it's telling you the answer. Barcode is and integer type and you are comparing it to a String. We don't know your data types so have to assume base off the code you are posting. Either convert "1000" to an integer, or convert Barcode to a string, easy fix. — Hursey, Jul 25 '23 at 03:44
@Hursey , `row = If(dt.Select($"Barcode = '{Barcode}'").FirstOrDefault(), dt.Select($"Codeproduct = '{CodeProduct }'").FirstOrDefault())` If I use this code then there is no error and "barcode" which means data type string . `Conversion from string "Barcode = 'TEST'" to type 'Long' is not valid.` I tried with strings also the same — roy, Jul 25 '23 at 04:23
@Hursey , `If you are getting Conversion from string "Barcode = '1000'" to type 'Long' is not valid errors. Honestly, if you had read the error it's telling you the answer. Barcode is and integer type and you are comparing it to a String.` So there is a difference between your recommendation code and my own code — roy, Jul 25 '23 at 04:25
@Hursey , `Please don't flood comments with code, update your question with extra details. Doing this just makes the whole thing hard to read. Also not 100% sure what the question is now, it looks like in your last comment you've almost got the answer. Join conditions using logic operators in a single select (You've got an extra '$' in there btw)` Alright I updated in posting the code — roy, Jul 25 '23 at 04:36

score 0 · Accepted Answer · answered Jul 25 '23 at 04:54

as per solution from Here's a link! and although the solution is made in C# by @MichaelPetrotta but provide the best solution for me

If dt.Rows.Cast(Of DataRow).Any() Then
row = dt.Select($"Barcode = '{Barcode}' AND Codeproduct = '{CodeProduct}' AND ColorCode = '{ColorCode}' AND Size = '{Size}'").FirstOrDefault()
End If

How to Datatable select with multiple FirstOrDefault variables in VB.NET

1 Answers1

Linked