ERROR : Input String was not in a correct format when calculating multiple columns in DATAGRIDVIEW

Question

I want to calculate the value of 3 cells in DATAGRIDVIEW

the calculation :

SDI = ((result - mean ) / SD)

I tried the following code :

private void dgvResult_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            string result = dgvResult.CurrentRow.Cells[5].Value.ToString();
            string mean = dgvResult.CurrentRow.Cells[6].Value.ToString();
            string sd = dgvResult.CurrentRow.Cells[7].Value.ToString();
           
            dgvResult.CurrentRow.Cells[16].Value = (Convert.ToDecimal(result) - Convert.ToDecimal(mean)) ;
          //  dgvResult.CurrentRow.Cells[8].Value = (Convert.ToDecimal(dgvResult.CurrentRow.Cells[16].Value.ToString()) / Convert.ToDecimal(sd));
                        
        }

This line of code working correct and calculate first part :

 dgvResult.CurrentRow.Cells[16].Value = (Convert.ToDecimal(result) - Convert.ToDecimal(mean)) ;

result - mean 

But the error appeared when added the last line of code

when type the number in the mean cell error appeared
but when i remove the last line of code and type numbers in the mean field its calculating the value

dgvResult.CurrentRow.Cells[8].Value = (Convert.ToDecimal(dgvResult.CurrentRow.Cells[16].Value.ToString()) / Convert.ToDecimal(sd));

How to solve this error please ?

I populated the data in grid by using search button when click the button this is the code :

  private void btnSearch_Click(object sender, EventArgs e)
        {


            string sql = @" SELECT samples.name as 'Sample No',
       [program_id] as 'Program',
       RESULTS.TESTID,
       testname as 'Test',
       TestsUnits.UnitName as 'Unit',
       results.RESULT as 'Result',
       RESULTS.mean as 'Mean',
       RESULTS.sd as 'Standard Deviation',
       RESULTS.sdi as 'Standard Deviation Index',
       results.low_limit as 'Low Limit' , 
       RESULTS.high_limit as 'High Limit' , 
       RESULTS.perf_id as 'Performance',
       results.APPROVED_DATE as 'Result Date',
       Machines.Machine_name,
       results.[Machine_id],
       sys_users.user_full_name,
       '' as 'RM'

  FROM [dbo].[RESULTS]
  inner join labtests on RESULTS.testid = labtests.testid 
  inner join machines on RESULTS.Machine_id = Machines.Machine_id 
  inner join TestsUnits on labtests.UnitId = TestsUnits.UnitId
  inner join sys_users on results.custid = sys_users.user_id
  inner join samples on results.sample_id = samples.id 
  
  where  1=1 ";

            string condition = "";


            DateTime fromDate;
            DateTime toDate;


            if (!DateTime.TryParse(dtFromDate.Value.ToString(), out fromDate))
            {
                System.Windows.Forms.MessageBox.Show("Invalid From Date");
            }
            else if (!DateTime.TryParse(dtToDate.Value.ToString(), out toDate))
            {
                System.Windows.Forms.MessageBox.Show("Invalid to Date");
            }
            else
            {
                condition += " and cast(results.APPROVED_DATE as date) between '" + fromDate + "' and '" + toDate + "'";
            }


            if (textCustId.Text != "")
            {
                condition += " and RESULTS.custid = '" + textCustId.Text + "'";
            }

            if (comboProgram.Text != "")
            {
                condition += " and results.program_id = '" + comboProgram.SelectedValue + "'";
            }


            DataTable dt = data.fireDatatable(string.Format(sql + condition));
            dgvResult.DataSource = dt;
            dgvResult.Refresh();
            //  dgvResult.DataSource = test.GET_RESULTS(Convert.ToInt32(comboProgram.SelectedValue),Convert.ToDateTime(dtFromDate.Text.ToString()),Convert.ToInt32(textCustId.Text));

            dgvResult.Columns["Performance"].DefaultCellStyle.NullValue = "1";
          //  dgvResult.Columns["Standard Deviation"].DefaultCellStyle.NullValue = "1";
           // dgvResult.Columns["Mean"].DefaultCellStyle.NullValue = "1";
           // dgvResult.Columns["RM"].DefaultCellStyle.NullValue = "1";

        }

Answer 1

It is unclear how the grid is populated with data. However, when you want to “calculate” a value in a grid cell based on other cells in the same row, then, you should consider using a DataTable and a Expression column OR a Class that computes the SDI value as a property of the Class.

You could do as you have and use some events from the grid to “manually” compute the value, however there is an easier way as described above. In the example below I used a DataTable with an expression column. This will also set the grids column types to decimals.

Using your example, there would be a decimal column for the “Result”, “Mean” and “StandardDeviation” values. Then we could add the “expression” column to the DataTable and it may look something like….

dt.Columns.Add("SDI", typeof(decimal), "(Result - Mean) / StandardDeviation");

The “expression” is the last parameter… "(Result - Mean) / StandardDeviation"

Where Result is the value in the Results cell on that row and the same applies to the other column names “Mean” and “StandardDeviation”.

Since those columns are decimal columns we can limit the conversion/parsing of the values. About the only issue here is that if the “StandardDeviation” cell value is zero (0) then the grid will throw an obvious DataError since this would lead to a divide by zero expression which will obviously fail. So, we are going to have to look for this.

To help, I suggest we wire up the grids CellValidating event. This event fires when the user tries to leave a cell after making changes. If we look at the value in the “StandardDeviation” cell and check to see if the value is zero, then we could pop up a message box and note that a zero is not allowed as a value in that cell. Then revert the cell back to its original value and cancel the edit. This should help avoiding the possible divide by zero situations.

Next, we could also help the user by setting some valid default values if the user starts typing into the grid’s new row (if available) using the grids DefaultValuesNeeded event.

And further we could also wire up the cells KeyPress event to only allow for numbers and a single decimal point to be typed into the cell. This will prevent the user from typing alpha characters into the cell. NOTE: even though we prevent the user from “typing” invalid characters… the user can still “paste” invalid values. Fortunately, the CellValidating event will catch this for us.

Setting up the KeyPress event is accomplished using two events. The first is the grids EditingControlShowing event. When this event fires, the cell is going into “edit” mode. In this case, all we want to do is cast the cell to a regular TextBox cell and subscribe to the text boxes KeyPress event. The KeyPress event will then fire when the user types characters into the cell, which we will obviously check and ignore any invalid characters.

Below is an example of what is described above. Create a new winforms solution and drop a DataGridView onto the form to see this in action.

DataTable GridDT;
TextBox CurrentEditedCell;

public Form1() {
  InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e) {
  GridDT = GetGridDT();
  dataGridView1.DataSource = GridDT;
}


private DataTable GetGridDT() {
  DataTable dt = new DataTable();
  dt.Columns.Add("Result", typeof(decimal));
  dt.Columns.Add("Mean", typeof(decimal));
  dt.Columns.Add("StandardDeviation", typeof(decimal));
  dt.Columns.Add("SDI", typeof(decimal), "(Result - Mean) / StandardDeviation");
  dt.Rows.Add(1, 1, 1);
  dt.Rows.Add(2, 1, 1);
  dt.Rows.Add(3, 1, 1);
  dt.Rows.Add(4, 1, 1);
  dt.Rows.Add(5, 1, 1);
  dt.Rows.Add(6, 1, 1);
  return dt;
}


private void dataGridView1_DefaultValuesNeeded(object sender, DataGridViewRowEventArgs e) {
  e.Row.Cells["Result"].Value = "1";
  e.Row.Cells["Mean"].Value = "1";
  e.Row.Cells["StandardDeviation"].Value = "1";
}


private void dataGridView1_CellValidating(object sender, DataGridViewCellValidatingEventArgs e) {
  if (dataGridView1.Columns[e.ColumnIndex].Name == "StandardDeviation") {
    string userInput = dataGridView1.Rows[e.RowIndex].Cells["StandardDeviation"].EditedFormattedValue.ToString();
    if (!decimal.TryParse(userInput, out decimal dValue) || dValue == 0.0M) {
      dataGridView1.CancelEdit();
      MessageBox.Show("Standard Deviation can not be zero and must be a valid decimal number");
    }
  }
}


private void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e) {
  if (dataGridView1.Columns[dataGridView1.CurrentCell.ColumnIndex].Name == "StandardDeviation") {
    CurrentEditedCell = (TextBox)e.Control;
    if (CurrentEditedCell != null) {
      CurrentEditedCell.KeyPress -= new KeyPressEventHandler(DecimalNumbersOnlyCell_KeyPress);
      CurrentEditedCell.KeyPress += new KeyPressEventHandler(DecimalNumbersOnlyCell_KeyPress);
    }
  }
}

private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e) {
  if (CurrentEditedCell != null) {
    CurrentEditedCell.KeyPress -= new KeyPressEventHandler(DecimalNumbersOnlyCell_KeyPress);
  }
}


private void DecimalNumbersOnlyCell_KeyPress(object sender, KeyPressEventArgs e) {
  if (!char.IsControl(e.KeyChar) && !char.IsDigit(e.KeyChar) && e.KeyChar != '.') {
    e.Handled = true;
  }
  if (e.KeyChar == '.' && (sender as TextBox).Text.IndexOf('.') > -1) {
    e.Handled = true;
  }
}

I hope this makes sense and helps.

Answer 2

This error can occur because of different reasons, but I suggest you to:

• Check if the input strings are acceptable by the function.
• Even if the input strings are acceptable for conversion, the decimal you are trying to convert could be too big or too small, or precisions could be the problem, check if you exceed the limits.

I also suggest using Decimal.TryParse, it'll make your code more durable.

Answer 3

Using LINQ

foreach (DataGridViewRow dgvrow in dataGridView1.Rows)
        {
            //Set the column indexes what you want to sum
            double result = ((Convert.ToDouble(dgvrow.Cells[0].Value) - Convert.ToDouble(dgvrow.Cells[1].Value)) / Convert.ToDouble(dgvrow.Cells[2].Value));

            dgvrow.Cells.Cast<DataGridViewCell>()
            .Select(s => s).ToList<DataGridViewCell>()
            .ForEach(f => 
            {
                if (f.ColumnIndex == 3 && f.Value != null) //column index where to place your sum
                {
                    //have negative results
                    f.Value = Math.Round(result, 2)
                    .ToString();
                    
                    //negative results turn to 0
                    //f.Value = Math.Round(((result < 0) ? 0 : result), 2)
                    //.ToString();
                }
            });
        }

There are 2 types of result, having negative or not. It's up to you what you're going to use. I hope it helps!

Answer 4

You should first make sure that your string is really convertible to decimal. It seems that sd is not convertible to decimal.

To check this, you can use Decimal.TryParse. i.e

Decimal n;

(Convert.ToDecimal(dgvResult.CurrentRow.Cells[16].Value.ToString()) / Decimal.TryParse(sd, out n) ? n : 1 /* Default Value */)

And one more thing it's better to check sd before dividing, it should not be ZERO (I've set default value to 1).

Kindly check https://www.educative.io/edpresso/how-to-convert-a-string-to-a-decimal-in-c-sharp, for more information about Decimal.TryParse.