Results 1 to 9 of 9

Thread: Selecting more than one item in a listbox

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2018
    Location
    UK
    Posts
    49

    Selecting more than one item in a listbox

    Hi all,

    I have a list of student names in a listBox,(studentList)I click on a name in the box and get all the students details up ie name, year subject etc.The code then gets the details from the database(in my case it's access) the displays it in a datagridview.
    The first part is the creation of a sql query like this
    Code:
     Dim defaultSQL As String = "SELECT * FROM studentfile "
    Then I use the next piece of code to add the selected item(student name) from the studentList(box).So the sql query is extended to encompass the selection from the box
    Code:
     If studentList.SelectedIndex > -1 Then 
                
                If isFirstColumn Then
                    student = "WHERE student = '" & x & "' " ' 
                Else
                    student = "AND student = '" & x & "' "
                End If
                isFirstColumn = False
            End If
    the variable x stores the selected item in the studentList box and completes the sql query.
    This works well if I just wanted to choose one student at a time but what if I wanted to select more than one student.
    I know I can use SelectedMode property to highlight more than one item,but it will require a variation on the code to get this to work,any suggestions??
    cheers
    al
    Last night I dreamt I went to Manderley again

  2. #2
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    196

    Re: Selecting more than one item in a listbox

    Code:
    For Each Item As Object In StudentList.SelectedItems
    
            Next
    (i think this was what you where looking for?)
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  3. #3

    Thread Starter
    Member
    Join Date
    Feb 2018
    Location
    UK
    Posts
    49

    Re: Selecting more than one item in a listbox

    Hi Goggy

    Thanks for the reply.Where would your code go,I tried this:
    Code:
    For Each Item As Object In StudentList.SelectedItems
    If studentList.SelectedIndex > -1 Then 
                
                If isFirstColumn Then
                    student = "WHERE student = '" & x & "' " ' 
                Else
                    student = "AND student = '" & x & "' "
                End If
                isFirstColumn = False
            End If
    Next
    but that didn't work.am I to loop all of the List boxes?.Not too sure where the loop should go.Any help much appreciated
    regards
    al
    Last night I dreamt I went to Manderley again

  4. #4
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    196

    Re: Selecting more than one item in a listbox

    The SelectedItems property is collection holding all selected items.

    Code:
    For Each Item As Object In StudentList.SelectedItems
                If isFirstColumn Then
                    student = "WHERE student = '" & x & "' " ' 
                Else
                    student = "AND student = '" & x & "' "
                End If
                isFirstColumn = False
    Next
    I think something like so would do the trick


    Also if your formiliar with linq you could try something like so

    Code:
            Dim SQL_Part As String = String.Empty
    
            If Me.ListBox1.SelectedItems.Count > 0 Then
                SQL_Part = $"Where Student = {Strings.Join((From I As Object In Me.ListBox1.SelectedItems Select I).ToArray, " And Student = ")}"
            End If
    
            MsgBox(SQL_Part)
    Last edited by Goggy; Feb 20th, 2018 at 09:28 AM.
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  5. #5

    Thread Starter
    Member
    Join Date
    Feb 2018
    Location
    UK
    Posts
    49

    Re: Selecting more than one item in a listbox

    Hi Goggy

    Thanks for the reply.Where would your code go,I tried this:
    Code:
    For Each Item As Object In StudentList.SelectedItems
    If studentList.SelectedIndex > -1 Then 
                
                If isFirstColumn Then
                    student = "WHERE student = '" & x & "' " ' 
                Else
                    student = "AND student = '" & x & "' "
                End If
                isFirstColumn = False
            End If
    Next
    but that didn't work.am I to loop all of the text boxes?.Not too sure where the loop should go.Any help much appreciated
    regards
    al
    Last night I dreamt I went to Manderley again

  6. #6

    Thread Starter
    Member
    Join Date
    Feb 2018
    Location
    UK
    Posts
    49

    Re: Selecting more than one item in a listbox

    Hi Goggy,
    I tried the code as you suggested and I keep getting a "Syntax error in FROM clause" up.The code works OK if I just make one selection from the list but with two I get the error
    any suggestions much appreciated
    cheers al
    Last night I dreamt I went to Manderley again

  7. #7
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    196

    Re: Selecting more than one item in a listbox

    hmmm strange...

    When i tested here, i could select multiple items and it would work just fine...
    Could you please post your full code. (I used a listbox, are you?)
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  8. #8

    Thread Starter
    Member
    Join Date
    Feb 2018
    Location
    UK
    Posts
    49

    Re: Selecting more than one item in a listbox

    Hi Goggy,
    Here is the complete original code as requested(minus any of your modifications).This works fine if it is just for the one selection per listBox
    Code:
     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
            Dim con As New OleDb.OleDbConnection 
    
            Dim dbProvider As String
            Dim dbSource As String
    
            Dim ds As New DataSet
    
            Dim tables As DataTableCollection = ds.Tables
    
            Dim source1 As New BindingSource()
    
            Dim da As New OleDb.OleDbDataAdapter        
    
            dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
    
            dbSource = "Data Source = C:\Documents and Settings\albert\Desktop\studentmarks.accdb"
            con.ConnectionString = dbProvider & dbSource
    
            con.Open()
    
            Dim isFirstColumn As Boolean = True
            Dim student As String = ""
            Dim course As String = ""
            Dim grade As String = ""
            
         
            Dim x As String = studentList.Text
            Dim y As String = courseList.Text
            Dim z As String = gradeList.Text     
    
    
            Dim defaultSQL As String = "SELECT * FROM studentfile "       
    
            If studentList.SelectedIndex > -1 Then 
                If isFirstColumn Then
                    student = "WHERE student = '" & x & "' " 
                Else
                    student = "AND student = '" & x & "' "
                End If
                isFirstColumn = False
            End If
    
            If courseList.SelectedIndex > -1 Then 
                If isFirstColumn Then
                    course = "WHERE course = '" & y & "' " 
                Else
                    course = "AND course = '" & y & "' "
                End If
                isFirstColumn = False
            End If       
    
            If gradeList.SelectedIndex > -1 Then 
                If isFirstColumn Then
                    grade = "WHERE grade = '" & z & "' " 
                Else
                    grade = "AND grade = '" & z & "' "
                End If
                isFirstColumn = False
            End If       
    
            Dim sql As String = defaultSQL & student & course & grade
           
            da = New OleDb.OleDbDataAdapter(sql, con)
    
            da.Fill(ds, "topclass")
    
    
            Dim view1 As New DataView(tables(0))
    
            source1.DataSource = view1
            DataGridView1.DataSource = view1
            DataGridView1.Refresh()
            DataGridView1.DataSource = view1
    
            DataGridView1.Refresh()
    
            Dim cnt As Integer
    
            cnt = DataGridView1.Rows.Count
    
            TextBox1.Text = cnt - 1
          
            Dim dayclass As String = TextBox1.Text
            TextBox8.Text = dayclass
            con.Close()
    
            
        End Sub
    Regards
    al
    Last night I dreamt I went to Manderley again

  9. #9
    Addicted Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    196

    Re: Selecting more than one item in a listbox

    This what I got and tested (it seems all ok)

    Code:
    Public Class Form1
    
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
            With Me.Students
                .Items.Add("Hulk")
                .Items.Add("Spiderman")
                .Items.Add("Batman")
                .Items.Add("Aquaman")
            End With
    
            With Me.Courses
                .Items.Add("Web slinging")
                .Items.Add("Smashing")
                .Items.Add("Beeing rich")
                .Items.Add("Swimming")
            End With
    
            With Me.Grades
                .Items.Add("A")
                .Items.Add("B")
                .Items.Add("C")
                .Items.Add("D")
                .Items.Add("E")
                .Items.Add("F")
            End With
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim SQLString As String = "SELECT * FROM studentfile "
    
    
            If Me.Students.SelectedItems.Count > 0 Then
                SQLString &= $" Where Student = {Strings.Join((From I As Object In Me.Students.SelectedItems Select I).ToArray, " Or Student = ")}"
            End If
    
            If Me.Courses.SelectedItems.Count > 0 Then
                If SQLString.Contains(" Where ") Then
                    SQLString &= $" And (Course = {Strings.Join((From I As Object In Me.Courses.SelectedItems Select I).ToArray, " Or Course = ")})"
                Else
                    SQLString &= $" Where Course = {Strings.Join((From I As Object In Me.Courses.SelectedItems Select I).ToArray, " Or Course = ")}"
                End If
            End If
    
            If Me.Grades.SelectedItems.Count > 0 Then
                If SQLString.Contains(" Where ") Then
                    SQLString &= $" And (Grade  = {Strings.Join((From I As Object In Me.Grades.SelectedItems Select I).ToArray, " Or Grade = ")})"
                Else
                    SQLString &= $" Where Grade = {Strings.Join((From I As Object In Me.Grades.SelectedItems Select I).ToArray, " Or Grade = ")}"
                End If
            End If
    
            MsgBox(SQLString)
        End Sub
    End Class
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width