DataGridView and user entry exceptions with int and double

sgraham posted on Wednesday, April 09, 2008

I have two issues that are similar in nature but may or may not have the same resolution.

I have EditableCollection bound to a BindingSource which is bound to a DataGridView.

1)  When a user enters text or a large number into a cell that is bound to an int property, a FormatException is thrown.  What is the best way to handle these situations?  I realize that I can override the DataError event but I'm not even sure what I would do once I'm in that event.

2)  When a user enters zero into a cell that is bound to an int or double property (and already has a value), a ArgumentException is thrown stating that Object of type 'System.DBNull' cannot be converted to type 'System.Double'.  Any idea why this is happening and how I can fix it?  This also leads to an interesting question, if the user removes all numbers/letters from a field, what will happen?  In that case, I would think it would make sense for the ErrorProvider to flash the error (similar to string properties that are required).  How would I implement that?

Thanks in advance for any feedback!

ajj3085 replied on Monday, April 14, 2008

I think you can tell it a value to use instead of DBNull.  Also, I think this is why people have taken to creating SmartInt, SmartDecimal, etc.  Check the CslaContrib for those classes.

sgraham replied on Monday, April 14, 2008

If I tell it a value (such as "NULL"), it still fires a FormatException when clearing the field.

I understand that concept of the "smart" classes in the contrib project.  However, it seems to me the use of such classes is for when your object property could have empty values.  In this case, my object can not have empty values.  The GUI is what is allowing the empty values.  Unless there is a good fix, I may have to alert the user or change the value back for them by canceling their invalid edit.  Thoughts?

sergeyb replied on Monday, April 14, 2008

Steve Graham replied on Tuesday, October 28, 2008

Hi Sean, you need to code two datagridview events:

1. The DataError event to "forget" the error that occurs when the user clears a numeric cell

   Private Sub dgv_DataError( _

        ByVal sender As Object, _

        ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) _

   Handles _

        dgv.DataError

 

        If TypeOf (e.Exception) Is System.FormatException Then

            If e.RowIndex <> -1 AndAlso dgv.Columns(e.ColumnIndex).Name = "colY" Then

                e.Cancel = True

            End If

        End If

     End Sub

 

2. The CellValidating event to place a zero into the empty cell

 

    Private Sub dgv_CellValidating( _

        ByVal sender As Object, _

        ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) _

    Handles _

        dgv.CellValidating

 

        If e.RowIndex <> -1 AndAlso dgv.Columns(e.ColumnIndex).Name = "colY" Then

            If e.FormattedValue.ToString() = String.Empty Then

                dgv.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = 0

            End If

        End If

     End Sub

 

The CellValidating event is required to allow focus to move away from the cell otherwise focus is locked onto the cell. In my scenario I validate for a number between 100 and 100,000 so as focus moves away from the cell a error provider icon appears. Hope this helps.  

Madrus replied on Wednesday, July 15, 2009

Guys, it is a year later but I have had the same experience as Sean and have finally found another solution to this nuisance. I will mention it here just in case.

My field was of type Currency, which is actually Double. I did get similar error messages as Sean when trying to leave empty cells or “0” in (while “0.0” worked just fine!). I did not go as far as “forgetting” (a question of personal taste) but I did try to set the Value to “0”, even to “(double)0.0” in my CellValidating event as Steve suggested. Funny enough, it did no good: the error messages persisted.

The solution I found was simple but I still do not understand why it works. I implemented another event, CellEndEdit, which is triggered after CellValidating. In that event I check the trimmed value and set it to “(double)0.0” if “0”. It seems that setting the Value to 0.0 in the CellValidating event does not affect the new value whereas doing it in CellEndEdit works fine.

Here are my snippets.

private void yourDGV_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
if (this.yourDGV.CurrentCell.Value.ToString().Trim() == "0"
{
this.yourDGV.CurrentCell.Value = (double)0.0;
}
} // end event yourDGV_CellEndEdit

private void yourDGV_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
{
Double number;
NumberStyles style;

style = NumberStyles.Number | NumberStyles.AllowCurrencySymbol | NumberStyles.AllowThousands;

if (String.IsNullOrEmpty(e.FormattedValue.ToString()))
{
// if empty set back the original value
this.yourDGV.CancelEdit();
e.Cancel = false;
}
else
{
if (Double.TryParse(e.FormattedValue.ToString(), style, CultureInfo.CurrentCulture, out number) == false)
{
// error: the input does not parse to double
e.Cancel = true;
}
else
{
//… do your real checks on the "number" variable
}
}

if (e.Cancel)
{
// go back to the old value
this.yourDGV.RefreshEdit();
}
} // end event yourDGV_CellValidating

In this way, if the user tries to leave the empty cell, it is possible but the old value reappears. If (s)he types “0”, then it is substituted with “0.0”. And, what is important for me, I don’t get any errors and DataError event does not fire.