DataGridViewComboBoxColumn not saving to datasource unless enter is pressed

Question

I have a DataGridView whose DataSource property is bound to a DataTable. Said DataTable is manually populated (not from a database). I will not go into details as to how exactly this happens because everything works flawlessly except for what I'm about to describe. Once the DataTable is build, it is then bound to the DataGridView with the following code :

DgvResults.DataSource = _data.ResultsData;

The last column of this DataGridView is a DataGridViewComboBoxColumn created with the following code :

var col = new DataGridViewComboBoxColumn
        {
            Name = "newMedId",
            HeaderText = @"Med ID (NEW PIS)",
            DataPropertyName = "newMedId",
            DisplayMember = "ItemId",
            ValueMember = "ItemId",
            ReadOnly = false,
            Resizable = DataGridViewTriState.False,
            SortMode = DataGridViewColumnSortMode.Programmatic
        };

        col.Items.Add("");
        col.Items.Add("DELETE");
        _data.NewFormularyData.AsEnumerable().Select(r => r.Field<string>("ItemId")).ToList()
            .ForEach(m => col.Items.Add(m));

        DgvResults.Columns.Add(col);

What this essentially does is fill each ComboBox in this column with all the IDs from another DataTable and adds a blank value and a "DELETE" value on top.

I also have a custom programmatic sort on this DataGridView. I won't post the code as I don't think it's relevant but essentially it's triggered through the ColumnHeaderMouseClick event and simply sorts the underlying DataTable by whatever column you clicked on, nullifies the DataGridView's DataSource property and rebinds it.

This all works great EXCEPT this : Say one of the dropdowns in the DataGridViewComboBoxColumn is blank and I update the value to something else manually. If I sort the DataGridView immediately after selecting said value, the value does not update the underlying DataTable and is lost. If I do the same thing BUT I hit the enter key after selecting the value, it updates correctly.

My app has a feature which exports said DataTable to an Excel spreadsheet. Clicking that button results in the spreadsheet containing the value I updated even if I did not hit enter after selecting it. It is behaving as if the DataGridViewComboBoxCell needs to lose focus before it updates the DataTable that it is bound to. Immediately clicking on a column header to sort it does not seem to provide this lost focus and the value does not update.

How can I make this value update immediately in the DataTable?

Here is an MRE reproducing the problem

using System;
using System.ComponentModel;
using System.Data;
using System.Windows.Forms;

namespace MRE
{
    public partial class Form1 : Form
    {
        private DataTable dt;

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        SetupDgv();
        CreateDT();

        Dgv.DataSource = dt;
    }

    private void CreateDT()
    {
        dt = new DataTable();

        dt.Columns.Add("test1", typeof(string));
        dt.Columns[0].ColumnName = "test1";
        dt.Columns[0].Caption = "test1";

        dt.Columns.Add("test2", typeof(string));
        dt.Columns[1].ColumnName = "test2";
        dt.Columns[1].Caption = "test2";

        var row = dt.NewRow();
        row[0] = 1;
        row[1] = 1;

        dt.Rows.Add(row);

        row = dt.NewRow();
        row[0] = 2;
        row[1] = 2;

        dt.Rows.Add(row);

        row = dt.NewRow();
        row[0] = 3;
        row[1] = 3;

        dt.Rows.Add(row);
    }

    private void SetupDgv()
    {
        Dgv.Columns.Add("test1", "test1");
        Dgv.Columns[0].DataPropertyName = "test1";
        Dgv.Columns[0].ReadOnly = true;
        Dgv.Columns[0].Resizable = DataGridViewTriState.False;
        Dgv.Columns[0].SortMode = DataGridViewColumnSortMode.Programmatic;

        var col = new DataGridViewComboBoxColumn
        {
            Name = "test2",
            HeaderText = "test2",
            DataPropertyName = "test2",
            ReadOnly = false,
            Resizable = DataGridViewTriState.False,
            SortMode = DataGridViewColumnSortMode.Programmatic
        };

        col.Items.Add("");
        col.Items.Add("DELETE");
        col.Items.Add("1");
        col.Items.Add("2");
        col.Items.Add("3");

        Dgv.Columns.Add(col);
    }

    private void Dgv_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
    {
        var newColumn = Dgv.Columns[e.ColumnIndex];
        var oldColumn = Dgv.GetSortedColumnFromDataTable(dt);

        ListSortDirection direction;

        if (oldColumn != null)
        {
            if (oldColumn == newColumn && dt.GetDataTableSortOrder() == "ASC")
                direction = ListSortDirection.Descending;
            else
            {
                direction = ListSortDirection.Ascending;
                oldColumn.HeaderCell.SortGlyphDirection = SortOrder.None;
            }
        }
        else
            direction = ListSortDirection.Ascending;

        Dgv.ClearSortGlyphInAllColumnsExcept(e.ColumnIndex);

        dt.DefaultView.Sort = direction == ListSortDirection.Ascending ?
            Dgv.Columns[e.ColumnIndex].Name + " ASC" : Dgv.Columns[e.ColumnIndex].Name + " DESC";

        newColumn.HeaderCell.SortGlyphDirection =
            direction == ListSortDirection.Ascending ? SortOrder.Ascending : SortOrder.Descending;
    }
}

public static class DGVExtensions
{
    public static DataGridViewColumn GetSortedColumnFromDataTable(this DataGridView dgv, DataTable dt)
    {
        var dtSort = dt.DefaultView.Sort;

        string colName;
        if (dtSort.IndexOf(" ") != -1)
            colName = dtSort.Substring(0, dtSort.IndexOf(" "));
        else
            colName = dtSort;

        return dgv.Columns[colName];
    }

    public static void ClearSortGlyphInAllColumnsExcept(this DataGridView dgv, int index)
    {
        foreach (DataGridViewColumn col in dgv.Columns)
        {
            if (col.Index != index)
                col.HeaderCell.SortGlyphDirection = SortOrder.None;
        }
    }
}

public static class DTExtensions
{
    public static string GetDataTableSortOrder(this DataTable dt)
    {
        if (dt.DefaultView.Sort.IndexOf(" ") == -1)
            return "ASC";
        else
        {
            var startIndex = dt.DefaultView.Sort.IndexOf(" ") + 1;

            return dt.DefaultView.Sort.Substring(startIndex, dt.DefaultView.Sort.Length - startIndex).ToUpper();
        }
    }
}
}

Answer 1

In the Dgv_ColumnHeaderMouseClick event add the following line of code as the first line of code in the event...

Dgv.CommitEdit(DataGridViewDataErrorContexts.Commit);