Using format in DataGridView

Question

I have some problems with my DGV, the DGV is bounded by a datatable with atleast 30 columns (all expect PK and FKs, are set as String), now there is a specific column that is the cost of the product which I want to make it like this sample: 359.99 €, every time the user clicks on that columns cell it will show nullvalue 0.00 €, the currency symbol can't be edited inside the cell, so is the dot seperator.

So that is my goal, and I am trying to do everything I mentioned in the windows form, I have tried by code, however it didn't do anything. At the end what kind of steps should I take for the desired result?

I have tried the following code for that column, however as I said before, it does nothing, though I found a similar question to mine in StarkOverflow, it seems it could be that I should enter this code before binding to DGV (actually I didn't bind it by code, I think I don't need that right now), but I think that is not possible because I need the DGV binded so I can edit it (there is also one unbound column, containing delete buttons for rows, but I dont think it matters here):

            advancedDataGridView1.Columns[14].DefaultCellStyle.Format = "c2";
            advancedDataGridView1.Columns[14].DefaultCellStyle.FormatProvider = CultureInfo.GetCultureInfo("fr-FR");

Thank you in advance.

Answer 1

I am not sure “why” you would want to store a “COST” value as a string. Cost implies a numeric decimal value and it “should” be stored in the DB as a decimal or other numeric value, but NOT as a string. This is going to create more work every time the string is used in a numeric context… as is your current case.

Additionally, this could leave the DB in an inconsistent state if some user types “Hello” as a “Cost” value. Something smells fishy and this would appear to actually “create” problems.

Is there some reason you are saving a “Cost” value as a string?

Setting the cells format to “c2” is not going to do anything “when” the column “type” is a string.

When formatting, the cell has to be of the proper “type” to format it properly. In this case, a decimal type would work for a currency value.

Also, it should be noted, that if your code “manually” adds the columns to the grid, then the lines of code…

dataGridView1.Columns[14].DefaultCellStyle.Format = "c2";
dataGridView1.Columns[14].DefaultCellStyle.FormatProvider = CultureInfo.GetCultureInfo("fr-FR");
 

… will obviously not work BEFORE these columns are added.

The same would apply when using a DataSource. You FIRST add the DataSource, THEN the code formats the column(s).

Again, this all seems very strange to store a decimal value into the DB as a string. I am making this assumption as your code “could” technically convert the decimal value in the DB to a string in the query. If this is the case, then a simple fix is to alter the query to not convert the value to a string.

Below is an example of what is described above. The example uses a DataTable as a DataSource. It has two columns such that the first column is a decimal type and the second column is of a string type. The table has some rows added such that each row has the “same” value in each column.

When the formatting is applied after the grid’s DataSource has been set, it should be clear, that the formatting is being “ignored” on the cells that are of string type and ARE applied to cells of the decimal type.

Since the culture info is set to “fr-FR”, then the “decimal point” is NOT used. Therefore when the user types in numbers, the user needs to use a “comma” where they want the decimal place to be.

For a complete example, drop a DataGridView onto a form and use the posted code below to test what is described above. I hope this makes sense.

private void Form1_Load(object sender, EventArgs e) {
  dataGridView1.DataSource = GetTable();
  dataGridView1.Columns[0].DefaultCellStyle.Format = "c2";
  dataGridView1.Columns[0].DefaultCellStyle.FormatProvider = CultureInfo.GetCultureInfo("fr-FR");
  dataGridView1.Columns[1].DefaultCellStyle.Format = "c2";
  dataGridView1.Columns[1].DefaultCellStyle.FormatProvider = CultureInfo.GetCultureInfo("fr-FR");
}

private DataTable GetTable() {
  DataTable dt = new DataTable();
  dt.Columns.Add("Col0", typeof(decimal));
  dt.Columns.Add("Col1", typeof(string));
  dt.Rows.Add(12.345, 12.345);
  dt.Rows.Add(1.35, 1.35);
  dt.Rows.Add(112.555, 112.555);
  dt.Rows.Add(12.333, 12.333);
  return dt;
}

private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e) {
  Debug.WriteLine("Error at Row: " + e.RowIndex + " Col: " + e.ColumnIndex);
  Debug.WriteLine("Error is:" + e.Exception.Message);
  Debug.WriteLine("Value is:" + dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].EditedFormattedValue);
}