Retrieve all the rows in a table with a SELECT WHERE query with multiple parameterized conditions

Question

I'm trying to retrieve some data from a SQLite database formatted like this:

TABLE

Customers

COLUMNS

id | firstName | lastName | address | phone | email | notes

I have several TextBoxes and a DataGridView to display the data from the customers. What I'm trying to do is to retrieve and display in the DataGridView any row that matches the content of any TextBox. The code is as follows but I can't figure what I'm doing wrong with my SQL statement:

public static DataTable RecoverCustomer(Customer customer)
    {
        var connection = new SQLiteConnection("Data Source = prova.sqlite; Version=3;");

        var command = connection.CreateCommand();
        command.CommandText = "SELECT * FROM customers WHERE firstName = @firstName OR lastName = @lastName OR address = @address OR phone = @phone OR email = @email OR notes = @notes";
        command.Parameters.AddWithValue("@firstName", customer.FirstName);
        command.Parameters.AddWithValue("@lastName", customer.LastName);
        command.Parameters.AddWithValue("@address", customer.Address);
        command.Parameters.AddWithValue("@phone", customer.Phone);
        command.Parameters.AddWithValue("@email", customer.Email);
        command.Parameters.AddWithValue("@notes", customer.Notes);

        var dataAdapter = new SQLiteDataAdapter();
        var dataTable = new DataTable();

        connection.Open();
        dataAdapter.SelectCommand = command;
        dataAdapter.Fill(dataTable);
        connection.Close();

        return dataTable;
    }

My issue is this code returns several more rows than it should. Could it be because when adding a new customer I don't check if there's any empty field thus returning those rows when I search for a customer using this code? If yes, how can I mitigate it? This is the code I use to add a new customer to the table:

public static void CreateCustomer(Customer customer)
    {
        var connection = new SQLiteConnection("Data Source = prova.sqlite; Version=3;");
        var command = connection.CreateCommand();
        command.CommandText = "INSERT INTO customers (firstName, lastName, address, phone, email, notes) VALUES (@firstName, @lastName, @address, @phone, @email, @notes)";
        command.Parameters.AddWithValue("@firstName", customer.FirstName);
        command.Parameters.AddWithValue("@lastName", customer.LastName);
        command.Parameters.AddWithValue("@address", customer.Address);
        command.Parameters.AddWithValue("@phone", customer.Phone);
        command.Parameters.AddWithValue("@email", customer.Email);
        command.Parameters.AddWithValue("@notes", customer.Notes);

        connection.Open();
        command.ExecuteNonQuery();
        connection.Close();
    }

EDIT

Thanks to @Haldo I corrected my SQL statement that was matching empty spaces and it works. The correct statement is:

SELECT * FROM customers WHERE (IFNULL(@firstName, '') <> '' AND firstName = @firstName) OR (IFNULL(@lastName, '') <> '' AND lastName = @lastName) OR (IFNULL(@address, '') <> '' AND address = @address) OR (IFNULL(@phone, '') <> '' AND phone = @phone) OR (IFNULL(@email, '') <> '' AND email = @email) OR (IFNULL(@notes, '') <> '' AND notes = @notes)

Do you get an error when running the code? Or is it returning a faulty value? — PEPEGA, Dec 05 '18 at 10:51
@Caius Jard my bad, edited my question with a more specific request. — nicktheone, Dec 05 '18 at 10:58
What rows is it returning that it shouldn't? (This line of questioning is developing your problem solving thought processes, as well as improving how you ask questions to people who have *no idea* how your system functions - in essence it's teaching you to fish rather than giving you a fish) — Caius Jard, Dec 05 '18 at 10:59
@Caius Jard thanks for the help. [This is](https://imgur.com/a/H9PPOMn) what I'm getting back — nicktheone, Dec 05 '18 at 11:03
Looks like it is matching empty/null values. You could add something like `... where (ISNULL(@firstName, '') <> '' AND firstName = @firstName)) OR ...` for each parameter to the SQL select statement. This will prevent matching empty/null values. — haldo, Dec 05 '18 at 11:15
@Haldo thanks! Although with SQLite it seems the syxntax is different `IFNULL(x, y)` instead of `ISNULL(x, y)` this worked like a charm it seems! Post it again as an anwser and I'll flag it accordingly. — nicktheone, Dec 05 '18 at 11:25
I'm glad it helped. Please be careful with using OR and AND and parenthesis, you may need to group them together inside brackets (condition1 AND condition2) OR (condition3 AND condition4) OR etc... — haldo, Dec 05 '18 at 11:30

haldo · Accepted Answer · 2018-12-05T12:02:06.450

Based on your comments it looks like what's happening is the SQL Select statement is matching empty/null values which is why more rows are being returned than expected.

You want to match results only when the parameters have a value. Therefore, amending the select statement to the below should work:

SELECT * FROM customers 
    WHERE (IFNULL(@firstName, '') <> '' AND firstName = @firstName) 
    OR (IFNULL(@lastName, '') <> '' AND lastName = @lastName)
    OR (IFNULL(@address, '') <> '' AND address = @address)
    OR (IFNULL(@phone, '') <> '' AND phone = @phone)
    OR (IFNULL(@email, '') <> '' AND email = @email)
    OR (IFNULL(@notes, '') <> '' AND notes = @notes)

This uses IFNULL (for sqlite) but the same result can be achieved with ISNULL in SQL server. Using IFNULL will handle the case where the parameter is empty string '' or NULL.

score 1 · Answer 2 · answered Dec 05 '18 at 11:50

If I got you right, you want to return rows that are matching at least one of the passed parameters. Therefore first you should add a check that there is at least one parameter which is not null. In my opinion it should be in Customer constructor, (cause what meaning it has to have a customer with all null parameters?). Java constructor style: check parameters aren't null Then you can alter the select statement to be something like this:

SELECT * FROM customers
WHERE ( firstName = @firstName AND @firstName IS NOT NULL)
OR ( lastName = @lastName AND @lastName IS NOT NULL)
OR ( address = @address AND @address IS NOT NULL)
OR ( phone = @phone AND @phone IS NOT NULL)
OR ( email = @email AND @email IS NOT NULL)
OR ( notes = @notes AND @notes IS NOT NULL)

Retrieve all the rows in a table with a SELECT WHERE query with multiple parameterized conditions

2 Answers2