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

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.

Code:

---

        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

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.

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

Code:

---

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

---