Filter data within datagridview bounded from DataSet

Question

I have bounded data to datagridview from DataSetand I am trying to filter these bounded data within datagridview on event of textchange I got two issues when

1. I start typing it work fine except it delete the custom datagridview headers and set the name of columns query ex. Header is 'First Name' it replaced by 'NAM' which is the column name at database...

2. Second issue when I came into else part it wont re-bounded and throw an Exception what I have missed ?

   public DataSet GetPatientList()
   {
       string connStr = ConfigurationManager.ConnectionStrings["SRJDconnstr"].ToString();
       string cmdStr = @"SELECT ROW_NUMBER()OVER(ORDER BY ID) AS SEQ,
                                                ID,
                                                DocNUM,
                                                NAM,
                                                FNAME,
                                                LFNAME,
                                                PHONE,
                                                MOBILE,
                                                SEX,
                                                BIRTHDAY,
                                                ADDRESS,
                                                ENDATETIME
                                           FROM SICK
                                    ORDER BY ENDATETIME ASC;";
   
       SqlConnection conn = new SqlConnection(connStr);
       using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
       {
               conn.Open();
               cmd.CommandText = cmdStr;
               cmd.CommandType = CommandType.Text;
   
               ds = new DataSet();
               da = new SqlDataAdapter(cmd);
   
               da.Fill(ds, "PatientList");
   
               DGV_PatientList.Columns["DGV_PatientList_RowNum"].DataPropertyName = ds.Tables["PatientList"].Columns["SEQ"].ColumnName;
               DGV_PatientList.Columns["DGV_PatientList_PatientID"].DataPropertyName = ds.Tables["PatientList"].Columns["ID"].ColumnName;
               DGV_PatientList.Columns["DGV_PatientList_DocNUM"].DataPropertyName = ds.Tables["PatientList"].Columns["DocNUM"].ColumnName;
               DGV_PatientList.Columns["DGV_PatientList_FirstName"].DataPropertyName = ds.Tables["PatientList"].Columns["NAM"].ColumnName;
               DGV_PatientList.Columns["DGV_PatientList_FatherName"].DataPropertyName = ds.Tables["PatientList"].Columns["FNAME"].ColumnName;
               DGV_PatientList.Columns["DGV_PatientList_LastName"].DataPropertyName = ds.Tables["PatientList"].Columns["LFNAME"].ColumnName;
               DGV_PatientList.Columns["DGV_PatientList_Phone"].DataPropertyName = ds.Tables["PatientList"].Columns["PHONE"].ColumnName;
               DGV_PatientList.Columns["DGV_PatientList_Mobile"].DataPropertyName = ds.Tables["PatientList"].Columns["MOBILE"].ColumnName;
               DGV_PatientList.Columns["DGV_PatientList_Gender"].DataPropertyName = ds.Tables["PatientList"].Columns["SEX"].ColumnName;
               DGV_PatientList.Columns["DGV_PatientList_Birthday"].DataPropertyName = ds.Tables["PatientList"].Columns["BIRTHDAY"].ColumnName;
               DGV_PatientList.Columns["DGV_PatientList_Address"].DataPropertyName = ds.Tables["PatientList"].Columns["ADDRESS"].ColumnName;
               DGV_PatientList.Columns["DGV_PatientList_EntryDate"].DataPropertyName = ds.Tables["PatientList"].Columns["ENDATETIME"].ColumnName;
   
               return ds;
       }
   }
   

Text Change event

    private void TB_FirstName_TextChanged(object sender, EventArgs e)
    {
        if (!string.IsNullOrWhiteSpace(TB_FirstName.Text))
        {
            // first try below
            (ds.Tables["PatientList"] as DataTable).DefaultView.RowFilter = string.Format("NAM LIKE '%{0}%'", TB_FirstName.Text);
            // second try below
            //ds.Tables["PatientList"].DefaultView.RowFilter = string.Format("NAM LIKE '%{0}%'", TB_FirstName.Text);
        }
        else
        {
            DGV_PatientList.DataSource = GetPatientList();
            DGV_PatientList.DataSource = ds.Tables["PatientList"].DefaultView;
        }
    }

Answer 1

Set AutoGenerateColumns to false.

That is what is causing the names to change, and also why you are getting the exception. The columns don't exist anymore and you are referencing them by name.

Answer 2

Working with DataGridView bound to a dataset isn't supposed to be this hard - you must have been following a really old, or poorly written tutorial

The way this is all supposed to hang together is much more neat and compact:

//in your code that handles loading the grid with data, e.g. in a Load button handler

  patientListTableAdapter.Fill(ds.PatientList); //strongly typed dataset, table is already bound to grid in design time. 
//Visual Studio binds it fully for you when you add it to the form, in the designer
//you never again mess with the bindings, just fill and empty the table: MVC


private void TB_FirstName_TextChanged(object sender, EventArgs e){

  if(string.IsNullOrWhiteSpace(TB_FirstName.Text)
    patientListBindingSource.Filter = null;
  else
    patientListBindingSource.Filter = string.Format("NAM LIKE '%{0}%'", TB_FirstName.Text);
}

Yes... just 5 lines of code should be doing everything you're trying to achieve here. Right now, you're using these technologies in an incorrect way, and achieving a difficult and poor performing result.

For guidance on how you should be using datatables, refer to MSDN: https://msdn.microsoft.com/en-us/library/fxsa23t6.aspx

Start with the "Creating a Simple Data Application" walk through, make a new project, follow th steps and create a new sample app. After you've done that, i recommend coming back to the existing app and making NO attempts to salvage what has already been done - delete the lot, remove the datagridview from the form, create a new typed dataset, link it to your DB, drop a new correctly bound datagridview on your form and th designer will set everything up. Then all you need to do is pick a suitable place to load it with data, and apply a textchanged handler (the 5 lines of code I put above)

I know it's going to be hard, throwing away all that code you poured blood weat and tears into.. but it will always be a headache, and never work right, because it's plain up the wrong way to go about working with data and bound controls