I have a form with a 2 listboxes. Here, listbox1 is populated with names of actors and actresses. If a name is selected from listbox1, listbox2 should show the title(s) of movie(s) where that name is involved. If another name is selected, listbox2 will show title(s) of movie(s) that 2 name is involved.
STRSQL = "select mTitle from selectmovie where cName = '" & lstNames.SelectedItem & "'"
myCmd.Connection = myConn
myCmd.CommandText = STRSQL
myReader = myCmd.ExecuteReader
If (myReader.Read()) Then
myAdptr.SelectCommand = myCmd
lstTitle.DisplayMember = "mTitle"
lstTitle.ValueMember = "mTitle"
If myDataTable.Rows.Count > 0 Then
For i As Integer = 0 To myDataTable.Rows.Count - 1
Catch ex As Exception
Your main problem seems to be that you do not clear your
lstTitle control before re-loading it with the new selection. Therefore, each time you select a new name, it will add all the titles for that name to the existing list of titles that are already loaded. Also, instead of using an integer to iterate all the indexes, it is easier to just use a
For Each loop:
lstTitle.Items.Clear() For Each row As DataRow In myDataTable.Rows lstTitle.Items.Add(row("mTitle")) Next
However, I must also mention that you really should also be using a parameter in your query rather than dynamically building the SQL statement like that, for instance:
myCmd.CommandText = "select mTitle from selectmovie where cName = @name" myCmd.Parameters.AddWithValue("name", lstNames.SelectedItem)
To select all the movies where all of the multiple selected actors are involved, you would need to add an additional condition to your where clause for each actor, for instance:
Dim builder As New StringBuilder() builder.Append("select distinct mTitle from selectmovie where ") For i As Integer = 0 to lstNames.SelectedItems.Count - 1 Dim parameterName As String = "@name" & i.ToString() If i <> 0 Then builder.Append("and ") End If builder.Append(parameterName) builder.Append(" in (select cName from selectmovie where mTitle = m.mTitle) ") myCmd.Parameters.AddWithValue(parameterName, lstNames.SelectedItems(i)) Next myCmd.CommandText = builder.ToString()