hello, this is my first post here. I'm working on my movie library project now in vb2008. I have a form with 3 listboxes, a remove button and find button. Listbox1 is populated with names of actors from a table in the mysql database and selection mode = one. User can select items from listbox1 one by one and each selected item will be listed in listbox2. If user want to remove a selected item from the listbox2, remove button will do the work. I already made this part. My problem is I don't have an idea how to check those selected items in the database and show them on the listbox3. Here's how my table in the database looks:
id | name | title
1 | actor1 | movie1
2 | actor2 | movie1
3 | actor3 | movie1
4 | actor4 | movie1
5 | actor1 | movie2
6 | actor3 | movie2
7 | actor1 | movie3
8 | actor1 | movie4
9 | actor4 | movie5
10 | actor5 | movie5
So, say user chose actor1, actor3, and actor5, these three will be listed in the listbox2. If find button is clicked, listbox3 should show movie1, movie2, movie3, movie4, and movie5. If user remove actor1 from listbox2 and clicked the find button, listbox3 will show movie1, movie2, movie5. I tried to search the internet to find any idea about this but failed. I'm very thankful for this forum because this helped me accomplish the other parts of my current project so I decided to ask for help here. I'm just a newbie in vb2008 as you can say. Please help me solve this, any idea will be greatly appreciated. Thanks in advance, God bless.

Recommended Answers

All 2 Replies

I guess you will need to construct a MySql Command like:

SELECT title FROM table WHERE name = 'actor1' OR name = 'actor2' OR name = 'actor3';

To create this sentence dinamically, you'll need verify if exist any item in the listbox2 and, if they exist, cicle over the items in the listbox2 creating the where part like (untested):

If listbox2.Items.Count >0 then
  Dim strSQL as string = "SELECT title FROM table WHERE "
  Dim strWhere as string = ""
  For I as Integer = 0 to listbox2.Items.Count-1
    If strWhere.Length > 0 then
       strWhere &= " OR "
    End If
    Dim itemName as string = CStr(listbox.Items(I))
    strWhere &= " name = '" & itemName & "' "
  strSQL &= strWhere & ";"
  '  here you must add the necessary code to:
  '    create a command using the strSQL as command string, and the existing connection to the DB 
  '    create a DataAdapter from the command
  '    and fill a movies DataTable with it
  '  then, use it as the datasource for the listbox3 and refresh it.
End If

Hope this helps

thanks lolafuertes for the suggestion, I tried your code..no error but it only identifies the last selected item from listbox1 which is the last name listed in listbox2. If I remove a selected item from listbox2, the result doesn't change. Actually I already had a code for this that's working perfectly in the previous design of my form. In my previous design I only have 2 listboxes and a find button. Listbox1's selection mode is multisimple. There, user can select as many actor as he wants from listbox1. After that, when find button is clicked, the result will be shown on listbox2. If user disselect an item from listbox1 then cliked the find button, the result shown on listbox2 will change depending on the the result of the remaining selected items in listbox1. So my previous code is like this:

Call Connect()
            With Me
                Dim dt As New DataTable
                Dim cmd As New MySqlCommand
                Dim reader As MySqlDataReader
                Dim adptr As New MySqlDataAdapter
                    cmd.Connection = myConn
                    cmd.CommandText = "select title from movielibrary where name = @act"
                    cmd.Parameters.AddWithValue("act", lstNames.SelectedItem)
                    reader = cmd.ExecuteReader
                    If (reader.Read()) Then
                        adptr.SelectCommand = cmd
                        lstTitle.DisplayMember = "title"
                        lstTitle.ValueMember = "title"

                        For Each row As DataRow In dt.Rows
                        Dim builder As New StringBuilder()
                        builder.Append("select distinct title from movielibrary where ")
                        For y As Integer = 0 To lstNames.SelectedItems.Count - 1
                            Dim parameterName As String = "@act" & y.ToString()
                            If y <> 0 Then
                                builder.Append("and ")
                            End If
                            builder.Append(" in (select name from movielibrary where title = t.title) ")
                            cmd.Parameters.AddWithValue(parameterName, lstNames.SelectedItems(y))
                        cmd.CommandText = builder.ToString()

                    End If
                Catch ex As Exception
                End Try
                cmd = Nothing
                reader = Nothing
                Call Disconnect()
            End With

Presently, the form I'm working now is the form that I first created. I just made some changes: I set the lstNames' selection mode to one, added another listbox(lets name it lstSelected) where the selected name from the lstNames will be listed and a remove button. I tried modifying the code but failed to make it work. Can you please help me correct it? thanks again

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.