Getting selected listbox items values to display i

问题:

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 & "'"
        Try
            myCmd.Connection = myConn
            myCmd.CommandText = STRSQL
            myReader = myCmd.ExecuteReader
            If (myReader.Read()) Then
                myReader.Close()
                myAdptr.SelectCommand = myCmd
                myAdptr.Fill(myDataTable)
                lstTitle.DisplayMember = "mTitle"
                lstTitle.ValueMember = "mTitle"

                If myDataTable.Rows.Count > 0 Then
                    For i As Integer = 0 To myDataTable.Rows.Count - 1
                        lstTitle.Items.Add(myDataTable.Rows(i)("mTitle"))
                    Next
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        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/

回答1:

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()