VB .NET Paramaterized SQL call not correctly parsing IN clause arguments

Question

So I am currently working on a project where I am looking to retrieve all data from a table matching a modifyable list on the screen. Here is a sample of the code that I am using to make the call

Public Sub GetAnimals(ByReg selectedTypes As String)
    Dim strSql As String
    Dim pselectedTypes As SqlClient.SqlParameter = New SqlClient.SqlParameter("@pselectedTypes", selectedTypes)
    Dim strDBConnection As String = DBConnection.GetConnection.ConnectionString
    Dim rs As DataSet = new DataSet

    strSql = "SELECT * FROM PetInformation WHERE AnimalType IN (@pselectedTypes)"

    Using sqlConn As SqlClient.SqlConnection = New SqlClient.SqlConnection(strDBConnection)
        sqlConn.Open()
        Using sqlcmd As SqlClient.SqlCommand = New SqlClient.SqlCommand
            With sqlcmd
                .Connection = sqlConn
                .CommandTimeout = DBConnection.DLLTimeout
                .CommandType = CommandType.Text
                .CommandText = strSql
                .Parameters.Add(pselectedTypes)
                Using sqlda As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(sqlcmd)
                    sqlda.Fill(rs)
                End Using
            End with
        End Using
    End Using

    ' Data Calculations
End Sub

This then makes the SQL query look something like this. For this example I'm using my list of animals as just cats and dogs.

exec sp_executesql N'SELECT * FROM PetInformation WHERE AnimalType IN (@pselectedTypes)',N'@pselectedTypes nvarchar(22),@pselectedTypes='''cat'',''dog'''

So everything looks all fine and dandy but when I go to run it and execute it I don't get any results back. If I change the sql to look like either one of these I get data back, but since this SQL is generated that's not really an option w/o switching away from parameterized sql, which I don't want to do.

exec sp_executesql N'SELECT * FROM PetInformation WHERE AnimalType IN (''cat'',''dog'')',N'@pselectedTypes nvarchar(22),@pselectedTypes='''cat'',''dog'''

or

SELECT * FROM PetInformation WHERE AnimalType IN ('cat','dog')

Any suggestions or ideas?

[Arrays and Lists in SQL Server - The Short Version](http://sommarskog.se/arrays-in-sql.html) is a good place to start. — Andrew Morton, Sep 30 '16 at 17:17
My bad on the MySQL and SQL Server, I added tags in a hurry and didn't check them. — swiftzor, Sep 30 '16 at 18:50
I have a feeling that selectedTypes starts as a generic collection? You can pass a generic collection to a stored procedure as a table valued parameter. Makes short work of this kind of thing. :D — Sean Lange, Sep 30 '16 at 19:15

score 0 · Answer 1 · answered Sep 30 '16 at 17:14

0

As far as I know, no db libraries allow parameterizing lists like you are trying. In those situations, you need to build your parameter list and query dynamically based on the number of elements in your list.

answered Sep 30 '16 at 17:14

Uueerdo

15,723
1
16
21

2

SQL Server 2008 and up support [Table Valued Parameters](https://msdn.microsoft.com/en-us/library/bb675163.aspx), which would allow you to pass an array or list as your `pselectedTypes` parameter. – Jacob Krall Sep 30 '16 at 17:18
I knew SQL Server supports table valued parameters, I just wasn't aware that support had made it down to the client api level. Good to know. – Uueerdo Sep 30 '16 at 17:22

VB .NET Paramaterized SQL call not correctly parsing IN clause arguments

1 Answers1