The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.
Visual basic 2012 filtering datagrid view data
I am a beginner in Visual basic 2012 and making a data management programme with visual basic 2012 as front end and MySql as back end. I want to filter data grid view table with combo box , checked listbox and List box (multiple selected) . When I select one item it works fine and when I select multiple items in the List box I do not have code can u help me please. I am able to get all the items of a column in the list box by following code
Query = "Select * from `rrrr.data`.`trial` "
COMMAND = New MySqlCommand(Query, MySqlConn)
READER = COMMAND.ExecuteReader
While READER.Read
Dim sCountry = READER.GetString("COUNTRY"
ListBox1.Items.Add(sCountr
End While
But for filtering data when I use below query it does not work ( for multiple selected or more than one item ) It does not work Query which I use is-
Query = "Select * from `rrrr.data`.`trial` Where `COUNTRY`in (''" & ListBox1.SelectedItem& "'' )"
Kindly help me out in filtering data using combo box , checked list box and List box ,. my requirement is to filter data as per the selected column and multiple selected items of the a selected column.
Query = "Select * from `rrrr.data`.`trial` "
COMMAND = New MySqlCommand(Query, MySqlConn)
READER = COMMAND.ExecuteReader
While READER.Read
Dim sCountry = READER.GetString("COUNTRY"
ListBox1.Items.Add(sCountr
End While
But for filtering data when I use below query it does not work ( for multiple selected or more than one item ) It does not work Query which I use is-
Query = "Select * from `rrrr.data`.`trial` Where `COUNTRY`in (''" & ListBox1.SelectedItem& "'' )"
Kindly help me out in filtering data using combo box , checked list box and List box ,. my requirement is to filter data as per the selected column and multiple selected items of the a selected column.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You appear to be using the ListBox1.SelectedItem property instead of the ListBox.SelectedItemS property.
I am unable to use ListBox1.SelectedItemS .My code is like this
Private Sub ListBox1_SelectedIndexChan
Dim dbDataSet As New DataTable
MySqlConn = New MySqlConnection
MySqlConn.ConnectionString
"server = localhost;userid=root;pass
Dim SDA As New MySqlDataAdapter
Dim bsource As New BindingSource
Try
MySqlConn.Open()
Dim Query As String
Query = "Select * from `ewosc-elint.data`.`trial`
COMMAND = New MySqlCommand(Query, MySqlConn)
SDA.SelectCommand = COMMAND
SDA.Fill(dbDataSet)
bsource.DataSource = dbDataSet
Me.DataGridView2.DataSourc
SDA.Update(dbDataSet)
MySqlConn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message
Finally
MySqlConn.Dispose()
End Try
End Sub
Private Sub ListBox1_SelectedIndexChan
Dim dbDataSet As New DataTable
MySqlConn = New MySqlConnection
MySqlConn.ConnectionString
"server = localhost;userid=root;pass
Dim SDA As New MySqlDataAdapter
Dim bsource As New BindingSource
Try
MySqlConn.Open()
Dim Query As String
Query = "Select * from `ewosc-elint.data`.`trial`
COMMAND = New MySqlCommand(Query, MySqlConn)
SDA.SelectCommand = COMMAND
SDA.Fill(dbDataSet)
bsource.DataSource = dbDataSet
Me.DataGridView2.DataSourc
SDA.Update(dbDataSet)
MySqlConn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message
Finally
MySqlConn.Dispose()
End Try
End Sub
The "SelectedIndexChanged" event is basically assumed to be implemented on a single selection listbox (as opposed to multiples). At least that's how it's usually used. With that in mind, I believe there is a far better ListBox event you can use instead: ItemSelectionChanged.
The ItemSelectionChanged event will fire anytime you select or deselect a country in your multi-select enabled Listbox. Try that and I think you'll have better luck.
The ItemSelectionChanged event will fire anytime you select or deselect a country in your multi-select enabled Listbox. Try that and I think you'll have better luck.
In my query I want my statement to appear like this
Select * from `ewosc-elint.data`.`trial`
I am using the below mentioned query
Select * from `ewosc-elint.data`.`trial`
It works fine for one selected item but not for multiple selected items like UK, USA, france. Can u please write the correct query.
Select * from `ewosc-elint.data`.`trial`
I am using the below mentioned query
Select * from `ewosc-elint.data`.`trial`
It works fine for one selected item but not for multiple selected items like UK, USA, france. Can u please write the correct query.
Dear David sir , ItemSelectionChanged is also not working . Below is my code can you kindly correct it for errors
Private Sub ListBox1_ ItemSelectionChanged (sender As Object, e As EventArgs) Handles ListBox1. ItemSelectionChanged
Dim dbDataSet As New DataTable
MySqlConn = New MySqlConnection
MySqlConn.ConnectionString
"server = localhost;userid=root;pass
Dim SDA As New MySqlDataAdapter
Dim bsource As New BindingSource
TextBoxtest.Clear()
Try
MySqlConn.Open()
Dim Query As String
Query = "Select * from `clu.data`.`trial` Where `COUNTRY`in ('" & ListBox1.SelectedItems & "')"
COMMAND = New MySqlCommand(Query, MySqlConn)
SDA.SelectCommand = COMMAND
SDA.Fill(dbDataSet)
bsource.DataSource = dbDataSet
Me.DataGridView2.DataSourc
SDA.Update(dbDataSet)
MySqlConn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message
Finally
MySqlConn.Dispose()
End Try
End Sub
Private Sub ListBox1_ ItemSelectionChanged (sender As Object, e As EventArgs) Handles ListBox1. ItemSelectionChanged
Dim dbDataSet As New DataTable
MySqlConn = New MySqlConnection
MySqlConn.ConnectionString
"server = localhost;userid=root;pass
Dim SDA As New MySqlDataAdapter
Dim bsource As New BindingSource
TextBoxtest.Clear()
Try
MySqlConn.Open()
Dim Query As String
Query = "Select * from `clu.data`.`trial` Where `COUNTRY`in ('" & ListBox1.SelectedItems & "')"
COMMAND = New MySqlCommand(Query, MySqlConn)
SDA.SelectCommand = COMMAND
SDA.Fill(dbDataSet)
bsource.DataSource = dbDataSet
Me.DataGridView2.DataSourc
SDA.Update(dbDataSet)
MySqlConn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message
Finally
MySqlConn.Dispose()
End Try
End Sub
Dim sql As String = ""
Dim cntries As String = ""
For Each i As Object In Me.ListBox1.SelectedItems
cntries += "'" & i.ToString() & "',"
Next
If cntries.Length > 0 Then
cntries = cntries.Remove(cntries.Length - 1, 1)
End If
sql = "SELECT * FROM dbo.theDB.Table1 WHERE county_Name IN (" & cntries & ")"
MessageBox.Show(sql)
The message (query) is showing correct now but still I am able to view data related to one selected item and not multiple items . It is getting closer to getting results but not showing correctly on data grid view. Have I done an thing wrong - Here is the code I have written
Private Sub ListBox1_SelectedItemsChan
Dim dbDataSet As New DataTable
MySqlConn = New MySqlConnection
MySqlConn.ConnectionString
"server = localhost;userid=root;pass
Dim SDA As New MySqlDataAdapter
Dim bsource As New BindingSource
Dim sql As String = ""
Dim cntries As String = ""
For Each i As Object In Me.ListBox1.SelectedItems
cntries += "'" & i.ToString() & "',"
Next
If cntries.Length > 0 Then
cntries = cntries.Remove(cntries.Len
End If
sql = "SELECT * FROM dbo.theDB.Table1 WHERE county_Name IN (" & cntries & ")"
MessageBox.Show(sql)
Try
MySqlConn.Open()
Dim Query As String
Query = "Select * from `ewosc-elint.data`.`trial`
COMMAND = New MySqlCommand(Query, MySqlConn)
SDA.SelectCommand = COMMAND
SDA.Fill(dbDataSet)
bsource.DataSource = dbDataSet
Me.DataGridView2.DataSourc
SDA.Update(dbDataSet)
MySqlConn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message
Finally
MySqlConn.Dispose()
End Try
End Sub
Private Sub ListBox1_SelectedItemsChan
Dim dbDataSet As New DataTable
MySqlConn = New MySqlConnection
MySqlConn.ConnectionString
"server = localhost;userid=root;pass
Dim SDA As New MySqlDataAdapter
Dim bsource As New BindingSource
Dim sql As String = ""
Dim cntries As String = ""
For Each i As Object In Me.ListBox1.SelectedItems
cntries += "'" & i.ToString() & "',"
Next
If cntries.Length > 0 Then
cntries = cntries.Remove(cntries.Len
End If
sql = "SELECT * FROM dbo.theDB.Table1 WHERE county_Name IN (" & cntries & ")"
MessageBox.Show(sql)
Try
MySqlConn.Open()
Dim Query As String
Query = "Select * from `ewosc-elint.data`.`trial`
COMMAND = New MySqlCommand(Query, MySqlConn)
SDA.SelectCommand = COMMAND
SDA.Fill(dbDataSet)
bsource.DataSource = dbDataSet
Me.DataGridView2.DataSourc
SDA.Update(dbDataSet)
MySqlConn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message
Finally
MySqlConn.Dispose()
End Try
End Sub
And replace my "dbo.theDB.Table1" with your own database/table names. Also note the differences in single quotes, etc. between my sql and yours. I'm only offering a similar type query...you'll need to use it as a guide/template.
I have changed from multiple extended to Multi simple and it is working now. Thanks a lot David Sir, U r great and extremely helpful.
Thanks a ton.
I may ask u more questions as u have become my favorite.
Thanks a ton.
I may ask u more questions as u have become my favorite.
Sir, David L. Hansen
I am populating my list box with following code but in the list box there are some duplicate items , how do I remove duplicate items of the list box. My code to get list box populated is as follows:-
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
MySqlConn = New MySqlConnection("server = localhost;userid=root;pass
MySqlConn.Open()
MySqlConn = New MySqlConnection
MySqlConn.ConnectionString
"server = localhost;userid=root;pass
Dim READER As MySqlDataReader
Try
MySqlConn.Open()
Dim Query As String
Query = "Select * from `country.data`.`trial` "
COMMAND = New MySqlCommand(Query, MySqlConn)
READER = COMMAND.ExecuteReader
While READER.Read
ListBox1.Items.Add(sCountr
End While
MySqlConn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message
Finally
MySqlConn.Dispose()
End Try
End Sub
For example in my list box there are two countries by name US , how do I make them one.
I am populating my list box with following code but in the list box there are some duplicate items , how do I remove duplicate items of the list box. My code to get list box populated is as follows:-
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
MySqlConn = New MySqlConnection("server = localhost;userid=root;pass
MySqlConn.Open()
MySqlConn = New MySqlConnection
MySqlConn.ConnectionString
"server = localhost;userid=root;pass
Dim READER As MySqlDataReader
Try
MySqlConn.Open()
Dim Query As String
Query = "Select * from `country.data`.`trial` "
COMMAND = New MySqlCommand(Query, MySqlConn)
READER = COMMAND.ExecuteReader
While READER.Read
ListBox1.Items.Add(sCountr
End While
MySqlConn.Close()
Catch ex As MySqlException
MessageBox.Show(ex.Message
Finally
MySqlConn.Dispose()
End Try
End Sub
For example in my list box there are two countries by name US , how do I make them one.
Sir, David L. Hansen
You have clarified all my doubts about listbox with the help of example and u r the first one to understand my (beginner) limitations. Thanks once again. What ever u told me about the list box about data filtering , is it also possible to do with Combobox or checked list box ( it will save some space). If yes kindly tell me with the help of my code for list box.
You have clarified all my doubts about listbox with the help of example and u r the first one to understand my (beginner) limitations. Thanks once again. What ever u told me about the list box about data filtering , is it also possible to do with Combobox or checked list box ( it will save some space). If yes kindly tell me with the help of my code for list box.
Also can you briefly explain your code below, as of now I have copied it and it is working.
Dim sql As String = ""
Dim cntries As String = ""
For Each i As Object In Me.ListBox1.SelectedItems
cntries += "'" & i.ToString() & "',"
Next
If cntries.Length > 0 Then
cntries = cntries.Remove(cntries.Len
End If
sql = "SELECT * FROM dbo.theDB.Table1 WHERE county_Name IN (" & cntries & ")"
MessageBox.Show(sql)
Dim sql As String = ""
Dim cntries As String = ""
For Each i As Object In Me.ListBox1.SelectedItems
cntries += "'" & i.ToString() & "',"
Next
If cntries.Length > 0 Then
cntries = cntries.Remove(cntries.Len
End If
sql = "SELECT * FROM dbo.theDB.Table1 WHERE county_Name IN (" & cntries & ")"
MessageBox.Show(sql)
Sure. So, the idea is basically to get the list of countries selected from the Listbox into the SQL query (that is to say, into the SELECT statement).
I was hoping to pull those selected countries directly from the ListBox in the line of code for the query but I couldn't remember how to do that. So I just created a string variable (cntries) to hold the countries, and I filled it by means of a loop. Once that variable was filled in the loop, placing it in the query is strait forward.
Note that the messagebox is for testing only and can be removed now that you have it working.
I was hoping to pull those selected countries directly from the ListBox in the line of code for the query but I couldn't remember how to do that. So I just created a string variable (cntries) to hold the countries, and I filled it by means of a loop. Once that variable was filled in the loop, placing it in the query is strait forward.
Note that the messagebox is for testing only and can be removed now that you have it working.
Yes, you can do this in a combobox, but I'd recommend not doing so. ComboBoxes work great for a single selection, doing multiple selections in a combobox is confusing to many users. Always shoot for great user design.
Thanks a lot Sir. Only one problem I am facing with the list box- how to remove duplicate items in the list box. I m using the following code it works but if I deselect the item ( when I deselect the Last item it shows error-- syntax sql... ')' line 1). I wanted to use checked list box or combo box for same purpose too save some space but I will adhere to ur advice.
'Code for removing duplicate items in list box---
For Row As Int16 = 0 To ListBox1.Items.Count - 2
For RowAgain As Int16 = ListBox1.Items.Count - 1 To Row + 1 Step -1
If ListBox1.Items(Row).ToStri
ListBox1.Items.RemoveAt(Ro
End If
Next
Next
End While
'Code ends for removing duplicate items in list box
'Code for removing duplicate items in list box---
For Row As Int16 = 0 To ListBox1.Items.Count - 2
For RowAgain As Int16 = ListBox1.Items.Count - 1 To Row + 1 Step -1
If ListBox1.Items(Row).ToStri
ListBox1.Items.RemoveAt(Ro
End If
Next
Next
End While
'Code ends for removing duplicate items in list box
If you are not sure how the duplicates are getting in there, I'd extract and isolate the code which fills the ListBox into its own subroutine (Sub). Then you can be very specific about when and how it gets filled. Also, at the beginning of that sub make sure you clear the ListBox control of all its items so that you are starting fresh.
David L. Hansen Sir,
I am making a project for compiling data using visual studio 2012 as front end and my Sql as backend for saving data. I often get data in Microsoft access format ( .accdb ) from various sources, which I require to add to my main data base in MySQL (after filtering). My requirement is too
import ms access file i.e .accdb into datagrid view of visual basic 2012 and after filtering save data grid view selected data in My Sql Main data base ( the columns of .accdb file are similar to my main data base and save the filtered data on data grid view in .accdb format on desktop of my computer. Kindly help me with the code and procedure.
1. How to import .accdb file to data grid view.
2. How to merge the imported data on data grid view with my sql data.
3. How to save as in .accdb format the datagrid view data.
I am making a project for compiling data using visual studio 2012 as front end and my Sql as backend for saving data. I often get data in Microsoft access format ( .accdb ) from various sources, which I require to add to my main data base in MySQL (after filtering). My requirement is too
import ms access file i.e .accdb into datagrid view of visual basic 2012 and after filtering save data grid view selected data in My Sql Main data base ( the columns of .accdb file are similar to my main data base and save the filtered data on data grid view in .accdb format on desktop of my computer. Kindly help me with the code and procedure.
1. How to import .accdb file to data grid view.
2. How to merge the imported data on data grid view with my sql data.
3. How to save as in .accdb format the datagrid view data.
Nash,
I'm happy to help you with the design of your program. I think it's time to start a new question however. I will gladly steer you towards what will help you connect to the different databases.
https://www.connectionstrings.com/
ps. Feel free to message me when you post your new question so I can jump in and help.
Hint: I've dealt with data migration projects in the past and it can be very tricky. Mostly the difficulty lies in the scrubbing of the data (bad formatting, mixed data points into one, which have to be separated later) may I suggest being well versed in Regular Expressions (Regex). It will make your life easier. Spend some time here.
I'm happy to help you with the design of your program. I think it's time to start a new question however. I will gladly steer you towards what will help you connect to the different databases.
https://www.connectionstrings.com/
ps. Feel free to message me when you post your new question so I can jump in and help.
Hint: I've dealt with data migration projects in the past and it can be very tricky. Mostly the difficulty lies in the scrubbing of the data (bad formatting, mixed data points into one, which have to be separated later) may I suggest being well versed in Regular Expressions (Regex). It will make your life easier. Spend some time here.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial