Creating Multiple Criteria Search

Question

I am trying to write a code for multi-criteria search. My form is something like that:

Also my database tables are :

PROJE ADI Table:

FIRMA ADI (details) Table:

VENDOR Table:

SISTEM Table:

PROJE DURUMU Table:

It'll get the data from FIRMA ADI(details) table's PROJEDURUMU field.

All the results will be displayed in another window like that:

I've started to code but can't handle how to combine the checkboxes and also how to send the results to my result window.

Imports MySql.Data.MySqlClient
Public Class ProjeAra

    Private Sub ProjeAra_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        If con.State = ConnectionState.Open Then
           con.Close()
        End If

        Dim readerb As MySqlDataReader
        Try
            con.Open()
            Dim sorgub As String
           sorgub = "select ID,PROJEADI from projects"
            Dim cmdb As New MySqlCommand(sorgub, con)
            readerb = cmdb.ExecuteReader
            ComboBox1.Items.Clear()
            While readerb.Read
                Dim systb = readerb.GetString("PROJEADI")
                ComboBox1.Items.Add(systb)
            End While
            readerb.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try

        Dim readerc As MySqlDataReader
        Try
            con.Open()
            Dim sorguc As String
            sorguc = "select DISTINCT TEKLIFFIRMA from details"
            Dim cmdc As New MySqlCommand(sorguc, con)
            readerc = cmdc.ExecuteReader
            ComboBox2.Items.Clear()
           While readerc.Read
                Dim systc = readerc.GetString("TEKLIFFIRMA")
                ComboBox2.Items.Add(systc)
            End While
            readerc.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try


        Dim readera As MySqlDataReader
        Try
            con.Open()
            Dim sorgua As String
            sorgua = "select DISTINCT VENDOR from vendor"
            Dim cmda As New MySqlCommand(sorgua, con)
            readera = cmda.ExecuteReader
            ComboBox3.Items.Clear()
            While readera.Read
                Dim systa = readera.GetString("VENDOR")
                ComboBox3.Items.Add(systa)
            End While
            readera.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try

        Dim readerd As MySqlDataReader
        Try
        con.Open()
            Dim sorgud As String
            sorgud = "select STATUS from durum"
            Dim cmdd As New MySqlCommand(sorgud, con)
            readerd = cmdd.ExecuteReader
             ComboBox4.Items.Clear()
            While readerd.Read
                Dim systd = readerd.GetString("STATUS")
                ComboBox4.Items.Add(systd)
            End While
            readerd.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Close()
        End Try


    End Sub

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

        ' Build a list of values based on combo boxes with a selected index.
        Dim values As New List(Of String)

        ' Build an array of combo boxes we want to process.
        For Each cb As ComboBox In New ComboBox() {ComboBox1, ComboBox2, ComboBox3, ComboBox4}
            ' Check if the current combo box has an index selected.
            If cb.SelectedIndex <> -1 Then
                values.Add(cb.Text)
            End If
        Next

        ' Do something with the values.
        MessageBox.Show(String.Join(", ", values.ToArray))

        ' For example, build a where clause.
        ' If you do this, be sure to sanitize the values.
        '   MessageBox.Show("WHERE 0=1 " & String.Join(" OR Field=", values.ToArray))

    End Sub
End Class

Answer 1

Your question is not entirely clear but it seems to me that you need to build criteria based on check boxes.

First prepare your select

Dim sql As String = "Select .... From ... Where 1=1"

And then, for each check box do this

If chk.Checked Then
    sql += " AND Field1 = '" & cb.Text & "'"
End If
. . . . .  . 

for each combo box do this

' NOTE: good practice - to have an empty item at first position, 
' so user can select it as to say, "I select nothing". In this case you
' do If cbo.Selectedndex > 0 Then
If cbo.Selectedndex > -1 Then 
    sql += " AND Field1 = '" & cbo.Text & "'"
End If
. . . . .  .

This is general idea. Also, remember parameterization, i.e.

sql += " AND Field1 = @1"

See this answer for parameterization