Combobox filter issue?

Question

0.00/5 (No votes)

See more:

I have done filtering for the following access table and it was filtering successfully but my doubt is?

1. if i filter in Name combo should show all the Mani related items all the column
2. for a example if i searched Monday in Day combobox and i want to choose Mani in name combo. So all the Mani, Day and amount should filter....

Problem : If i filter Name it was filter Name column only, if i filter Day column it was filter day column only... Please help on this

ID	Name	Day	Amount
1	Mani	Monday	50
2	Subash	Tuesday	100
3	Mani	Tuesday	100
4	subash	Mondy	50
5	Mani	Wednesday	100
6	Subash	Wednesday	50
7	Mani	Thursday	100
8	Mani	Friday	10
9	Mani	Saturday	30
10	Subash	Saturday	20
11	Subash	Sunday	100

What I have tried:

Imports System.Data.OleDb

Public Class Form1
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader
    Dim sql As String
    Dim str As String
    Dim da As New OleDbDataAdapter
    Dim ds As New DataSet
    Dim row As DataRow
    Dim dt As New DataTable
    Dim bs As New BindingSource
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LD()
        fillcombo()
        fillcombo1()
        fillcombo2()
    End Sub

    Public Sub LD()
        ConnD()
        sql = "select ID,Name,Day,Amount from Empdata"
        cmd = New OleDbCommand(sql, conn)
        da = New OleDbDataAdapter(sql, conn)
        da.Fill(dt)
        bs.DataSource = dt
        dr = cmd.ExecuteReader()
        dg1.DataSource = bs
    End Sub



    Public Sub fillcombo()
        ConnD()
        Try
            sql = "select distinct Name FROM Empdata"
            cmd = New OleDbCommand(sql, conn)
            dr = cmd.ExecuteReader()
            compbo.Items.Clear()

            Do While dr.Read
                compbo.Items.Add(dr.GetValue(0).ToString)

            Loop

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        conn.Close()
        conn.Dispose()
        conn = Nothing
    End Sub

    Public Sub fillcombo1()
        ConnD()
        Try
            sql = "select distinct Day FROM Empdata"
            cmd = New OleDbCommand(sql, conn)
            dr = cmd.ExecuteReader()
            ComboBox3.Items.Clear()

            Do While dr.Read
                ComboBox3.Items.Add(dr.GetValue(0).ToString)

            Loop

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        conn.Close()
        conn.Dispose()
        conn = Nothing
    End Sub
    Public Sub fillcombo2()
        ConnD()
        Try
            sql = "select distinct Amount FROM Empdata"
            cmd = New OleDbCommand(sql, conn)
            dr = cmd.ExecuteReader()
            ComboBox2.Items.Clear()

            Do While dr.Read
                ComboBox2.Items.Add(dr.GetValue(0).ToString)

            Loop

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        conn.Close()
        conn.Dispose()
        conn = Nothing
    End Sub

    Private Sub compbo_SelectedIndexChanged(sender As Object, e As EventArgs) Handles compbo.SelectedIndexChanged
        ConnD()

        Try

            bs.Filter = "Name like '%" + compbo.Text + "%'"
            dg1.DataSource = bs

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try


        conn.Close()
        dg1.Visible = True
    End Sub

    Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox3.SelectedIndexChanged
        ConnD()

        Try

            bs.Filter = "Day like '%" + ComboBox3.Text + "%'"
            dg1.DataSource = bs

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try


        conn.Close()
        dg1.Visible = True
    End Sub

    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged
        ConnD()

        Try

            bs.Filter = "Amount like '%" + ComboBox2.Text + "%'"
            dg1.DataSource = bs

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try


        conn.Close()
        dg1.Visible = True
    End Sub
End Class

Posted 29-Feb-20 23:05pm

Member 14621280

Updated 1-Mar-20 0:12am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2020-03-01T00:13:00

Solution 1

If I understand your question correctly, the problem is that you use each combobox as a a separate filter which removes all previous filters

If you want to use all of them I suggest you handle all combo box selections in a single method and inside that method you use all the selections from all combo boxes.

Consider the following pseudo code

method FilteringCombo_SelectedIndexChanged
  filter as string = ""

  if FilterCombo1.SelectedIndex >= 0 then
     filter = "Amount like '%" + FilterCombo1.Text + "%'"
  end if

  if FilterCombo2.SelectedIndex >= 0 then
     if filter <> "" then
        filter = filter & " AND "
     end if
     filter = "Day like '%" + FilterCombo2.Text + "%'"
  end if

  if FilterCombo3.SelectedIndex >= 0 then
     if filter <> "" then
        filter = filter & " AND "
     end if
     filter = "Name like '%" + FilterCombo3.Text + "%'"
  end if
  
  bs.Filter = filter
end method

Posted 1-Mar-20 0:13am

Wendelius

Comments

Member 14621280 2-Mar-20 7:52am

Hi wendelius

it is Private function or Public sub (method FilteringCombo_SelectedIndexChanged)

Member 14621280 2-Mar-20 8:04am

I cant understand the way of wrote ...... where should i do this

Member 14621280 2-Mar-20 8:15am

Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged
Dim filter As String = ""

If ComboBox2.SelectedIndex >= 0 Then
filter = "Amount like '%" + ComboBox2.Text + "%'"
End If

If ComboBox3.SelectedIndex >= 0 Then
If filter <> "" Then
filter = filter & " AND "
End If
filter = "Day like '%" + ComboBox3.Text + "%'"
End If

If compbo.SelectedIndex >= 0 Then
If filter <> "" Then
filter = filter & " AND "
End If
filter = "Name like '%" + compbo.Text + "%'"
End If

bs.Filter = filter
End Sub

I have wrote this but every time i should go the First combobox select again the name and again i have to click 2nd 3rd combobox should select then it will filter but the thing is

1. when i filter Day and Amount it will not filtering
2. if i go and select Name filter then again i should select 2 and amount ie 3rd combobox then it will filter
3. But after filtering the correct match not appear like if i filter all the 50 amount in Name of mani it was showed all the Mani and all the day and the amounts
4. Exact match Not happened

Wendelius 2-Mar-20 14:36pm

It looks like you're still handling only one of the combo boxes in this event. Try changing the code to handle all the combo boxes in a single event

Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged, ComboBox3.SelectedIndexChanged, compbo.ComboBox2_SelectedIndexChanged

Member 14621280 3-Mar-20 4:00am

ok