title: C# DataGridView (DataSet source) Column SortMode resets when changing DataMember
body: <p><strong><em>TL;DR</strong>: When changing DataMember tables in a DataSet bound to a DataGridView with NotSortable column SortMode and ColumnSelect SelectionMode, an InvalidOperationException is thrown because the SortMode appears as "Automatic".</em></p>
  
  <p>I have implemented an Excel input feature into my app, and I'm using the <a href="https://github.com/ExcelDataReader/ExcelDataReader" rel="nofollow noreferrer">ExcelDataReader</a> package from NuGet to do so. This is reading my Excel files and converting them into DataSet as expected.</p>
  
  <p>However, when I tried to bind that DataSet to a DataGridView, with the intention of allowing the user to "select" what cells/columns/rows to use, I received the error the following InvalidOperationException when enabling Column selection mode:</p>
  
  <pre><code> Column's SortMode cannot be set to Automatic while the DataGridView control's SelectionMode is set to ColumnHeaderSelect.
  </code></pre>
  
  <p>This was solved using <a href="https://stackoverflow.com/a/4335035/2664719">this answer</a> on another question, which works as expected on initial bind.</p>
  
  <p><strong>However</strong>, the Excel spreadsheet may have multiple Worksheets, so I have displayed them using a ComboSelectBox -- and on SelectionChangeCommitted, I am changing the DataGridView's DataMember to the index of the selected Worksheet.</p>
  
  <p>On this change I am still getting the InvalidOperationException above, even though I am repeating the exact same code as in the initial bind -- that is working correctly.</p>
  
  <p><strong>So question is, how can I stop the SortMode from apparently being reset to Automatic or get the same code that's working for the initial Bind to work on the DataMember change.</strong></p>
  
  <p>Thanks :)</p>
  
  <p><strong>ExcelForm.cs</strong></p>
  
  <pre><code>// This works fine on initial Bind, no errors
  private void PrepareUI()
  {
    // DataGridView dataGridContents
    dataGridContents.DataSource = _contents;
    dataGridContents.DataMember = _contents.Tables[0].TableName;
    dataGridContents.SetColumnSortMode(DataGridViewColumnSortMode.NotSortable);
    dataGridContents.SelectionMode = DataGridViewSelectionMode.ColumnHeaderSelect;
  
    DataTable worksheets = GetWorksheetValues(_contents);
  
    comboWorksheet.DataSource = worksheets;
    comboWorksheet.ValueMember = "index";
    comboWorksheet.DisplayMember = "name";
  }
  
  // On SelectionChangeCommitted, same code -- but throws an InvalidOperationException
  private void comboWorksheet_SelectionChanged(object sender, EventArgs e)
  {
    dataGridContents.DataMember = _contents.Tables[int.Parse(((ComboBox)sender).SelectedValue.ToString())].TableName;
    dataGridContents.SetColumnSortMode(DataGridViewColumnSortMode.NotSortable);
    dataGridContents.SelectionMode = DataGridViewSelectionMode.ColumnHeaderSelect;
  }
  </code></pre>
  
  <p><strong>DataGridViewExtensions.cs</strong></p>
  
  <pre><code>static class DataGridViewExtensions
  {
      public static void SetColumnSortMode(this DataGridView dataGridView, DataGridViewColumnSortMode sortMode = DataGridViewColumnSortMode.NotSortable)
      {
          foreach (DataGridViewColumn c in dataGridView.Columns)
          {
              c.SortMode = sortMode;
          }
      }
  }
  </code></pre>