How to use parameters command with IN operator in mysql and vb.net

Question

I need to use IN operator in WHERE clause so I tried the next code with my SQL statement but it gives me the record for the first number only:

MyVar_AccID= "110,112,113"

Dim MyVar_SqlStr_Main As String = "SELECT 
                                    FatoraID, 
                                    FatoraRef,
                                    FatoraCode, 
                                    FatoraDate, 
                                    (D.AccName) AS DName,
                                    FatoraQuan,
                                    CONCAT(CategoryName,' ',ProductName) AS ProdName,
                                    FatoraSalePrice, 
                                    (C.AccName) AS CusName,
                                    FatoraPurPrice,
                                    (R.AccName) AS ResoName,
                                    FatoraPalletQuan,
                                    FatoraPalletPrice,
                                    FatoraPurTotal, 
                                    FatoraCustomer, 
                                    FatoraDis,
                                    FatoraPlus,
                                    FatoraSaleTotal,
                                    FatoraDriver, 
                                    FatoraCarNo, 
                                    FatoraDriverCost, 
                                    FatoraDriverCostTotal1,
                                    FatoraDriverCostTotal2,
                                    FatoraDriverPrice,
                                    FatoraDriverPayStatus, 
                                    FatoraReso, 
                                    FatoraProduct,
                                    FatoraDetails1,
                                    FatoraDetails2,
                                    FatoraDetails3,


                                    (FatoraPalletQuan * FatoraPalletPrice) AS PalletTotalPrice 
                                    FROM tblfatora F 
                                    INNER JOIN tblproducts P ON
                                    P.ProductID = F.FatoraProduct
                                    INNER JOIN tblaccounts R ON
                                    R.AccID = F.FatoraReso
                                    INNER JOIN tblaccounts C ON
                                    C.AccID = F.FatoraCustomer
                                    LEFT JOIN tblaccounts D ON
                                    D.AccID = F.FatoraDriver
                                    INNER JOIN tblcategories CT ON
                                    CT.CategoryID = P.ProductCategory
                                    Where 
                                    (FatoraReso IN (@FatoraReso))
                                    
                                    ORDER BY FatoraDate DESC"


        xCmd = New MySqlCommand(MyVar_SqlStr_Main, Conn)

        xCmd.Parameters.Add("@FatoraReso", MySqlDbType.VarChar).Value = MyVar_AccID

I tried also:

Where 
                                (FatoraReso IN ("@FatoraReso"))

but didn't give me results, how can I solve that problem, please note that I don't know the number of the codes so maybe it will be (1,2,3,4,5) or less or more.

The problem is that a parameterized "IN" clause can't accept more than a single value. Here's one workaround: Parameterized IN clauses with ADO.NET and LINQ — paulsm4
– paulsm4, Commented Dec 7, 2020 at 0:49

GMB · Accepted Answer · 2020-12-07 00:43:19Z

1

It seems like you want to check if a value belongs to a comma-separated list. You can't do that with IN, which expects a list of values, not a single string of values.

A generic solution uses string functions:

where concat(',', @fatorareso, ',') like concat('%,', fatorareso, ',%')

In MySQL, however, you can use handy string function find_in_set():

where find_in_set(fatorareso, @fatorareso)

answered Dec 7, 2020 at 0:43

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ysth Over a year ago

Note that this will make the query unable to use an index on fatorareso

Collectives™ on Stack Overflow

How to use parameters command with IN operator in mysql and vb.net

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related