Filtering MySql database with multiple checkboxes in VB.NET

Question

I want to filter my datas which are stored in MySql database with combining multiple checkbox and textbox queries. You can find the screenshot of the form here:

SEARCH FORM:

enter image description here

TABLES:

PROJECTS TABLE enter image description here

DETAILS TABLE enter image description here

PS: Only ID field in Projects table is unique!

If user writes in any of these three textboxes than the rest of the textbox will getting empty. Currently it works fin with textboxes. I want to combine the seach criteria with the checkboxes.

Assume that user writes in TEXTBOX1 and selects FAS, VAPA and ACC from checkboxes than the result should be listed in my listview box.

Here's the codes I've come so far :

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    If TextBox1.Text = "" And TextBox2.Text = "" And TextBox3.Text = "" Then
        MsgBox("Bir arama kriteri girin!", MsgBoxStyle.Critical, "UYARI!")
        Exit Sub
    End If


        ListView1.Items.Clear()

        If TextBox1.Text <> "" Then
                Dim cmd As New MySqlCommand
                Dim ad As New MySqlDataAdapter
                Dim projeadi As String = "select projects.ID, projects.PROJEADI, projects.TEKLIFFIRMA, details.ID, details.MARKA from projects INNER JOIN details ON projects.ID = details.ID WHERE PROJEADI='" & TextBox1.Text & "';"
                Dim tbl As New DataTable
                Dim i As Integer
                With cmd
                    .CommandText = projeadi
                    .Connection = con
                End With
                With ad
                    .SelectCommand = cmd
                    .Fill(tbl)
                End With

                For i = 0 To tbl.Rows.Count - 1
                    With ListView1
                        .Items.Add(tbl.Rows(i)("ID"))
                        With .Items(.Items.Count - 1).SubItems
                            .Add(tbl.Rows(i)("PROJEADI"))
                            .Add(tbl.Rows(i)("TEKLIFFIRMA"))
                            .Add(tbl.Rows(i)("MARKA"))
                        End With
                    End With
                Next

        ElseIf TextBox2.Text <> "" Then
            Dim cmd As New MySqlCommand
            Dim ad As New MySqlDataAdapter
            Dim tekliffirma As String = "select projects.ID, projects.PROJEADI, projects.TEKLIFFIRMA, details.ID, details.MARKA from projects INNER JOIN details ON projects.ID = details.ID WHERE TEKLIFFIRMA='" & TextBox2.Text & "';"
            Dim tbl As New DataTable
            Dim i As Integer
            With cmd
                .CommandText = tekliffirma
                .Connection = con
            End With
            With ad
                .SelectCommand = cmd
                .Fill(tbl)
            End With

            For i = 0 To tbl.Rows.Count - 1
                With ListView1
                    .Items.Add(tbl.Rows(i)("ID"))
                    With .Items(.Items.Count - 1).SubItems
                        .Add(tbl.Rows(i)("PROJEADI"))
                        .Add(tbl.Rows(i)("TEKLIFFIRMA"))
                        .Add(tbl.Rows(i)("MARKA"))
                    End With
                End With
            Next
        ElseIf TextBox3.Text <> "" Then
            Dim cmd As New MySqlCommand
            Dim ad As New MySqlDataAdapter
            Dim marka As String = "select projects.ID, projects.PROJEADI, projects.TEKLIFFIRMA, details.ID, details.MARKA from projects INNER JOIN details ON projects.ID = details.ID WHERE MARKA='" & TextBox3.Text & "';"
            Dim tbl As New DataTable
            Dim i As Integer
            With cmd
                .CommandText = marka
                .Connection = con
            End With
            With ad
                .SelectCommand = cmd
                .Fill(tbl)
            End With

            For i = 0 To tbl.Rows.Count - 1
                With ListView1
                    .Items.Add(tbl.Rows(i)("ID"))
                    With .Items(.Items.Count - 1).SubItems
                        .Add(tbl.Rows(i)("PROJEADI"))
                        .Add(tbl.Rows(i)("TEKLIFFIRMA"))
                        .Add(tbl.Rows(i)("MARKA"))
                    End With
                End With
            Next
        End If

Please advise.

Thank you.

Oguz

Ok. I can view that you're not combining the search if the user fill more than one textboxes. Is it the behavior you also want for the checkboxes? — ɐsɹǝʌ ǝɔıʌ
– ɐsɹǝʌ ǝɔıʌ, Commented Jun 9, 2015 at 7:30
Dear equisde. I am not allowing user to write in multiple textboxes in the same time. User only can write in one of the textboxes. Currently textbox search is working. I just need optionally combine the textbox criteria with checkboxes. — Ardeth
– Ardeth, Commented Jun 9, 2015 at 7:36
You need to write the code on CheckBox.CheckedChanged event. — Mahadev
– Mahadev, Commented Jun 9, 2015 at 7:41

ɐsɹǝʌ ǝɔıʌ · Accepted Answer · 2015-06-09 14:31:49Z

1

Well, since i don't know whats the name of the table field which corresponds to the checkboxes filter criteria, i'm supposing that it is Projects.Sistem

I would group all checkboxes into a GroupBox in order to use a For loop to check them all. If any of them is checked, an AND clause will be added to the end of your SQL statement.

Once done, another For will check every checked CheckBox to add the Name of the checkbox to your final SQL statement in order to obtain a SQL statement like this

SELECT projects.ID, projects.PROJEADI, projects.TEKLIFFIRMA, details.ID,
details.MARKA from projects INNER JOIN details ON projects.ID = details.ID
WHERE ..... AND Projects.Sistem IN ('FAS', 'VAPA', 'CCTV',.....)

The below code is only an approximation. You will need to check it and adapt it to your existing code

Dim SQL As String = ""

If projeadi <> "" Then
   SQL = projeadi
End If

If tekliffirma <> "" Then
   SQL = tekliffirma
End If

If marka <> "" Then
   SQL = marka
End If

For Each chkBox As CheckBox in GroupBox1.Controls
   If chkBox.Checked = True Then
      SQL = SQL & " AND Project.Sistem IN ( "
      Exit For
   End if
Next
For Each chkBox As CheckBox In GroupBox1.Controls
    If chkBox.Checked = True Then
         Sql = Sql & "'" & chkBox.Name & "',"
    End If
Next
Sql = Sql.Substring(0, Sql.Length - 1)
Sql = Sql & ")"

edited Jun 9, 2015 at 14:31

answered Jun 9, 2015 at 7:57

ɐsɹǝʌ ǝɔıʌ

4,5223 gold badges38 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Ardeth Over a year ago

Dear equisde. Thank you for your answer. I've created a groupbox and bring it with default name which is GroupBox1. In the nested for loop codes it gives syntax errors for "GroupBox1" . It also gives syntax error for END FOR statement. I am also adding the database tables screenshots in the question.

ɐsɹǝʌ ǝɔıʌ Over a year ago

Ahhh..little typo! It should be For Each chkBox As CheckBox in GroupBox1.Controls and Exit For. Sorry, my bad! I've edited the answer.

ɐsɹǝʌ ǝɔıʌ Over a year ago

Note: Remember to name every CheckBox according to the value of every field on Sistem column (E.g: Checkboxes name should be FAS, VAPA, ... instead of CheckBox1, CheckBox2,...). Otherwise the formed SQL statement will be wrong.

Ardeth Over a year ago

Thank you so much equisde. Here's the code i've come so far. I am having a syntax problem in query. Please Check the so far code in here I tried to check only FAS but it is adding an extra ( ,' ') in the query. Please check the quary result in here

ɐsɹǝʌ ǝɔıʌ Over a year ago

Yep. Another mistake in my code. Answer edited. Now it should construct the query without the extra , at the end.

|

Collectives™ on Stack Overflow

Filtering MySql database with multiple checkboxes in VB.NET

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related