Sorting columns in DataGridView causes failure while loading new data

Question

I have a custom DataTable bound to a BindingDataSource that is bound to a DataGridView.

    private dynamic matches;
    private BindingSource bsTBAstats = new BindingSource();
    private PivotTable teamstats = new PivotTable();

    public frmTBAstatistics()
    {
        InitializeComponent();

        bsTBAstats.DataSource = teamstats;
        dgvTBAstats.DataSource = bsTBAstats;

        teamstats.Key = "key";

    }

The custom DataTable is intended to provide PivotTable-like functionality, summarizing data from a source table using different methods for each column. The code works perfectly fine, and you can change the summary types by column at runtime with a context menu. However, if you sort the table, then try to change one of the summary types, the application fails when I try to re-add the rows.

In order to update the table, I have to delete the columns (because I may have to change the DataType for the column) and in some cases I may be adding or removing columns. I thought this might be related to binding, so I used the BindingDataSource to disable the suspend binding before performing the update, but that didn't resolve the issue. It fails at this.Rows.Add(newrow).

Is there some other event that's being raised that I should disable that could be causing the issue? It's somehow related to sorting, but I can't figure out how.

    // Refresh the contents of the Summary Table
    public void Update()
    {
        // If there are no source columns, do nothing.
        if (source.Columns.Count == 0)
            return;

        // Remove any data currently in the tab
        this.Clear();
        this.Columns.Clear();

        // Add a column in the pivot table for each column in the source and match the type.
        foreach (DataColumn c in source.Columns)
        {
            // If the summary type is "Count Values" add a column for each unique value in that column.
            int summarytype = summarytypes.FirstOrDefault(x => x.Key == c.ColumnName).Value;
            if (summarytype == SummaryType.CountValues)
            {
                foreach (string value in uniquevalues(c.ColumnName))
                {
                    string columnname = c.ColumnName + "_" + value;
                    SubColumn newcolumn = new SubColumn();
                    newcolumn.ParentColumn = c.ColumnName;
                    newcolumn.ColumnName = columnname;
                    newcolumn.DataType = typeof(int);
                    this.Columns.Add(newcolumn);
                }
            }
            else
            {
                string keyname = c.ColumnName;
                DataColumn newcolumn = new DataColumn();
                newcolumn.ColumnName = keyname;
                if (summarytype == 0)
                {
                    if (c.DataType == typeof(int))
                        summarytype = SummaryType.Total;
                    else if (c.DataType == typeof(bool))
                        summarytype = SummaryType.Boolean;
                    else
                        summarytype = SummaryType.First;

                }

                if (summarytype < SummaryType.Average)
                    newcolumn.DataType = typeof(int);
                else if (summarytype == SummaryType.Average)
                    newcolumn.DataType = typeof(float);
                else
                    newcolumn.DataType = c.DataType;

                this.Columns.Add(newcolumn);
                SetSummaryType(keyname, summarytype);
            }
        }

        var uniquekeys = UniqueKeys;

        // Summarize each unique key
        foreach (string uniquekey in uniquekeys)
        {
            // Create a new container for the new row data
            DataRow newrow = this.NewRow();

            // Select all records matching the current key
            DataRow[] keyrecords = source.Select(key + "='" + uniquekey + "'");

            // For each column, add the values.
            foreach (DataColumn datacolumn in source.Columns)
            {
                string keyname = datacolumn.ColumnName;
                int summarytype = summarytypes[keyname];
                if (summarytype == SummaryType.CountValues)
                {
                    foreach (string uniquevalue in uniquevalues(keyname))
                    {
                        int count = keyrecords.Count(x => (string)x[keyname] == uniquevalue);
                        newrow[keyname + "_" + uniquevalue] = count;
                    }
                }
                else
                {
                    newrow[keyname] = Summarize(keyrecords, datacolumn);
                }
            }
            this.Rows.Add(newrow);

        }

    }

Prdufresne Prdufresne · Accepted Answer · 2017-04-20T12:09:25

I was able to solve the problem by removing the sort from the BindingSource

        bsTBAstats.RemoveSort();
        bsTBAstats.SuspendBinding();
        teamstats.UpdateColumn(teamstats.Columns[columnClicked].ColumnName);
        bsTBAstats.ResumeBinding();

I'm still not sure I understand what aspect of having the DataGridView sorted impacts my ability to add records, but I was able to work around it.

Sorting columns in DataGridView causes failure while loading new data

1 Answers