VBeginner VBeginner - 1 year ago 62
Vb.net Question

Getting selected listbox items values to display in another listbox using vb 2008

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.

Call Connect()
With Me
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
End If
End If
Catch ex As Exception
End Try
End With

There's no error. When I select 1 item the result is correct but it leaves many space..here the screen shot of my form: http://www.flickr.com/photos/92925726@N06/8445945758/in/photostream/

The output becomes worse when I selected actor3: http://www.flickr.com/photos/92925726@N06/8445945724/in/photostream/

Answer Source

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:

For Each row As DataRow In myDataTable.Rows

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(" in (select cName from selectmovie where mTitle = m.mTitle) ")
    myCmd.Parameters.AddWithValue(parameterName, lstNames.SelectedItems(i))
myCmd.CommandText = builder.ToString()