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

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.

Answer

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)

Advertisement

Answer