I am developing a "cv save and search application" with using c# and windows forms. My problem is when I enter both the phone number field and the email field at the same time it doesn't do a correct search.

In theory when I enter wrong email and enter correct phone number, the data gridview should not display any information. But because of if and else statements running from up to down, if phone number is true the program does not control e-mail field. How can I fix this?

Note: I changed all "else if" statements to "if" statements but it did not work.

Screenshot1

Scrensshot2

my codes:

private void search_Click(object sender, EventArgs e)
{
    ///search in databese
    if (textBox_Name.Text != "")
    {
        showData("SELECT * FROM BILGILER WHERE NAME LIKE'"   textBox_Name.Text   "%'");
    }
    else if (textBox_Surname.Text != "")
    {
        showData("SELECT * FROM BILGILER WHERE SURNAME LIKE'"   textBox_Surname.Text   "%'");
    }
    else if (textBox_Id.Text != "")
    {
        showData("SELECT * FROM BILGILER WHERE ID LIKE'"   textBox_Id.Text   "%'");
    }
    else if (textBox_Phone.Text != "")
    {
        showData("SELECT * FROM BILGILER WHERE PHONE LIKE'"   textBox_Phone.Text   "%'");
    }
    else if (textBox_Email.Text != "")
    {
        showData("SELECT * FROM BILGILER WHERE EMAIL LIKE'"   textBox_Email.Text   "%'");
    }
    else if (comboBox_Gender.Text != "")
    {
        if (comboBox_Gender.Text == "Male")
        {
            showData("SELECT * FROM BILGILER WHERE GENDER LIKE'"   0   "%'");
        }
        else if (comboBox_Gender.Text == "Female")
        {
            showData("SELECT * FROM BILGILER WHERE GENDER LIKE'"   1   "%'");
        }
    }
    else
    {
        MessageBox.Show("Please enter a information");
    }
}

CodePudding user response：

If you want to execute several filters at once, then you must include all the filters in a single SELECT command and construct a WHERE clause dynamically. I do this by adding terms to a StringBuilder.

By using some helper functions the task becomes easier. They can be either implemented as local functions (inside search_Click) or as separate methods.

static void AddLikeWithAnd(StringBuilder filter, string columnName, string value)
{
    if (value != "") {
        if (filter.Length > 0) {
            filter.Append(" AND ");
        }
        filter.Append(columnName).Append(" LIKE ").Append(AsSqlString(value   "%"));
    }

}

static string AsSqlString(string s)
{
    return "'"   s.Replace("'", "''")   "'";
}

The first one adds the term conditionally, depending on whether the text box is empty or not. It also joins the terms with AND. The second functions creates a SQL string literal by taking care of escaping single quotes with double quotes.

With these helper methods you can write:

private void search_Click(object sender, EventArgs e)
{
    var filter = new StringBuilder();
    AddLikeWithAnd(filter, "[NAME]", textBox_Name.Text);
    AddLikeWithAnd(filter, "SURNAME", textBox_Surname.Text);
    AddLikeWithAnd(filter, "ID", textBox_Id.Text);
    AddLikeWithAnd(filter, "PHONE", textBox_Phone.Text);
    AddLikeWithAnd(filter, "EMAIL", textBox_Email.Text);

    string genderValue = comboBox_Gender.Text.ToLower() switch {
        "male" => "0",
        "female" => "1",
        _ => ""
    };
    AddLikeWithAnd(filter, "GENDER", genderValue);

    if (filter.Length > 0) {
        string sql = "SELECT * FROM BILGILER WHERE "   filter.ToString();
        showData(sql);
    } else {
        MessageBox.Show("Please enter a information");
    }
}

I also escaped the column name NAME with square brackets as this is a reserved word in some SQL dialects. E.g. SQL-Server and Access use square brackets, MySQL uses backt-tickes and Oracle double quotes as escape characters.

With textBox_Name.Text = "Joe's Pub" and textBox_Email.Text = "joe" I get this SQL command:

SELECT * FROM BILGILER WHERE [NAME] LIKE 'Joe''s Pub%' AND EMAIL LIKE 'joe%'

Note that you can use LIKE only with text columns. If e.g. ID and GENDER are number columns, this will not work. And also the values must not be enclosed in single quotes then.

My answers solves the "Searching with multiple textboxes" problem; however, another fundamental problem is inserting parameter values with string concatenation instead of using parametrized queries. Many answers were given and many articles were written to this subject, so I will not integrate it in my answer.

See: C# using parametrized query

I used newer C# features like the switch expression. If you work with an older Framework version, you might have to add the <LangVersion>latest</LangVersion> tag to your project file. See: C# language versioning