DataGridView Validation & Changing Cell Value

Question

I would like to manipulate a cell in my DataGridView when it is validating so that if the user enters a value that is not valid for the database, but is easily converted to valid data, the program will change the value to an appropriate one.

I am able to validate my value properly but when I try to change it to something valid I get a DataError. Here is my code:

        private void unit_List_2_GroupsDataGridView_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
    {
        Console.WriteLine("Validating");
        DataGridViewColumn col = this.unit_List_2_GroupsDataGridView.Columns[e.ColumnIndex];
        DataGridViewCell cell = this.unit_List_2_GroupsDataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex];
        if (col == this.batchDataGridViewTextBoxColumn && this.unit_List_2_GroupsDataGridView.IsCurrentCellInEditMode)
        {
            Console.WriteLine("   Batch Column");
            DataRow[] rows = label_EntryDataSet.viewJobBatchList.Select(String.Format("Job={0} AND Display='{1}'"
                , comboBox1.SelectedValue, e.FormattedValue));
            if (rows.Length == 1)
            {
                Console.WriteLine("      Auto Completed item from list: {0}", rows[0]["Batch"]);
                //e.Cancel = true;
                cell.Value = rows[0]["Batch"];
                //this.unit_List_2_GroupsDataGridView.EndEdit();
            }
            else
            {
                Console.WriteLine("     No Autocomplete!");
                int i = 0;
                if (!int.TryParse(e.FormattedValue.ToString(), out i))
                {
                    Console.WriteLine("         Not an integer either");
                    e.Cancel = true;
                }
            }
        }
    }

The line that reads cell.Value = rows[0]["Batch"]; is not doing what I expect it to do.

Demarco

Please post the exact error message and point out which line of code is mentioned. – Elviraelvis

Answer 1

The CellValidating event occurs just prior to when the DataGridView leaves edit mode; it's an event that relates-to/involves the editing control (DataGridView.EditingControl). You should never attempt to change the cell value in the handler for this event, because unless you cancel the event (in which case the user is stuck in edit mode), the cell value is set to the value from the editing control immediately after the event finishes. This, therefore, undoes any action you perform in the handler.

What you have to do instead is change the value in the editing control (remembering not to cancel the event). For example, for a DataGridViewTextBoxCell, you would use the following instead of your problematic line:

unit_List_2_GroupsDataGridView.EditingControl.Text = Convert.ToString(rows[0]["Batch"]);

You should find that this solves your issue.

Modernism

Your response was great and saved me lots of time. Is there a way to change the dgv cell Style Format and cell Tag from within the CellValidating event? I've tried a few things and have searched for answers without luck so far. – Crenelate

@SteveRehling You should be able to set the Tag property at any point in time. I haven't tried changing the cell style inside the CellValidating event; my advice would be to place this code in the CellValueChanged event instead (if possible). – Modernism

If your column is not of String type, be sure to toString() the value before comparison. When changing the edited value be sure to make this type a string. – Juryrig

Thanks Bradley Smith, so great , since years together i was searching the best way to the situation. – Seem

Answer 2

In general, it is better to use the CellParsing event whenever you need to convert/change the value in a cell. From within that event, you can indicate that the user's value is invalid by setting the ErrorText value of the cell or row.

Ellington

This answer solved my particular problem, but it was still necessary to handle CellValidating as well, because if the user's entry is garbage and I cannot correct it while handling CellParsing, I still want to keep them in edit mode (which can't be done when handling CellParsing). It is worth noting that in such circumstances, CellValidating is raised before CellParsing. – Horary