Editing DataGridView data changes row orders

Question

I have a DataGridView (dgvVolReport) bound to a DataTable. There is a column in the table called "group" (colGroup on the DataGridView). I am trying to create a button that can group together (i.e. assign the same group number to) all selected rows.

I have the logic of assigning the group numbers and editing the other group number correct and the new groups for each row are stored in a List<int> called groupNumbersAll.

I then transfer these number to the DataGridView like so:

for (int r = 0; r < groupNumbersAll.Count; r++)
{
    dgvVolReport.Rows[r].Cells["colGroup"].Value = groupNumbersAll[r];
}

// A breakpoint is set on this line (i.e. values below are BEFORE this line has been run
dgvVolReport.Sort(colGroup, ListSortDirection.Ascending);

And somehow that seems to change the row orders or assign the numbers to the wrong rows.

I have debugged just after that loop and the contents of groupNumbersAll is correct:

Note that those two pinned "sixes" correspond to the two selected rows (they were fives before). Now here is what the contents of the colGroup column look like:

You can see that from row 6 they don't match. Why don't they match? WHY?

But more bizarrely, if I comment out the last line (dgvVolReport.Sort(colGroup, ListSortDirection.Ascending);), which has not even run yet since that's where the breakpoint lies, then suddenly they do match!!! Does anyone know what's going on here?

btw I have also tried unbinding and then rebinding the DataSource which I thought worked for a time but now I see that it does not.

The following is the complete code (from a button click) for the group button as requested (Note that the final working code is here):

private void btnGroup_Click(object sender, EventArgs e)
        {
            //dgvVolReport.CommitEdit(DataGridViewDataErrorContexts.Commit);

            List<int> groupNumbersAll = new List<int>();
            List<int> groupNumbersNotSelected = new List<int>();
            List<int> groupNumbersSelected = new List<int>();
            List<int> rowNumbersSelected = new List<int>();
            List<int> groupNumbersOfEntirelySelectedGroups = new List<int>();

            // Populate groups (All, Selected and NotSelected)
            foreach (DataGridViewRow row in dgvVolReport.Rows)
            {
                groupNumbersAll.Add(Convert.ToInt16(row.Cells["colGroup"].Value));

                if (row.Selected)
                {
                    groupNumbersSelected.Add(Convert.ToInt16(row.Cells["colGroup"].Value));
                    rowNumbersSelected.Add(row.Index);
                }
                else
                {
                    groupNumbersNotSelected.Add(Convert.ToInt16(row.Cells["colGroup"].Value));
                }
            }

            int smallestSelectedGroupNumber = groupNumbersSelected.Min();
            int newGroupNumber = smallestSelectedGroupNumber;
            bool newGroupFlag = false;
            // If the selected rows do not contain all rows with group number equal to the smallest selected group number,
            // then we need to create a new group whose group number is the smallest selected group number plus 1.
            // This then implies that we need to add 1 to the group number of every row with a group number larger than the
            // lowest selected group number (that is the original lowest selected number before we added 1).
            if (groupNumbersNotSelected.Contains(smallestSelectedGroupNumber))
            {
                newGroupNumber++;
                newGroupFlag = true;
            }

            // Find which groups have been selected entirely, but ignore the smallest number. 
            // If a group has been entirely selected it means that that group number will no longer exist. Thus we will have to 
            // subtract 1 from each group number that is larger than a group that has been entirely selected. This process is 
            // cumulative, so if a number is higher than 2 entirely selected groups (excluding the smallest selected group) then
            // we need to subtract 2 from the group number.
            foreach (int group in groupNumbersSelected.Distinct())
            {
                if (!groupNumbersNotSelected.Contains(group) && !(group == smallestSelectedGroupNumber))
                {
                    groupNumbersOfEntirelySelectedGroups.Add(group);
                }
            }

            // Find the new group numbers
            for (int r = 0; r < groupNumbersAll.Count; r++)
            {
                int groupNum = groupNumbersAll[r];

                if (rowNumbersSelected.Contains(r))
                {
                    groupNumbersAll[r] = newGroupNumber;
                }
                else
                {
                    int subtract = groupNumbersOfEntirelySelectedGroups.Where(num => num < groupNum).Count();

                    if (newGroupFlag && groupNum >= newGroupNumber)
                    {
                        groupNum++;
                    }

                    groupNumbersAll[r] = groupNum - subtract;
                }
            }

            //// Unbind the data table because of weird ass sorting error: https://stackoverflow.com/questions/30785736/editing-datagridview-data-changes-row-orders/30799185#30799185
            //DataTable dt = (DataTable)dgvVolReport.DataSource;

            //dgvVolReport.DataSource = null;
            ////Alter the values on the underlying DataTable
            //for (int r = 0; r < groupNumbersAll.Count; r++)
            //{
            //    dt.Rows[r]["Group"] = groupNumbersAll[r];
            //}
            ////Rebind
            //dgvVolReport.DataSource = dt;

            //((DataView)dgvVolReport.DataSource).Sort = null;

            int counter = 0;
            foreach (DataGridViewRow row in dgvVolReport.Rows)
            {
                row.Cells["colGroup"].Value = groupNumbersAll[counter++];
            }

            dgvVolReport.Sort(colGroup, ListSortDirection.Ascending);
        }
    }

Answer 1

Based on Plutonix's answer, this is the final working code:

        private void btnGroup_Click(object sender, EventArgs e)
        {
            //See: https://stackoverflow.com/questions/30785736/editing-datagridview-data-changes-row-orders

            List<int> groupNumbersAll = new List<int>();
            List<int> groupNumbersNotSelected = new List<int>();
            List<int> groupNumbersSelected = new List<int>();
            List<int> rowNumbersSelected = new List<int>();
            List<int> groupNumbersOfEntirelySelectedGroups = new List<int>();

            DataTable dt = (DataTable)dgvVolReport.DataSource;

            // Populate groups (All, Selected and NotSelected)
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                int group = Convert.ToInt16(dt.Rows[r]["Group"]);
                groupNumbersAll.Add(group);

                bool selected = false;
                foreach (DataGridViewRow gridrow in dgvVolReport.SelectedRows)
                {
                    DataRowView dr = (DataRowView)gridrow.DataBoundItem;
                    selected |= dr.DataView.Table.Rows.IndexOf(dr.Row) == r;
                }

                if (selected)
                {
                    groupNumbersSelected.Add(group);
                    rowNumbersSelected.Add(r);
                }
                else
                {
                    groupNumbersNotSelected.Add(group);
                }
            }

            int smallestSelectedGroupNumber = groupNumbersSelected.Min();
            int newGroupNumber = smallestSelectedGroupNumber;
            bool newGroupFlag = false;
            // If the selected rows do not contain all rows with group number equal to the smallest selected group number,
            // then we need to create a new group whose group number is the smallest selected group number plus 1.
            // This then implies that we need to add 1 to the group number of every row with a group number larger than the
            // lowest selected group number (that is the original lowest selected number before we added 1).
            if (groupNumbersNotSelected.Contains(smallestSelectedGroupNumber))
            {
                newGroupNumber++;
                newGroupFlag = true;
            }

            // Find which groups have been selected entirely, but ignore the smallest number. 
            // If a group has been entirely selected it means that that group number will no longer exist. Thus we will have to 
            // subtract 1 from each group number that is larger than a group that has been entirely selected. This process is 
            // cumulative, so if a number is higher than 2 entirely selected groups (excluding the smallest selected group) then
            // we need to subtract 2 from the group number.
            foreach (int group in groupNumbersSelected.Distinct())
            {
                if (!groupNumbersNotSelected.Contains(group) && !(group == smallestSelectedGroupNumber))
                {
                    groupNumbersOfEntirelySelectedGroups.Add(group);
                }
            }

            // Find the new group numbers
            for (int r = 0; r < groupNumbersAll.Count; r++)
            {
                int groupNum = groupNumbersAll[r];

                if (rowNumbersSelected.Contains(r))
                {
                    groupNumbersAll[r] = newGroupNumber;
                }
                else
                {
                    int subtract = groupNumbersOfEntirelySelectedGroups.Where(num => num < groupNum).Count();

                    if (newGroupFlag && groupNum >= newGroupNumber)
                    {
                        groupNum++;
                    }

                    groupNumbersAll[r] = groupNum - subtract;
                }
            }

            for (int n = 0; n < dt.Rows.Count; n++)
            {
                dt.Rows[n]["Group"] = groupNumbersAll[n];
            }   

            dgvVolReport.Sort(colGroup, ListSortDirection.Ascending);
        }

Answer 2

I would also try editing the DataTable directly. Skip using the groupNumbersAll for now and use an incremental value for testing, instead:

DataTable dt = (DataTable)dgvVolReport.DataSource;
dgvVolReport.DataSource = null;

for (int i = 0; i < dt.Rows.Count; i++) {
    dt.Rows[i]["Group"] = i;
}

dgvVolReport.DataSource = dt;
//dgvVolReport.Sort(colGroup, ListSortDirection.Ascending);

If each row and column "Group" in the DataGridView doesn't display an unique integer, the problem is in the DataGridView's properties. You should also check the "Group"-column's properties (value type etc).

Answer 3

I think your groupNumbersAll - or how it is used - is to blame. It is hard to be certain because we can't see how it is used and more importantly, prepared for reuse. This however, is incorrect:

for (int r = 0; r < groupNumbersAll.Count; r++)
{
    dgvVolReport.Rows[r].Cells["colGroup"].Value = groupNumbersAll[r];
}

If the DGV is databound, you should not be changing values in the DGV. In fact, if you query a cell after a new assignment, you should see that it is unchanged:

Console.WriteLine(dgvGrouper.Rows[1].Cells[GrpColIndex].Value);
dgvGrouper.Rows[1].Cells[GrpColIndex].Value = 99;
Console.WriteLine(dgvGrouper.Rows[1].Cells[GrpColIndex].Value);

Mine prints the same value before as after. I have to think that the Group value doesn't show on the grid or you would see that the value is not changing. Instead you should be changing the DataSource:

for (int n = 0; n < myDT.Rows.Count; n++)
{
    myDT.Rows[n][GrpColIndex] = rList[n];
} 

My data looks like this:

The Name and Value both represent the original data order.

The problem is that sorting changes the display order, but not the underlying DataTable - the DGV is simply presenting a View of the data (DataTable -> DataView -> DataGridView control). Dumping the Group values for a sorted DGV, List and DataTable demonstrates this:

*** SORTED DGV Rows to List and DT  ***
DGV Grp: 1   List Val: 1   DT Val: 1  
DGV Grp: 1   List Val: 2   DT Val: 2  
DGV Grp: 1   List Val: 2   DT Val: 2  
DGV Grp: 1   List Val: 1   DT Val: 1  
DGV Grp: 2   List Val: 1   DT Val: 1  
DGV Grp: 2   List Val: 1   DT Val: 1  

I grouped rows 2 and 3 thru the DataTable. Afterwards the List and DT are in synch, but the DGV is not. The next time you iterate the DGV for the selected rows those row indices have no relation to the List index or DataTable row index.

Since the DGV selected rows is the starting point, you need to convert a DataGridView selected row index to a DataTable row index. If other things can happen to the DGV (rows added, rows deleted, user sort by column etc), I'd rebuild the List every time (I'd do this anyway since it is so utterly detached from the control and the data):

// rebuild the List from the Group value in the DataTable
rList = new List<int>();
for (Int32 n = 0; n < myDT.Rows.Count; n++)
{
   rList.Add((int)myDT.Rows[n][GrpColIndex]);
}

// loop thru the selected rows
foreach(DataGridViewRow dgvr in dgvGrouper.SelectedRows)
{
   // get at the underlying data item for this row
   // which is likely at a different index than the DGV row
   DataRowView dr = (DataRowView)dgvr.DataBoundItem;

   // use the DataView.Table to get the index of this DataRowView
   rList[dr.DataView.Table.Rows.IndexOf(dr.Row)] = newGrpVal;
}
newGrpVal += 1;

dr.DataView.Table.Rows.IndexOf(dr.Row) basically converts the DGV visual selected row index to the actual DataTable row index. As long as the group values don't have intrinsic meaning, you might be able to use something simple like the counter shown. If you need the current value of the selected rows from the table for a more robust group method dr[GrpColIndex] should contain the value you need.

Example:
Baker and Charlie have already been regrouped. The DGV shows them at the bottom, but we saw that they remain in slots 2 and 3 in the DataTable. Now, after selecting Delta and Echo, the above code runs. View of the indices before changes are applied:

DGV Grp: 1   List Val: 1   DT Val: 1
DGV Grp: 1   List Val: 2   DT Val: 2
DGV Grp: 1   List Val: 2   DT Val: 2
DGV Grp: 1   List Val: 3   DT Val: 1
DGV Grp: 2   List Val: 3   DT Val: 1
DGV Grp: 2   List Val: 1   DT Val: 1

The planned changes are spot on; items 4 and 5 will be set as group #3 and sort to the bottom. Just copy the list to the data source:

for (int n = 0; n < myDT.Rows.Count; n++)
{
   myDT.Rows[n][GrpColIndex] = rList[n];
}   

It would be nice if something like a Dictionary or Tuple, perhaps using the hashcode from a row could tie things together better, but I cant work out anything which works better than restarting the list.

I'll try to amend with any salient information as time allows once more information about the list and/or grouping is available.

Answer 4

I guess the value has not yet committed to the underlying data source

To modify the underlying data source directly

for (int r = 0; r < groupNumbersAll.Count; r++)
{
    var drv = (DataRowView)dgvVolReport.Rows[r].DataBoundItem;
    drv.Row["colGroup"] = groupNumbersAll[r];
}