vb table adapter does not allow more than one parameter in the IN clause

Question

What I need to achieve is to send a list of unknown QTY of values to a Sql server NOT IN clause but can only achieve this with singular values. below is my Sql statement:

SELECT        SorMaster.LastInvoice
            , SorMaster.SalesOrder
            , SorMaster.OrderStatus
            , ArCustomer.RouteCode
            , SorMaster.Customer
            , SorMaster.CustomerName
            , SorMaster.CustomerPoNumber
            , SorMaster.OrderDate
            , SorMaster.DateLastInvPrt
            , ArInvoice.InvoiceBal1
            , ArInvoice.TermsCode
FROM        SorMaster AS SorMaster 
INNER JOIN  ArCustomer AS ArCustomer ON SorMaster.Customer = ArCustomer.Customer 
INNER JOIN  ArInvoice AS ArInvoice ON SorMaster.LastInvoice = ArInvoice.Invoice
WHERE  (SorMaster.OrderStatus = '9') 
  AND (SorMaster.Branch LIKE 'J%') 
  AND (SorMaster.DocumentType = 'O') 
  AND (SorMaster.LastInvoice > @Last_Invoice) 
  AND (SorMaster.OrderDate > DATEADD(Month, - 4, GETDATE())) 
  AND (SorMaster.LastInvoice NOT IN (@ExclusionList))
ORDER BY SorMaster.LastInvoice

The @ExclusionList value is generated by this code as a string from a listbox:

Dim exclusion As String = ""
            If MenuForm.ExclusionCB.Checked = True Then
                For i = 0 To MenuForm.ExclusionLB.Items.Count - 2
                    exclusion = exclusion & MenuForm.ExclusionLB.Items(i) & ","
                Next
                exclusion = exclusion & MenuForm.ExclusionLB.Items(MenuForm.ExclusionLB.Items.Count - 1)
            Else
                exclusion = ""
            End If

I have also tried sending the entire listbox as a collection.

Does anyone know how I can send more than one value (something like 1,2,3,4,5,6) and have sql understand that these is more than one? I won't have an issue with the SELECT statement changing, just as long as it returns the same information.

The reason I need this with the exception list, is our remote DB PK is on the Salesorder column and the local DB is on the LastInvoice column

Hope this makes sense. if you need more info, please let me know

Answer 1

You can send it as a string and use dynamic sql. Here's a simple example how to do that.

DECLARE @vals VARCHAR(50) = '1,2,3,4,5,6'
DECLARE @sql VARCHAR(MAX) = 'SELECT * FROM TABLE WHERE FIELD1 IN'

SET @sql = @sql + ' (' + @vals + ')'
-- @sql = 'SELECT * FROM TABLE WHERE FIELD1 IN (1,2,3,4,5,6)'

EXEC (@sql)