Solved

Visual basic 2012 filtering datagrid view data

Posted on 2016-08-09
21
31 Views
Last Modified: 2016-10-09
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(sCountry)
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.
0
Comment
Question by:Nash Bawz
  • 10
  • 10
21 Comments
 
LVL 15

Assisted Solution

by:David L. Hansen
David L. Hansen earned 500 total points
ID: 41750435
You appear to be using the ListBox1.SelectedItem property instead of the ListBox.SelectedItemS property.
0
 

Author Comment

by:Nash Bawz
ID: 41750539
I am unable to use ListBox1.SelectedItemS .My code is like this
 Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged


        Dim dbDataSet As New DataTable
        MySqlConn = New MySqlConnection
        MySqlConn.ConnectionString =
        "server = localhost;userid=root;password=5274;database=rrrrt.data"
        Dim SDA As New MySqlDataAdapter
        Dim bsource As New BindingSource

        Try
            MySqlConn.Open()
            Dim Query As String
                       Query = "Select * from `ewosc-elint.data`.`trial` Where  `COUNTRY`in  ('" & ListBox1.SelectedItem & "')"

            COMMAND = New MySqlCommand(Query, MySqlConn)
            SDA.SelectCommand = COMMAND
            SDA.Fill(dbDataSet)
            bsource.DataSource = dbDataSet
            Me.DataGridView2.DataSource = bsource
            SDA.Update(dbDataSet)
            MySqlConn.Close()

           

        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        Finally
            MySqlConn.Dispose()
        End Try
    End Sub
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 41751227
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.
0
 

Author Comment

by:Nash Bawz
ID: 41751480
In my query I want my statement to appear like this
Select * from `ewosc-elint.data`.`trial` Where  `COUNTRY`in  ("UK","USA","FRANCE")". When I write this in mysql it works fine.

I am using the below mentioned query
Select * from `ewosc-elint.data`.`trial` Where  `COUNTRY`in  ('" & ListBox1.SelectedItems & "')"  when using in visual studio .

It works fine for one selected item but not for multiple selected items like UK, USA, france. Can u please write the correct query.
0
 

Author Comment

by:Nash Bawz
ID: 41752108
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;password=5274;database=clu.data"
        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.DataSource = bsource
            SDA.Update(dbDataSet)
            MySqlConn.Close()

        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        Finally
            MySqlConn.Dispose()
        End Try
    End Sub
0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 500 total points
ID: 41752447
        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)

Open in new window

1
 

Author Comment

by:Nash Bawz
ID: 41752584
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_SelectedItemsChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged

        Dim dbDataSet As New DataTable
        MySqlConn = New MySqlConnection
        MySqlConn.ConnectionString =
        "server = localhost;userid=root;password=5274;database=ewosc-elint.data"
        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.Length - 1, 1)
        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` Where  `COUNTRY`in  ('" & ListBox1.SelectedItem & "') "

           
           
                COMMAND = New MySqlCommand(Query, MySqlConn)
                SDA.SelectCommand = COMMAND
                SDA.Fill(dbDataSet)
                bsource.DataSource = dbDataSet
                Me.DataGridView2.DataSource = bsource
                SDA.Update(dbDataSet)
                MySqlConn.Close()



            Catch ex As MySqlException
                MessageBox.Show(ex.Message)
            Finally
                MySqlConn.Dispose()
            End Try
    End Sub
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 41752589
Is the "SelectionMode" property for the Listbox set to "MultiSimple"?
1
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 41752594
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.
1
 

Author Comment

by:Nash Bawz
ID: 41752615
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Nash Bawz
ID: 41752660
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;password=5274;database=country.data")
        MySqlConn.Open()
        MySqlConn = New MySqlConnection
        MySqlConn.ConnectionString =
        "server = localhost;userid=root;password=5274;database=country.data"

        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(sCountry)
            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.
0
 

Author Comment

by:Nash Bawz
ID: 41752665
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.
0
 

Author Comment

by:Nash Bawz
ID: 41752681
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.Length - 1, 1)
        End If

        sql = "SELECT * FROM dbo.theDB.Table1 WHERE county_Name IN (" & cntries & ")"

        MessageBox.Show(sql)
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 41752708
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.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 41752720
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.
1
 

Author Comment

by:Nash Bawz
ID: 41752789
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).ToString = ListBox1.Items(RowAgain).ToString Then
                            ListBox1.Items.RemoveAt(RowAgain)
                        End If
                    Next
                Next
            End While
            'Code ends for removing duplicate items in list box
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 41752882
Do you know how the duplicates are getting in there?
0
 
LVL 15

Assisted Solution

by:David L. Hansen
David L. Hansen earned 500 total points
ID: 41753097
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.
0
 

Author Comment

by:Nash Bawz
ID: 41753362
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.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 41753912
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now