Refresh Datagridview, detail row when inserting in textbox

Question

I have a datagridview and a textbox. When the user enter some letter in the textbox, I want the datagridview to be sort and show results as the text of the textbox. But if the user dont type anything in the textbox or erase what is in the textbox, I want the datagridview to show all the results of my Database. Actually, I've success at it 2 weeks ago, but I've made some changes and now it doesn't work anymore.

Here is my code:

    private void textBox1_TextChanged(object sender, EventArgs e)
    {
        SqlConnection maConnexion = new SqlConnection("Server= localhost; Database= Seica_Takaya;Integrated Security = SSPI; ");
        maConnexion.Open();
        string Var1 = textBox1.Text;
        SqlCommand command = maConnexion.CreateCommand();
        SqlCommand command1 = maConnexion.CreateCommand();

        if (Program.UserType == "admin")
        {
            if (textBox1.Text != String.Empty)
            {
                command.Parameters.AddWithValue("@SerialNum", Var1 + "%");
                command1.Parameters.AddWithValue("@SerialNum", Var1 + "%");
                command.CommandText = "SELECT Message, FComponent, ReadValue, ValueReference, FaultCodeByOp, RepairingTime FROM FailAndPass WHERE SerialNum LIKE @SerialNum AND FComponent IS NOT NULL";
                command1.CommandText = "SELECT Machine, BoardName, BoardNumber FROM FailAndPass WHERE SerialNum LIKE @SerialNum And FComponent IS NOT NULL";
            }
        }
        else
        {
            if (textBox1.Text != String.Empty)
            {
                command.Parameters.AddWithValue("@SerialNum", Var1 + "%");
                command1.Parameters.AddWithValue("@SerialNum", Var1 + "%");
                command.CommandText = "SELECT Message, FComponent, ReadValue, ValueReference, FaultCodeByOp, RepairingTime FROM FailAndPass WHERE  (SerialNum LIKE @SerialNum) AND ReportingOperator IS NULL AND FComponent IS NOT NULL  ";
                command1.CommandText = "SELECT DISTINCT SerialNum, Machine, BoardName, BoardNumber FROM FailAndPass WHERE  ReportingOperator IS NULL AND FComponent IS NOT NULL AND (SerialNum LIKE @SerialNum) ";
            }
        }

        if (!string.IsNullOrWhiteSpace(textBox1.Text))
        {
            SqlDataAdapter sda = new SqlDataAdapter(command);
            SqlDataAdapter sda1 = new SqlDataAdapter(command1);
            DataTable dt = new DataTable();
            DataTable dt1 = new DataTable();
            sda.Fill(dt);
            sda1.Fill(dt1);

            DataColumn dcIsDirty = new DataColumn("IsDirty", typeof(bool));
            DataColumn dcIsDirty1 = new DataColumn("IsDirty", typeof(bool));
            dcIsDirty.DefaultValue = false;
            dcIsDirty1.DefaultValue = false;
            dt.Columns.Add(dcIsDirty);
            dt1.Columns.Add(dcIsDirty1);

            dataGridView1.DataSource = dt;
            dataGridView2.DataSource = dt1;
            maConnexion.Close();

            dataGridView1.Columns[6].Visible = false;
            dataGridView2.Columns[3].Visible = false;

        }
    }

As you will see, the datagridview is fill, the user can edit columns and so write new data in Database by clicking on the button. But then if the user write a serialNum in the textbox, it just show ONE results.. and not all the results beginning by B0033 for exemple.

Answer 1

As described in the comments you do not want to requery each time the text changed. I wrote some code to get you started. You will have to transform it into code for your application.

// Put this to the event or sub where you want to query for data
 Private DataGridView dgv;
 Private DataView _tblView;
 private void Form1_Load(object sender, EventArgs e) {
        DataTable tbl = myModule.GetDataTableWithRows();
        //  Imagine we have Columns "CutomerID", "CustomerLastName", "CustomerPriority"
        _tblView = new DataView(tbl);
        _tblView.RowFilter = "";
        dgv.DataSource = _tblView;
    }

And Then your TextChangedEvent

 private void txtChanged(object sender, EventArgs e) {
        _tblView.RowFilter = ("CustomerLastName like \'" + (mytextBox.Text + "\'"));
    }

This way your application will do the filtering.

There are also wildcards for the rowfilter, I think % for any char any amount of chars. For more information take a look at DataView MSDN