[FIXED] How to enter letters in one column cell of the table and only numbers in the other column cell?

Issue

I want to create a table in C#. The table contains two column headers. One column header is the products and the other column header is the price of the products. I want to enter the data by clicking on a cell in the table. I want to enter only numbers in the cells of the products price column. Does anyone know how to allow only numbers in the cell below the product price?

Picture of the table

My ideas: My idea is that if I enter a string in the cell of the product price column, I should use "replace" to make the cell empty. Maybe regex could be used, although I don't know how. Another idea I have is that after I leave the cell, if there is a string in the cell, I have to empty the cell. But unfortunately I don't know how to implement my ideas. I don't know how to tackle the task.

I want to do my task using "Windows Forms App (.Net Framework)".

Solution

An often overlooked issue with going to all the trouble of allowing only numeric characters to be typed into a cell is… that the user can still “copy/paste” offending text into a cell. So technically, even if we allow only numeric values to be typed… this does NOT release us from having to validate that the text is a valid number when the user leaves the cell.

Therefore, since we have to validate the text anyway, then, wiring up the grids CellValidating event is made for this. When this event fires we know the user is trying to “leave” the cell, therefore, before the user leaves the cell, we need to check that the text is a valid number. Otherwise, the grid’s DataError will continue complaining until the offending text is fixed.

Given this, then you have a choice. If you do not care what values the user types into the cell and are only concerned when the user “leaves” the cell, then all you need to do is wire up the grids CellValidating event. If you want to “help” the user by allowing only numeric values to be typed into a cell then more work is needed.

In addition, If we wire up the grid’s CellValidating event, then, we can catch bad data before it gets passed down to the underlying data source. However, the question is… “What do we do when this happens?” We MUST do something; we cannot let this go without the grids DataError complaining.

There are several ways you can approach this and the approach I use below works such that if the text is not valid, then a message box is displayed stating the text in the cell is invalid. In addition, the offending cell is colored a light red to help the user see which cell is the offending cell. When the user presses the “OK” button on the dialog, the offending text is replaced with the previous value. Basically, the same thing the user would experience when pressing the Esc key minus our message box prompt to warn the user.

It is your call how you handle this, IMO, warn the user, then go back to the old value, then move on. This way the user isn’t continuously bugged until they fix the offending text, however, in some cases this may not be the desired behavior.

So, in this example we want to help the user and only allow numeric values to be entered into a cell AND when the user leaves the cell, we want to make sure that the typed/or pasted text is valid. The last part we discussed above… and we will use the grids Cellvaidating event specifically for when the user leaves the cell.

Big Picture…

From a big picture perspective, is what we will be doing below goes something like this: first we will wire-up the grids EditingControlShowing event. This event is the first step and it will allow the code to examine “which” cell is being edited. If the edited cell is one of the numeric cells, then we will simply cast that cell to a global TextBox variable named CurrentCellBeingEdited and then wire up that text boxes KeyPress event. Then we simply wait for the user to type characters.

When the user does indeed type something, then the text boxes KeyPress event will fire. In that event, we will simply check to see if the character is a valid numeric character. If the character is NOT a valid numeric character, then we will “ignore” that character and continue.

Finally, when the user leaves the cell, this will fire the grids CellValidating event where we will check for a valid numeric value. Obviously as already noted, we need this event just in case the user “copy/pasted” some offending text into the cell.

Below is a small example of what is described above. In the example, the grid has four (4) columns: Description, Cost, Quantity and Total. Description is a simple string cell and can have alpha characters and numbers. The Cost cell is a decimal value and we will allow only numeric values and a single decimal place. The Quantity column will be an int value where only numeric values are allowed. Finally a Totals column that is an expression column in the underlying DataTable that calculates the Cost times Quantity. Note the Totals column is not editable by the user.

Create a new winforms solution and drop a DataGridView onto the form and follow along below. The finished product should look something like…

To start, I made one global TextBox variable called CurrentCellBeingEdited. This variable will be set to the currently edited cell. This is used for convenience and is not really necessary, however for this example, it makes things a little clearer.

TextBox CurrentCellBeingEdited = null;

We will assign this variable to the cell the user typed into IF the cell is one of the numeric valued cells. Then we will subscribe (wire-up) the TextBox’s KeyPress event to the proper KeyPress event. This is all done in the grids EditingControlShowing event and may look something like…

private void dataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e) {
  if (CurrentCellBeingEdited != null) {
    CurrentCellBeingEdited.KeyPress -= new KeyPressEventHandler(DecimalNumbersOnlyCell_KeyPress);
    CurrentCellBeingEdited.KeyPress -= new KeyPressEventHandler(NumbersOnlyCell_KeyPress);
  }
  string targetCellColName = dataGridView1.Columns[dataGridView1.CurrentCell.ColumnIndex].Name;
  if (targetCellColName == "Cost" || targetCellColName == "Qty") {
    CurrentCellBeingEdited = (TextBox)e.Control;
    if (targetCellColName == "Cost") {
      CurrentCellBeingEdited.KeyPress += new KeyPressEventHandler(DecimalNumbersOnlyCell_KeyPress);
    }
    else {
      // Qty cell
      CurrentCellBeingEdited.KeyPress += new KeyPressEventHandler(NumbersOnlyCell_KeyPress);
    }
  }
}

This event simply wires up the TextBox cell to the proper key press event if needed. The first if statement…

if (CurrentCellBeingEdited != null) {
  CurrentCellBeingEdited.KeyPress -= new KeyPressEventHandler(DecimalNumbersOnlyCell_KeyPress);
  CurrentCellBeingEdited.KeyPress -= new KeyPressEventHandler(NumbersOnlyCell_KeyPress);
}

is used to unsubscribe (un-wire) any previously subscribed to event. This prevents the event from firing in the wrong cells. Example; if the user selects the Description cell. And the same idea applies to the Quantity cell where we do not want the user to type a period for a decimal place. The main point is that if we do not “unsubscribe” the text box from the event, then it may get fired multiple times and possibly for the wrong cells.

After any previously enabled event is un-subscribed, the code simply checks which cell is being edited. If the edited cell is a Cost or Quantity column, then the code casts our global variable CurrentCellBeingEdited to the edited cell, then subscribes to the appropriate event.

Next we will need the two KeyPress events for the Cost and Quantity cells and they may look something like…

// Quantity cell
private void NumbersOnlyCell_KeyPress(object sender, KeyPressEventArgs e) {
  if (!char.IsControl(e.KeyChar) && !char.IsDigit(e.KeyChar)) {
    e.Handled = true;
  }
}

// Cost cell
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;
  }
}

And finally, the grids CellValidating event. Note, the code allows cells to be “empty.” The code checks to see if the cell is a Cost or Quantity column, then applies a TryParse to the appropriate cell to validate if the text in the cell is indeed a valid int or decimal value. If the TryParse fails, then the cell is colored red and a message box is displayed indicating the invalid text. After the user clicks the OK button on the message box, the edit is canceled.

private void dataGridView1_CellValidating(object sender, DataGridViewCellValidatingEventArgs e) {
  if (CurrentCellBeingEdited != null) {
    string targetCellColName = dataGridView1.Columns[e.ColumnIndex].Name;
    if (targetCellColName == "Cost" || targetCellColName == "Qty") {
      if (!string.IsNullOrEmpty(CurrentCellBeingEdited.Text)) { // <- Allow empty cells
        bool valid = true;
        if (targetCellColName == "Cost") {
          if (!decimal.TryParse(CurrentCellBeingEdited.Text, out decimal value)) {
            valid = false;
          }
        }
        if (targetCellColName == "Qty") {
          if (!int.TryParse(CurrentCellBeingEdited.Text, out int value2)) {
            valid = false;
          }
        }
        if (!valid) {
          CurrentCellBeingEdited.BackColor = Color.LightCoral;
          MessageBox.Show("Invalid input - value will revert to previous amount");
          dataGridView1.CancelEdit();
          CurrentCellBeingEdited.BackColor = Color.White;
        }
      }
    }
  }
}

Putting all this together and to complete the example, the code below uses the events above to demonstrate this functionality.

TextBox CurrentCellBeingEdited = null;

public Form1() {
  InitializeComponent();
  dataGridView1.CellValidating += new DataGridViewCellValidatingEventHandler(dataGridView1_CellValidating);
  dataGridView1.EditingControlShowing += new DataGridViewEditingControlShowingEventHandler(dataGridView1_EditingControlShowing);
}


private void Form1_Load(object sender, EventArgs e) {
  DataTable GridTable = GetDT();
  GetData(GridTable);
  dataGridView1.DataSource = GridTable;
}

private DataTable GetDT() {
  DataTable dt = new DataTable();
  dt.Columns.Add("Description", typeof(string));
  dt.Columns.Add("Cost", typeof(decimal));
  dt.Columns.Add("Qty", typeof(int));
  dt.Columns.Add("Total", typeof(decimal), "Cost * Qty");
  return dt;
}

private void GetData(DataTable dt) {
  dt.Rows.Add("Product1", 12.49, 2);
  dt.Rows.Add("Product3", 2.33, 3);
  dt.Rows.Add("Product16", 5.00, 12);
}

I hope this makes sense and helps.

Answered By - JohnG

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)