SQL/VB.NET Search-Function looking for at least one correct input

Question

I'm writing a program in Visual Basic about Databases. Now I have a Sub/Function who searches the database for correct inputs. I have five text boxes where the user can put in something for each data field.

If txtBox1.Text <> "" Or txtBox2.Text <> "" Or txtBox3.Text <> "" Or txtBox4.Text <> "" Or txtBox5.Text <> "" Then
        Try
            connection.Open()
            command.CommandText = "SELECT * from lager WHERE (lager_waren_id LIKE '" & txtBox1.Text & "' OR lager_warenanzahl LIKE '" & txtBox2.Text & "' OR lager_warenname LIKE '%" & txtBox3.Text & "%' OR lager_warengewicht LIKE '" & txtBox4.Text & "%' OR lager_waren_verkaufspreis LIKE '" & txtBox5.Text & "%');"
            reader = command.ExecuteReader()

            FormKunde.Enabled = True
            FormKunde.lstViewKundeStore.Items.Clear()
            Do While reader.Read()
                Dim lstViewItem As New ListViewItem(reader("lager_waren_id").ToString())
                lstViewItem.SubItems.Add(reader("lager_warenanzahl").ToString())
                lstViewItem.SubItems.Add(reader("lager_warenname").ToString())
                lstViewItem.SubItems.Add(reader("lager_warengewicht").ToString())
                lstViewItem.SubItems.Add(reader("lager_waren_verkaufspreis").ToString())
                FormKunde.lstViewKundeStore.Items.Add(lstViewItem)
            Loop
            reader.Close()
            FormKunde.Enabled = False
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        connection.Close()
    Else
        MessageBox.Show("Please fill in something in the text fields")
        Exit Sub
    End If

I'm aksing the database if at least one text field has some input that matches to the data field it belongs to. But when I put something in, doesn't matter how much, nothing happens in my list view. It just loads all data back in the list view. When I try to do "AND" instead of "OR", it works only if i fill all text fields with the correct datas for one data set. But I want, that it finds all data sets. An example: I have two data sets where the names are "App" and "Apple". When i just fill in "Ap" in the field for names (nothing in the others) it shows me both. I think it should work with "OR", but it just does nothing.

I'm really confused how to solve this, I hope anyone has a guess. Thank you!

score 2 · Accepted Answer · edited May 23 '17 at 11:50

Your problem is that your query always uses all the conditions also when there is no input in the relevant textboxes. In this way your LIKEs become LIKE '%%' and, of course, this matches every record.

You need to add the conditions only if the textboxes are not empty or null.
So you need to build your query in parts after checking if the textbox contains any value to search for.

    connection.Open()
    Dim sql = "SELECT * from lager WHERE "
    if Not string.IsNullOrWhiteSpace(textBox1.Text) Then
        sql = sql & "lager_waren_id LIKE @p1 OR "
        command.Parameters.AddWithValue("@p1", textBox1.Text)
    End If
    if Not string.IsNullOrWhiteSpace(textBox2.Text) Then
        sql = sql & "lager_warenanzahl LIKE @p2 OR "
        command.Parameters.AddWithValue("@p2", textBox2.Text)
    End If
    if Not string.IsNullOrWhiteSpace(textBox3.Text) Then
        sql = sql & "lager_warenname LIKE @p3 OR "
        command.Parameters.AddWithValue("@p3", "%" & textBox3.Text & "%")
    End If
    if Not string.IsNullOrWhiteSpace(textBox4.Text) Then
        sql = sql & "lager_warengewicht LIKE @p4 OR "
        command.Parameters.AddWithValue("@p4", textBox4.Text & "%")
    End If
    if Not string.IsNullOrWhiteSpace(textBox5.Text) Then
        sql = sql & "lager_waren_verkaufspreis LIKE @p5 OR "
        command.Parameters.AddWithValue("@p5", textBox5.Text & "%")
    End If
    ' Remove the last OR if any ....'
    if sql.EndsWith(" OR ") then
       sql = sql.Substring(0, sql.Length - 4)
    End If
    ' Remove the WHERE if no textbox has been filled....'
    if sql.EndsWith(" WHERE ") then
       sql = sql.Substring(0, sql.Length - 7)
    End If
    command.CommandText = sql
    reader = command.ExecuteReader()

Notice also that you should ALWAYS use a parameterized query to avoid Sql Injection particularly when you get your inputs directly from your user. (Not to mention the problems with typed texts that contain a single quote)

Thank you! That looks really good and I will try it. I'll tell you if it works, but it seems really good so far. — Johannes Mols, Nov 03 '14 at 19:46
Okay it worked! Thank you, this really helped me out and helps me in the future. — Johannes Mols, Nov 03 '14 at 20:12

zack.lore · Answer 2 · 2014-11-03T18:35:14.523

I hope I understand your problem correctly. I am sure there are better ways to do this and my VB is rusty but something like this may work

Dim query As String = "SELECT * FROM lager"

Function addField (ByVal query As String, ByVal value as String, ByVal field as String) As String
addField = query    
If value <> "" Then
    If query.IndexOf("where", 0, StringComparison.CurrentCultureIgnoreCase) > -1 Then
        addField = query & " AND " & field & " LIKE '%" & value & "%'"
    Else
        addField = query & " WHERE " & field & " LIKE '%" & value & "%'"
    End If
End If
End Function

query = addField(query, txtBox1.Text, "lager_waren_id")
query = addField(query, txtBox2.Text, "lager_warenanzahl")
'...continue adding fields...'

command.CommandText = query

This should make it so your query string only includes the populated fields

SQL/VB.NET Search-Function looking for at least one correct input

2 Answers2