How to add SQL query With 'and' condition to search function VB.Net

Question

I am going to create a search function where the texts in the textbox act as endings With and conditions.
If I use the OR condition, the function works fine but I don't understand how to use the And.
For example, I want to type in a text box - then product code, then product name, then product category, then brand name.
From where it will be possible to find Single row.
I would have benefited if an experienced person had wasted his precious time and cooperated in the solution. Thanks
Thanks in advance to the experienced collaborators.

Private Function searech() As DataTable
Dim quary As String = "select * From tbl_Sale where Date between @dt1 and @dt2"
quary &= " and product_code like '%'+ @d1 +'%'"
quary &= "and Product_name like '%' + @d1 + '%' "
quary &= " and Product_Category like '%' + @d1 + '%'"
quary &= " and brand_name like '%' + @d1 + '%'"
Dim cmd As New SqlCommand(quary, con)
cmd.Parameters.Add("@dt1", SqlDbType.Date).Value = DateTimePicker1.Value
cmd.Parameters.Add("@dt2", SqlDbType.Date).Value = DateTimePicker2.Value
cmd.Parameters.Add("@d1", SqlDbType.NVarChar).Value = TextBox1.Text
Dim da As New SqlDataAdapter(cmd)
Dim dt As New DataTable
da.Fill(dt)
Return dt
End Function

Private Sub TextBox1_KeyUp(sender As Object, e As KeyEventArgs) Handles TextBox1.KeyUp  
    DataGridView1.DataSource = searech()  
    total_Value()  
End Sub

Answer 1

Ok, this is a common setup.
You might have say 2-10 optional critera. If you leave them blank, you ignore. However, if you type in City, and then say a Name or whatever?

You don't want or - you want BOTH critera to be applied. In fact, this is the default setup I have for the vast majority of my search forms.

So, blank (no value) = ignore this criteria.
So, enter a value, then we apply that criteria - and all values must match ("and").

So, there are a number of ways to do this, but ultimately?

We should over time allow adding of more criteria. In other words, if we have 2 or 15 options, the code should not really care.

So, I suggest this pattern:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click  
  
        Dim strSQL As String = "SELECT * FROM tblHotels "  
        Dim strWhere As String = ""  
        Dim cmdSQL As New SqlCommand(strSQL)  
  
        ' hotel  
        If txtHotel.Text <> "" Then  
            strWhere = "(HotelName = *anonymous user*)"  
            cmdSQL.Parameters.Add("*anonymous user*", SqlDbType.NVarChar).Value = txtHotel.Text  
        End If  
  
  
        ' city  
        If txtCity.Text <> "" Then  
            If strWhere <> "" Then strWhere &= " AND "  
            strWhere &= "(City = @City)"  
            cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = txtCity.Text  
        End If  
  
  
        ' is hotel active  
        If chkHotelActive.Checked Then  
            If strWhere <> "" Then strWhere &= " AND "  
            strWhere &= "(Active = @Active)"  
            cmdSQL.Parameters.Add("@Active", SqlDbType.Bit).Value = 1  
        End If  
  
        ' state  
        If txtState.Text <> "" Then  
            If strWhere <> "" Then strWhere &= " AND "  
            strWhere &= "(State = @State)"  
            cmdSQL.Parameters.Add("@State", SqlDbType.NVarChar).Value = txtState.Text  
        End If  
  
  
        If strWhere <> "" Then  
            cmdSQL.CommandText = strSQL & " WHERE " & strWhere  
        End If  
  
        ' OPTIONAL order by  
        cmdSQL.CommandText &= " ORDER BY HotelName"  
  
        Dim rstData As New DataTable  
  
        Using conn As New SqlConnection(My.Settings.Test4)  
            Using cmdSQL  
                cmdSQL.Connection = conn  
                conn.Open()  
                rstData.Load(cmdSQL.ExecuteReader)  
            End Using  
        End Using  
  
        ' at this point, do whateever with the results  
  
        DataGridView1.DataSource = rstData  

So, note close - we can add 2 or 50 options. We STILL get to use strong typed parameters (no sql injection), and we simple build up as we go along, and we simple skip any control that has no value.

And if later on, you want to add 5 more controls, combo or whatever to this search page, then you can scroll down to the last value, and just start cookie cutting the additonal criteria to the end of this.

Now, I used full 100% match, but, we could just as well assume trailing wild card, and thus the criteria would look like:

        ' hotel  
        If txtHotel.Text <> "" Then  
            strWhere = "(HotelName like *anonymous user* + '%')"  
            cmdSQL.Parameters.Add("*anonymous user*", SqlDbType.NVarChar).Value = txtHotel.Text  
        End If  

Regards,

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Answer 2

Ok, as noted, we need to build a UI here, and one that allows:

Maybe a date range, maybe not.

Enter color - we get all products of that color

enter Brand, get all product of that brand.

Enter Brand, such as SamsSung, and enter color Black - we get all black SamsSung.

enter Brand, enter color, enter date range. We get all that color, for product - and within the given date range.

As we can see, we need the search criteria as "optional", and if we just enter black for color, we get all products.

As I stated, most search works above anyway. So, if we leave whatever search option "empty", then we ignore it. If user enters a value(s), then we AND the criteria. So, SamSung, or SamSung AND Black color.

As I pointed out in the other answer - the "design" pattern and approach works very well for this kind of setup. we can have 1, or 15 search options - the CODE remains the same!!!!

So, lets assume we have this search:

One BIG whopper issue is that date pickers don't allow blank values. So, you can waste a few pots of coffee, but best is to simple offer the user a check box WHEN you want to include date range.

Now, I don't have all your text boxes, but the form would in theory would, and could look like this:

To above, you can add a few more text boxes for the other searching you want. As noted, for date range, we can't have blank date pickers, so with above, we have a simple check box. (just drop the 2 date pick controls into a panel, and we can hide/show the panel when we check the check box like this:

    Private Sub SearchTest2_Load(sender As Object, e As EventArgs) Handles Me.Load  
  
        CheckBox1.Checked = False  
        Panel1.Visible = False  
  
    End Sub  

' change above  - if you want date picker DISPLAYED by default.  

' code to hide show is thus:

Private Sub CheckBox1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox1.CheckedChanged  

    ' if checked, show date range pick  

    Panel1.Visible = CheckBox1.Checked  

End Sub  

so, if I check the check box, and wanted SamSung, and black - and for this year up to today, then we have this:

The key concept, the Rosetta stone, the gold bar here is that we are now to free to use my other answer here as to how we attack and solve this problem. Each blank search text box, we SIMPLE ignore!!!

So, now lets use the code concept in the other answer, and code this out:

Based on first answer, then our code becomes "much like" this (this is air code - I don't have your code, your data, and your system - so I have to wing this a bit, but this is about the approach and attack of this kind of problem).

So, the code will look very much like this - you can extend for your purpose:

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click  
  
        ' SETUP the BASE sql query  
  
        Dim strSQL As String =  
            "SELECT * FROM tbl_Sale "  
  
        Dim strWhere As String = ""     ' our optional where clause  
        Dim strOrderBy As String = " ORDER BY [Date]"   ' add whatever you want here  
  
        Dim cmdSQL As New SqlCommand(strSQL)  
  
        ' date range?  
        If CheckBox1.Checked Then  
            strWhere = "([Date] BETWEEN @dtStart AND @dtEnd)"  
            cmdSQL.Parameters.Add("@dtStart", SqlDbType.Date).Value = DateTimePicker1.Value  
            cmdSQL.Parameters.Add("@dtEnd", SqlDbType.Date).Value = DateTimePicker2.Value  
        End If  
  
        ' product name??  
        If txtProductName.Text <> "" Then  
            If strWhere <> "" Then strWhere &= " AND "  
            strWhere &= "(Product_Name LIKE @ProductName + '%')"  
            cmdSQL.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = txtProductName.Text  
        End If  
  
  
        ' product code??  
        If txtProductCode.Text <> "" Then  
            If strWhere <> "" Then strWhere &= " AND "  
            strWhere &= "(Product_Code LIKE @PC + '%')"  
            cmdSQL.Parameters.Add("@PC", SqlDbType.NVarChar).Value = txtProductCode.Text  
        End If  
  
        ' product color  
        If txtColor.Text <> "" Then  
            If strWhere <> "" Then strWhere &= " AND "  
            strWhere &= "(Product_Color LIKE @Color + '%')"  
            cmdSQL.Parameters.Add("@Color", SqlDbType.NVarChar).Value = txtColor.Text  
        End If  
  
        ' CONTINUE FOR AS MANY as you want!!!!  
  
        If strWhere <> "" Then  
            cmdSQL.CommandText = strSQL & " WHERE " & strWhere  
        End If  
  
        ' OPTIONAL order by  
        cmdSQL.CommandText &= strOrderBy  
  
        Dim rstData As New DataTable  
  
        Using conn As New SqlConnection(My.Settings.Test4)  
            Using cmdSQL  
                cmdSQL.Connection = conn  
                conn.Open()  
                rstData.Load(cmdSQL.ExecuteReader)  
            End Using  
        End Using  
  
        ' at this point, do whateever with the results  
  
        DataGridView1.DataSource = rstData  
  
    End Sub  

So, you can add 5 more text boxes, or remove some from above. But, the above design allows AS MANY parameters as you need, and wish for, and want - and they are ALL OPTIONAL!!!

Also note, I did assume the searching is wild card ONLY for trailing, and I did not put "%" on each side. Keep in mind that you REALLY want to at least consider a cup of coffee if you going to put wild card "%" on each side of the critera, since such seaching cannot use high speed indexing, and will be VERY slow, and VERY heavy on the database server, since no indexing of the critera can be used if you put wild card "%" on each side. Trailing "%" is ok, and even for large data base, it will be fast. but, if you need and use "%" wild card on both sides (the prefix side), then full table scans will occur, it will be slow, hit the server VERY hard, and indexing can't be used for such criteria. (only you can decide the advantages of doing this, or only having wild card searching as trailing, which as noted will still search at high speed).

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada