Implementing database search

Question

I use mysql as database where I store my data.

I have a windows form with textboxes radiobuttons, comboboxes and more; where people give personal information about themselves like (first name, last name, sex, date birthday, phone, father name and more like this). (40 fields total)

I want to do a search button. With this button I want to fill some fields and after I push the search button a new window be opened containing all people with same personal information. I achieved to do a search button for one field (for example searching only by name).

But I have a problem when I select to search with more than one fields. For example I select to search all people who have name:Chris, Nickname:Dung, sex:Male, Birth_Country:UK and other but when I push search it gives back a window with irrelevant with the search data. Can someone help me with that?

The code I made for the search button after changes is:

public MySqlDataAdapter da;
public DataSet ds;
public string sTable = "data";

private void anazitisi_button_Click(object sender, EventArgs e)
        {
            Form2 form2 = new Form2();
            try
            {
                conn = openconnectio.GetConn();

                string radiob = null;                               
                if (radioButton1.Checked == true)
                {
                    radiob = radioButton1.Text;
                }
                else if(radioButton2.Checked == true)
                {
                    radiob = radioButton2.Text;
                }

                StringBuilder Query = new StringBuilder("SELECT * FROM data d INNER JOIN country c ON d.id_data = c.id_country WHERE 1=1 ");
                if (!String.IsNullOrEmpty(textBox1.Text))
                    Query.Append(" AND name like '" + textBox1.Text + "'");
                if (!String.IsNullOrEmpty(textBox2.Text))
                    Query.Append(" AND lastname like '" + textBox2.Text + "'");
                if (!String.IsNullOrEmpty(radiob))
                    Query.Append(" AND sex like '" + radiob + "'");
                if (!String.IsNullOrEmpty(maskedTextBox1.Text))
                    Query.Append(" AND birthdate like '" + maskedTextBox1.Text + "'");
                if (!String.IsNullOrEmpty(maskedTextBox2.Text))
                    Query.Append(" AND phone_number like '" + maskedTextBox2.Text + "'");

                MySqlDataAdapter da = new MySqlDataAdapter(Query.ToString(), conn);
                ds = new DataSet();
                da.Fill(ds, sTable);
                conn.Close();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                MessageBox.Show(ex.Message);

            }
            finally
            {
                DataGridView dg1 = new DataGridView();
                form2.Controls.Add(dg1);
                dg1.Dock = DockStyle.Fill;

                dg1.Refresh();
                dg1.DataSource = ds;
                dg1.DataMember = sTable;

                form2.Show();

                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

My results after search is fine when i comment that code:
(birthdate code) and i dont used as search of course.

        //if (!String.IsNullOrEmpty(maskedTextBox1.Text))
        //    Query.Append(" AND birthdate like '" + maskedTextBox1.Text + "'");

But when i use the (birthdate code) i get us result only a blank row.
I think because the birthdate maskedTextbox have a mask: 00/00/0000
Any suggestion?
Thanks.

Solution

I think you should consider three things
1- You may replace OR with And in your query
I mean instead of using

da = new MySqlDataAdapter(
"SELECT * FROM data INNER JOIN country ON id_data = id_country 
 WHERE name like '" + textBox1.Text + 
"'OR lastname like '" + textBox2.Text + 
"'OR sex like '" + radiob + 
"'OR birthdate like '" + maskedTextBox1.Text + 
"'OR phone
_number like '" + maskedTextBox2.Text + "' ;", conn);

You may use

 da = new MySqlDataAdapter(
 "SELECT * FROM data INNER JOIN country ON id_data = id_country 
 WHERE name like '" + textBox1.Text + 
 "'AND lastname like '" + textBox2.Text + 
 "'AND sex like '" + radiob + 
 "'AND birthdate like    '" + maskedTextBox1.Text + 
 "'AND phone_number like '" + maskedTextBox2.Text + "' ;", conn); 

2- You have to build your query string based on your text boxes and else seeing if they have any value, something like this:

StringBuilder Query = "SELECT * FROM data INNER JOIN country ON id_data = id_country 
     WHERE 1=1 ";
if(!String.IsNullOrEmpty(textBox1.Text))
  Query.Append(" AND name like '" + textBox1.Text);
....

3- Sql Injection vulnerabilities

Comments

[Just Aguy]

@Chris The two answers above both have merit in their approach. The stored procedure here is a very good alternative if you want to return null value match but that's not really the idea behind a search. It also introduces you to another area of complexity, stored procedure programming, and being a beginner these approaches will overwhelm you.

[Just Aguy]

@Chris This one fits in with your current code and further refines the query. However it may be too restrictive on it's matching. Give this a shot and see what you come out with.

[Chris]

@JustAguy Well i changed the code. I changed OR with AND but now i have to fill all the fields to get a result. When i fill only the name field i get nothing as a result. What i have to do if i want to get all the people with name = Chris for example??

[Matt Busche]

@Chris if a form field was not filled in then you need to not add that to your SQL query - option 2 of M.Heydari's answer covers this

[Chris]

@sarshong eng. Can you do an example with my variables? Because i can't understand exactly what you want to do. (Sorry i am new in programming).

[Harry Sarshogh]

Ok sir, I change this response by your variable and please also if it useful you can set score for response to this fourm