[RESOLVED] Passing list of (integer) to select statement

[Nitesh]

Hi Guys,

I have a function that returns a Datatable. I am passing a list of Integer as an argument. Then I thought tis would work.

        Public Function GetEventsByEventType(ByVal EventType As List(Of Integer), ByVal DayInterval As Integer) As DataTable

Using con As New MySqlConnection(strCon)

                If Not EventType Is Nothing Then
                  
                    Dim cmd As MySqlCommand = New MySqlCommand("SELECT ID,Activity,Teams,Venue,Timing,Eventdate FROM tblcal WHERE ((EventDate >= CURDATE()) AND (EventDate <= DATE_ADD(CURDATE(), INTERVAL 7 DAY))) AND (EventType IN(?EventType)) ORDER BY EventDate;", con)

                    cmd.Parameters.AddWithValue("?DayInterval", DayInterval)
                    cmd.Parameters.AddWithValue("?EventType", String.Join(",", EventType))

                    con.Open()

                    Dim reader As MySqlDataReader = cmd.ExecuteReader

                    _data.Load(reader)

                    con.Close()
                    reader.Close()
                    cmd.Dispose()

                    Return _data
                End If
            End Using
End Function

as you can see. I use string.join to convert the list of integer to something like this. '1,2,3'. Problem is that is read as a string so i get the incorrect results. I need to have where EventType IN(1,2,3). what is the best fix here. Please help

[jmcilhinney]

Each parameter can only be used for one value. If you have a list of values then you need a list of parameters. You have two options:

1. Use string concatenation to insert the values as literals.
2. Add the parameters using a loop.

Option 1 is bad. Option 2 is good. Follow the CodeBank link in my signature and you'll find a thread dedicated to Parameters With A SQL IN Clause.

[Nitesh]

Thanks JM. Elegant solution from you as usual Here is my final working function

Public Function GetEventsByEventType(ByVal EventType As List(Of Integer), ByVal DayInterval As Integer) As DataTable
            Dim cmd As MySqlCommand = New MySqlCommand

            Using con As New MySqlConnection(strCon)

                Dim query As New StringBuilder("SELECT ID,Activity,Teams,Venue,Timing,Eventdate FROM tblcal WHERE ((EventDate >= CURDATE()) AND (EventDate <= DATE_ADD(CURDATE(), INTERVAL ?DayInterval DAY)))")

                Select Case EventType.Count
                    Case 0
                        query.Append(" WHERE EventType = ?EventType")
                        cmd.Parameters.AddWithValue("?EventType", EventType.Item(0))

                    Case Is > 0

                        query.Append(" AND EventType IN (")

                        Dim paramName As String

                        For index As Integer = 0 To EventType.Count - 1 Step 1
                            paramName = "?EventType" & index

                            If index > 0 Then
                                query.Append(", ")
                            End If

                            query.Append(paramName)
                            cmd.Parameters.AddWithValue(paramName, EventType.Item(index))
                        Next index

                        query.Append(")")

                End Select

                cmd.Parameters.AddWithValue("?DayInterval", DayInterval)

                cmd.CommandText = query.ToString
                cmd.Connection = con

                con.Open()

                Dim reader As MySqlDataReader = cmd.ExecuteReader

                _data.Load(reader)

                con.Close()
                reader.Close()
                cmd.Dispose()

                Return _data

            End Using

        End Function