-
Feb 20th, 2018, 05:50 AM
#1
Thread Starter
Member
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
-
Feb 20th, 2018, 06:58 AM
#2
Addicted Member
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
-
Feb 20th, 2018, 08:57 AM
#3
Thread Starter
Member
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
-
Feb 20th, 2018, 09:18 AM
#4
Addicted Member
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
-
Feb 20th, 2018, 09:28 AM
#5
Thread Starter
Member
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
-
Feb 20th, 2018, 09:31 AM
#6
Thread Starter
Member
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
-
Feb 20th, 2018, 09:45 AM
#7
Addicted Member
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
-
Feb 20th, 2018, 12:42 PM
#8
Thread Starter
Member
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
-
Feb 21st, 2018, 01:58 AM
#9
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|