Student Student - 1 year ago 60
SQL Question

How to implement a more efficient search feature?

In my database there are 3 column which is Name, Age, Gender.
In the program, I only want to use 1 search button. When the button is clicked, the program determine which 3 of the textbox has input and search for the right data.

How do you work with the query? For example if Name and Gender has text, the query :


Select * from table Where (Name = @name) AND (Gender = @gender)

And when only name is entered, I only query for the name. Must I check textbox by textbox whether there is user input and then write multiple query for each of them? Or is there a better way to do this?

Edit (29/5/16) : I tried doing this another way like this

myCommand = New MySqlCommand("Select * from project_record Where
(FloatNo = @floatNo OR FloatNo = 'None') AND
(DevCompanyName = @devCompanyName OR DevCompanyName = 'None') AND
(DevType = @devType OR DevType = 'None') AND
(LotPt = @lotPt OR LotPt = 'None') AND
(Mukim = @mukim OR Mukim = 'None') AND
(Daerah = @daerah OR Daerah = 'None') AND
(Negeri = @negeri OR Negeri = 'None') AND
(TempReference = @tempRef OR TempReference = 'None')", sqlConn)

But as you can guess already it will not work efficiently as well because if I only enter input for
and leave other textboxes blank, the query will not pull up all the records for
only. It will just display as no records.

Answer Source

Can you try the following:

  • build a list only including values of the textboxes that have an input
  • set a string of the join the items of that list together with the " AND " string
  • append that string to your standard SELECT statement

The code looks like this:

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

    Dim Predicate1 As String = Me.TextBox1.Text
    Dim Predicate2 As String = Me.TextBox2.Text
    Dim Predicate3 As String = Me.TextBox3.Text
    Dim PredicateList As New List(Of String)
    Dim WhereClause As String
    Dim Query As String

    If Predicate1 <> String.Empty Then
        PredicateList.Add("Name=""" & Predicate1 & """")
    End If
    If Predicate2 <> String.Empty Then
        PredicateList.Add("Age=""" & Predicate2 & """")
    End If
    If Predicate3 <> String.Empty Then
        PredicateList.Add("Gender=""" & Predicate3 & """")
    End If

    WhereClause = String.Join(" AND ", PredicateList.ToArray)
    Query = "SELECT * FROM TABLE WHERE " & WhereClause

End Sub
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download