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 dt As New DataTable
DataGridView1.DataSource = dt
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 dt As New DataTable
DataGridView1.DataSource = dt
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 dt As New DataTable
DataGridView1.DataSource = dt
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 dt As New DataTable
DataGridView1.DataSource = dt
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 dt As New DataTable
DataGridView1.DataSource = dt
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 dt As New DataTable
DataGridView1.DataSource = dt
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 dt As New DataTable
DataGridView1.DataSource = dt
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 dt As New DataTable
DataGridView1.DataSource = dt
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 dt As New DataTable
DataGridView1.DataSource = dt
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 dt As New DataTable
DataGridView1.DataSource = dt
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 dt As New DataTable
DataGridView1.DataSource = dt
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 dt As New DataTable
DataGridView1.DataSource = dt
command.Dispose()
conn.Close()
End Using

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

3
Contributors
20
Replies
23
Views
4 Years
Discussion Span
Last Post by yvrej17

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.

Edited by Reverend Jim

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.

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.

Edited by Reverend Jim

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 >.<

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.

Edited by Reverend Jim

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

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.

Edited by Reverend Jim

Thank you...

Any chance you could also post a sample database?

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

Oh. OK.

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

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.

Public Class Main

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 dt As New DataTable
DataGridView1.DataSource = dt
cmd.Dispose()
conn.Close()

End Sub


Edited by Reverend Jim

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 dt As New DataTable
DataGridView1.DataSource = dt
' I don't know what the followuing method call does, so I commented it out
command.Dispose()
conn.Close()
End Using
Catch ex As Exception

End Try


Edited by TnTinMN

It Works!! Thanx a lot for the help and effort Mr. Raverend 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 ;-)

Edited by Reverend Jim

Thanx also Mr. TnTinMN :)

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


I hope you don't mind :) Thanx

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 dt As New DataTable
DataGridView1.DataSource = dt
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.

Edited by Reverend Jim

Thank you so much^^