How do I manipulate the format on a DataGridView that is bound to a Data Source?

Question

I´m using SQL Server 2005 and Visual Studio 2008, C#.

In the data source (the SQL Server data table) I use the DateTime format mm/dd/yyyy, however, in a forms overview (DataGridView) users would like to see a completely other format, with year, week number and day number of week (yyww,d) which is in string format. I´ve created an algorithm for the transformation between values (date to weekday), but can I populate the affected cells with yyww,d (string) instead of mm/dd/yyyy (DateTime)?

This is what I´ve been testing out, without success (and note, it´s on the last line the problem becomes obvious, as the cell value won´t accept a string on runtime - it still wants to be a DateTime...)

  private void DataGridViewQueryFindRequests_CellFormatting(
     object sender, DataGridViewCellFormattingEventArgs e)
  {
     string weekAndDay = "";

     DataGridViewCell cell = 
         DataGridViewQueryFindRequests.Rows[e.RowIndex].Cells[e.ColumnIndex];


     if (cell.ColumnIndex == 13 && cell.Value == null)
        mEmptyRow = true;

     if ((cell.ColumnIndex == 14 || cell.ColumnIndex == 15) && !mEmptyRow)
     {
        weekAndDay = ClassWeeksAndDates.dateToWeekNumber(Convert.ToDateTime(cell.Value));

        cell.ValueType = typeof(string);

        cell.Value = weekAndDay;
     }
   }

Answer 1

I had a similar problem just few days ago.

Try to not change the ValueType in CellFormatting event but only the Value.

To avoid receiving a FormatException then at runtime, you have to convert/parse the value when you want to store it back

You can to this in the CellParsing event and you can convert here your desidered visible format yyww,d to the accepted DateTime format mm/dd/yyyy

Maybe look at this answer could help: http://stackoverflow.com/questions/3054767/datagridview-cell-editing-issue-with-decimal-hexadecimal-formatting/3062534#3062534