Checkbox Problem when 2 box was checked

[yvrej17]

Hello! I need a help from my code in filtering the male/female regular/contractual employees.
Scenario: I have 4 checkboxes the 'cbox' for contractual, 'rbox' for regular, 'mbox' for male and 'fbox' for female. What I wanted to happen is when I check 'mbox' it will display male employees in as well as 'fbox' for female and 'rbox' for regular employees and 'cbox' for contractual employees. I manage to make it work for single boxes filter but when I check 'cbox' together with 'fbox' that should display the female contractual employees only it displays both female regular and female contractual. Can someone check my code please.. Thank you.

Here's the code for my filter button:

        Try
            Dim choice As Boolean = True
            Select Case choice
                Case fbox.Checked = True And cbox.Checked = False
                    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
                        conn.Open()
                        Dim command As New OleDbCommand("select * from employees where gender = 'Female'", conn)
                        Dim adapter As New OleDbDataAdapter
                        Dim dt As New DataTable
                        adapter.SelectCommand = command
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        addDGV()
                        adapter.Dispose()
                        command.Dispose()
                        conn.Close()
                    End Using
                Case fbox.Checked = True And rbox.Checked = False
                    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
                        conn.Open()
                        Dim command As New OleDbCommand("select * from employees where gender = 'Female'", conn)
                        Dim adapter As New OleDbDataAdapter
                        Dim dt As New DataTable
                        adapter.SelectCommand = command
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        addDGV()
                        adapter.Dispose()
                        command.Dispose()
                        conn.Close()
                    End Using
                Case mbox.Checked = True And cbox.Checked = False
                    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
                        conn.Open()
                        Dim command As New OleDbCommand("select * from employees where gender = 'Male'", conn)
                        Dim adapter As New OleDbDataAdapter
                        Dim dt As New DataTable
                        adapter.SelectCommand = command
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        addDGV()
                        adapter.Dispose()
                        command.Dispose()
                        conn.Close()
                    End Using
                Case mbox.Checked = True And rbox.Checked = False
                    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
                        conn.Open()
                        Dim command As New OleDbCommand("select * from employees where gender = 'Male'", conn)
                        Dim adapter As New OleDbDataAdapter
                        Dim dt As New DataTable
                        adapter.SelectCommand = command
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        addDGV()
                        adapter.Dispose()
                        command.Dispose()
                        conn.Close()
                    End Using
                Case rbox.Checked = True And mbox.Checked = False
                    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
                        conn.Open()
                        Dim command As New OleDbCommand("select * from employees where status = 'Regular'", conn)
                        Dim adapter As New OleDbDataAdapter
                        Dim dt As New DataTable
                        adapter.SelectCommand = command
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        addDGV()
                        adapter.Dispose()
                        command.Dispose()
                        conn.Close()
                    End Using
                Case rbox.Checked = True And fbox.Checked = False
                    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
                        conn.Open()
                        Dim command As New OleDbCommand("select * from employees where status = 'Regular'", conn)
                        Dim adapter As New OleDbDataAdapter
                        Dim dt As New DataTable
                        adapter.SelectCommand = command
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        addDGV()
                        adapter.Dispose()
                        command.Dispose()
                        conn.Close()
                    End Using
                Case cbox.Checked = True And mbox.Checked = False
                    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
                        conn.Open()
                        Dim command As New OleDbCommand("select * from employees where status = 'Contractual'", conn)
                        Dim adapter As New OleDbDataAdapter
                        Dim dt As New DataTable
                        adapter.SelectCommand = command
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        addDGV()
                        adapter.Dispose()
                        command.Dispose()
                        conn.Close()
                    End Using
                Case cbox.Checked = True And fbox.Checked = False
                    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
                        conn.Open()
                        Dim command As New OleDbCommand("select * from employees where status = 'Contractual'", conn)
                        Dim adapter As New OleDbDataAdapter
                        Dim dt As New DataTable
                        adapter.SelectCommand = command
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        addDGV()
                        adapter.Dispose()
                        command.Dispose()
                        conn.Close()
                    End Using
                Case cbox.Checked = True And fbox.Checked = True
                    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
                        conn.Open()
                        Dim command As New OleDbCommand("select * from employees where (status = 'Contractual') and (gender = 'Female')", conn)
                        Dim adapter As New OleDbDataAdapter
                        Dim dt As New DataTable
                        adapter.SelectCommand = command
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        addDGV()
                        adapter.Dispose()
                        command.Dispose()
                        conn.Close()
                    End Using
                Case cbox.Checked = True And mbox.Checked = True
                    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
                        conn.Open()
                        Dim command As New OleDbCommand("select * from employees where status = 'Contractual' and gender = 'Male'", conn)
                        Dim adapter As New OleDbDataAdapter
                        Dim dt As New DataTable
                        adapter.SelectCommand = command
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        addDGV()
                        adapter.Dispose()
                        command.Dispose()
                        conn.Close()
                    End Using
                Case rbox.Checked = True And fbox.Checked = True
                    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
                        conn.Open()
                        Dim command As New OleDbCommand("select * from employees where status = 'Regular' and gender = 'Female'", conn)
                        Dim adapter As New OleDbDataAdapter
                        Dim dt As New DataTable
                        adapter.SelectCommand = command
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        addDGV()
                        adapter.Dispose()
                        command.Dispose()
                        conn.Close()
                    End Using
                Case rbox.Checked = True And mbox.Checked = True
                    Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
                        conn.Open()
                        Dim command As New OleDbCommand("select * from employees where status = 'Regular' and gender = 'Male'", conn)
                        Dim adapter As New OleDbDataAdapter
                        Dim dt As New DataTable
                        adapter.SelectCommand = command
                        adapter.Fill(dt)
                        DataGridView1.DataSource = dt
                        addDGV()
                        adapter.Dispose()
                        command.Dispose()
                        conn.Close()
                    End Using

            End Select
        Catch ex As Exception
            MessageBox.Show(ex.Message, "ERROR5", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

[Reverend Jim]

Compose the query first then do the DB access later as in

Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
Dim cmd As New OleDbCommand("", con)

Dim query As String = "SELECT * FROM Employees   "
Dim where As String = ""

If cbox.Checked Then where &= " status = 'Regular'     OR"
If rbox.Checked Then where &= " status = 'Contractual' OR"
If mbox.Checked Then where &= " gender = 'Male'        OR"
If fbox.Checked Then where &= " gender = 'Female'        "

If where <> "" Then query &= "WHERE" & where

cmd.CommandText = query.Substring(0, Len(query) - 2)

etc.

[yvrej17]

OMG.. Honestly Sir I don't know how to apply that, I'm just beginning to learn vb.net but I'll try to understand and apply.. Thanks for the effort.

[Reverend Jim]

I did a minor edit after you responded but before I posted and saw your reply. The process is really not that compliciated. All I am doing is building up the query a bit at a time. In people speak it's like:

• I want all employees
• oh yeah, I just want regular employees
• oh yeah, I also want contractual employees

Every time you find a checkbox you add a bit to the end of the query. Then when you are done you lop off the last two chars to prevent a dangling "OR". You can get sneaky when you want to avoid a lot of repetitive typing or complex If statements.

[yvrej17]

I just wanna ask Sir... Is there other way by just editing my code or is your code the only way to make it work? Call me stupid or noob but it's true.. I already tried it but it didn't work, I realy don't know on how to use it >.<

[Reverend Jim]

I don't know from DataTables and DataAdapters. As Scotty says, "the fancier they make the plumbing the easier it is to plug up the drains." I use mostly ADO when I want to do any database work, but what I suggested is an easy way to build the query. What in particular are you having trouble with in my code? I'll try to explain further.

[yvrej17]

May I upload my system here for you to check? If it's ok :)

[Reverend Jim]

I could have a look, but as I said, I don't know a lot about the new layers such as DataTables, DataAdapters, etc. I'll offer any advice I can.

[yvrej17]

Here's the download link Sir: Click Here
Thank you...

[Reverend Jim]

Any chance you could also post a sample database?

[yvrej17]

Inside that rar archieve also contain my database 'employeedb.mdb' located at 'bin/debug/' :)

[Reverend Jim]

Oh. OK.

[yvrej17]

I was thinking if this concept will work.. (haven't tried it yet)

case cbox.checked
    if fbox.checked then
        ...statement
    else
        statement
 case cbox.checked
    if mbox.checked then
        ...statement
    else
        statement

etc.. hmmmmmm

[Reverend Jim]

You hard coded the connection string a dozen or more times. The actual connection string should appear only once. If you ever have to change it you don't want to have to change it in multiple places. If you do you are likely to miss one.

You don't name your controls. They should never be named Button1, Button2, etc except for trivial examples. When working with a large block of code, using a name like btnRefresh makes the code much easier to follow.

You haven't added any comments to your code. This, in and of itself, is cause for justifiable flogging. Here I am speaking as a 30+ year veteran as a maintenance programmer where I had to work with untold thousands of lines of other people's code. If you are doing this as an assignment, some markers can be brutal in taking of marks for uncommented code. I know. I was one of them. And I learned that less the hard way when I lost marks on early assignments.

I made the following additions at the top of Main

Public Class Main

    Const DATABASE = "D:\Downloads\EmployeeRecords\EmployeeRecords\bin\Debug\Employeedb.mdb"
    Public ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DATABASE & ";"
    Public conn As New OleDbConnection(ConStr)

You can now use the same connection object (conn) everywhere in your project. However, in the login form you must now use Main.conn instead of just conn. The following is the replaacement code for Button5.Click. You can take out the MsgBox once you see it running. You can also add back in the Try/Catch statements. I don't know if your filter is an AND or OR so I just coded AND. Feel free to change AND to OR if it is incorrect.

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

    Dim query As String = "SELECT * FROM Employees    "
    Dim where As String = ""

    If cbox.Checked Then where &= " status = 'Regular'     AND"
    If rbox.Checked Then where &= " status = 'Contractual' AND"
    If mbox.Checked Then where &= " gender = 'Male'        AND"
    If fbox.Checked Then where &= " gender = 'Female'         "

    If where <> "" Then query &= "WHERE" & where
    query = query.Substring(0, Len(query) - 3)

    MsgBox(query)   'remove this after you are done debugging

    Dim cmd As New OleDbCommand(query, conn)
    Dim adapter As New OleDbDataAdapter
    Dim dt As New DataTable
    adapter.SelectCommand = cmd
    adapter.Fill(dt)
    DataGridView1.DataSource = dt
    addDGV()
    adapter.Dispose()
    cmd.Dispose()
    conn.Close()

End Sub

[TnTinMN]

Edit:
@Jim, sorry about this. Didn't mean to step on your toes. I got interrupted while posting and did not think to do a refresh before posting.
**********

No offence intended, but the logic of yours is very confusing. I'm not sure that this is much better. It does pretty much what Jim's did and builds the "where" clause on the fly. I downloaded your db and gave it a quick test and it seemed to work, but the logic is my interpretation of what you are trying to do.

   Dim genderclause As String = ""
   Dim statusclause As String = ""

   'set gender part
   If mbox.Checked And fbox.Checked Then
      genderclause = "((gender = 'Male') Or (gender = 'Female'))"
   Else
      If mbox.Checked Then genderclause = "(gender = 'Male')"
      If fbox.Checked Then genderclause = "(gender = 'Female')"
   End If

   'set status part
   If rbox.Checked And cbox.Checked Then
      statusclause = "((status = 'Regular') Or (status = 'Contractual'))"
   Else
      If rbox.Checked Then statusclause = "(status = 'Regular')"
      If cbox.Checked Then statusclause = "(status = 'Contractual')"
   End If

   Dim whereclause As String

   If genderclause.Length = 0 And statusclause.Length = 0 Then
      whereclause = "" ' no filter
   Else
      whereclause = " where " & If(genderclause.Length = 0, "", genderclause & If(statusclause.Length = 0, "", " AND ")) _
                              & If(statusclause.Length = 0, "", statusclause)


   End If

   Try

      Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.Environment.CurrentDirectory & "\employeedb.mdb;")
         conn.Open()
         Dim command As New OleDbCommand("select * from employees" & whereclause, conn)
         Dim adapter As New OleDbDataAdapter
         Dim dt As New DataTable
         adapter.SelectCommand = command
         adapter.Fill(dt)
         DataGridView1.DataSource = dt
         ' I don't know what the followuing method call does, so I commented it out
         'addDGV()
         adapter.Dispose()
         command.Dispose()
         conn.Close()
      End Using
   Catch ex As Exception

   End Try

[yvrej17]

It Works!! Thanx a lot for the help and effort Mr. Raverend Jim :)

[Reverend Jim]

@TnTinMN - RE:If mbox.Checked And fbox.Checked Then

If you look at the project you will see he has logic to ensure that male and female cannot both be checked at the same time.

My toes are just fine ;-)

[yvrej17]

Thanx also Mr. TnTinMN :)

[yvrej17]

Uhmm.. Sir Reverend can you please explain this part for I shall be question about this in our thesis defence..

 If where <> "" Then query &= "WHERE" & where
query = query.Substring(0, Len(query) - 3)

I hope you don't mind :) Thanx

[Reverend Jim]

No problem. If you haven't selected any options in the filter you want to select all records in which case you don't want to add a WHERE clause. For every checkbox that was selected you add another piece to the WHERE clause string. If no checkboxes were selected then the WHERE string will be empty. So in English the first line is

If any filter options (checkboxes) were selected then
    add a WHERE clause to the query

If you have more then one checkbox selected you have to use AND or OR to include the option in the WHERE clause. For example, if you only had one option the clause would look like

WHERE Gender = 'Male'

but if two are selected then you need

WHERE Gender = 'Male' AND Status = 'Regular'

but it gets a little ugly determining whether or not you have one option or more than one selected so it is easier to just add the conjunction to the end of each option and just remove the last one at the end so we get

WHERE Gender = 'Male' AND Status = 'Regular' AND

and then trim it to

WHERE Gender = 'Male' AND Status = 'Regular'

Note that the base query is

"SELECT * FROM Employees    "

which has at least three extra blanks on the end. That's so that when we trim the last three characters we just lop off blanks instead of ending up with

"SELECT * FROM Employ"

I probably should have just coded

If where <> "" Then query &= "WHERE" & where.SubString(0, Len(where) - 3)

and just dropped the following line completely. The result would have been

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

    Dim query As String = "SELECT * FROM Employees"
    Dim where As String = ""

    If cbox.Checked Then where &= " status = 'Regular'     AND"
    If rbox.Checked Then where &= " status = 'Contractual' AND"
    If mbox.Checked Then where &= " gender = 'Male'        AND"
    If fbox.Checked Then where &= " gender = 'Female'         "

    If where <> "" Then query &= " WHERE" & where.SubString(0, Len(where) - 3)

    MsgBox(query)   'remove this after you are done debugging

    Dim cmd As New OleDbCommand(query, conn)
    Dim adapter As New OleDbDataAdapter
    Dim dt As New DataTable
    adapter.SelectCommand = cmd
    adapter.Fill(dt)
    DataGridView1.DataSource = dt
    addDGV()
    adapter.Dispose()
    cmd.Dispose()
    conn.Close()

End Sub

And just a reminder to change AND to OR if required. It would make more sense considering that things like "Contractual" and "Regular" appear to be mutually exclusive so the phrase

WHERE status = 'regular' AND status = 'contractual'

Actually now that I think about it some more perhaps I have led you down a rabbit hole because you might need a combination of ANDs and ORs. Would you want a query like (in English)

Select only male contract employees

which would translate as

SELECT * FROM Employees WHERE status = 'Contract' AND gender = 'Male'

But if the user selects 'Contract', 'Regular' and 'Male' do you want

SELECT * FROM Employees WHERE (status = 'Contract' OR status = 'Regular') AND gender = 'Male'

In which case what you really want is

SELECT * FROM Employees WHERE gender = 'Male'

That makes building the WHERE clause a little more complicated but not much more so because you only have a few options. If you need something a little more complicated then I'll have to know what the possible combinations are. There won't be too many of them.