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

score 1 · Accepted Answer · edited May 23 '17 at 12:16

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

edited May 23 '17 at 12:16

Community

1
1

answered Oct 07 '15 at 16:08

T.S.

18,195
11
58
78

Hi dear T.S. Actually it's not only checkboxes but also checbox and comboxes combination. There are 4 comboboxes the user can select one of them or more than one. And there are also checkboxes which user can also select one ore more checkboxes. Assume that if user select only combobox1 and checkbox3 and checkbox4, when user click on the button searching criteria will be combination of the selected ones. – Ardeth Oct 08 '15 at 11:32
@Ardeth There is no real difference if it is combo box or check boc for building a criteria. I updated the answer. As you see, how you build criteria remains the same. What data you put, it is depends on your logic. The main idea here is `...where 1=1` and then adding `and field1 = ...` ... Everything else is up to you. As far as I concerned your question is answered – T.S. Oct 08 '15 at 15:46

Creating Multiple Criteria Search

1 Answers1