How to search into multiple column(s) using single textbox

Question

I am using a SQL Server database, I have a table with multiple columns ([First Name], [Last Name]), I display it in the datagridview. I also have a single textbox (txtBoxSearch) in my UI. How can I search in two columns using only 1 string?

I call this method when navigated to the user screen to load the the data in the datagridview:

private void LoadData() {
        try {
            string sqlCommand = "SELECT * from tbl_user";
            con = new SqlConnection(connectionString);

            dataAdapter = new SqlDataAdapter(sqlCommand, connectionString);

            table = new DataTable();
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataAdapter.Fill(table);
            dataGridProducts.DataSource = table;
        } catch (Exception ex) {
            MessageBox.Show(string.Format("An error occurred: {0}", ex.Message), "Error");
        }
    }

This is my code, it works for the first search, but after searching again it will show no record found.

if (!string.IsNullOrWhiteSpace(txtBoxSearch.Text)) {
                try {
                    using (SqlConnection con = new SqlConnection(connectionString)) {
                        con.Open();
                        string sqlCommand = "SELECT * FROM tbl_user WHERE CONCAT([First Name], [Last Name]) LIKE '%" + txtBoxSearch.Text + "%'";
                        using (SqlCommand cmd = new SqlCommand(sqlCommand, con)) {

                            DataTable dt = new DataTable();
                            SqlDataAdapter ad = new SqlDataAdapter(cmd);
                            ad.Fill(dt);

                            if (dt.Rows.Count > 0) {
                                dataGridProducts.DataSource = dt;
                            } else {
                                MessageBox.Show("No record found.", "Error");
                            }
                        }
                    }
                } catch (Exception ex) {
                    MessageBox.Show(string.Format("An error occurred: {0}", ex.Message), "Error");
                } finally {
                    con.Close();
                }
            }

Answer 1

Several problems I see here. I mocked up your simple table, and ran the below code and it works just fine.

string sqlCommand = "SELECT [Fist Name], [Last Name] FROM tbl_user WHERE [First Name] LIKE '%" +
txtBoxSearch.Text + "%' OR [Last Name] LIKE '%" + txtBoxSearch.Text + "%'";
table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
using(var dataAdapter = new SqlDataAdapter(sqlCommand, connectionString))
{
    dataAdapter.Fill(table);
}
dataGridUser.DataSource = table;

You don't need SqlCommandBuilder as far as I can see. You also don't need a BindingSource nor do you use it.

Only difference I'm doing here is disposing of dataAdapter.

The above code works just fine on a button click, for example.

I suspect you're not posting all code. If at all possible please post code that can fully reproduce the issue.

Answer 2

BindingSource.
table = new DataTable();

You've taken out the code that shows us why it wont work the second time.

---

Update:

Please go through all the solutions here: Getting blank rows after setting DataGridView.DataSource

1. DataGridView.AutoGenerateColumns = false
2. The DataPropertyNames have been set
3. DataGridView.ColumnCount = 0; as new columns maybe hiding the data the second time.

Answer 3

You assign commandBuilder but not use it. Please refer to the SqlCommandBuilder documentation:

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder(v=vs.110).aspx

builder.GetUpdateCommand();

Answer 4

Found out that there is nothing wrong with my code, my textbox MultiLine property is set to true. That is why when I hit enter key the previous text is still in the textbox.

BTW, Thank you to everyone who shared their ideas :)

Answer 5

private void txtsearch_TextChanged(object sender, EventArgs e)
{
    (dataGridView1.DataSource as DataTable).DefaultView.RowFilter = string.Format("ProductCode LIKE '{0}%' or ProductName LIKE '{1}%' or Type LIKE '{2}%' ", txtsearch.Text, txtsearch.Text, txtsearch.Text);
}