How to update date column as custom format in datagridview? - c#

Question

In a datagridview I am displaying several fields from MySql database.

One of the field is DATE type. I am displaying the DATE column as custom format i.e dd/MM/yyyy in datagridview

        dataGridView1.Columns["DATE"].DefaultCellStyle.Format = "dd/MM/yyyy";

Here I am using CommandBuilder to update the database from datagridview.

When I want to edit the date column, I am entering the date in MM/dd/yyyy format. It works.

But I need to update the date column in dd/MM/yyyy format from datagridview. If I do so, I get the following exception from datagridview

The following exception occurred in the DataGridView


System.FormatException: String was not recognized as a valid DateTime.

at System.DateTimeParse.Parse(String s, DateTimeFormat dtfi,DateTimeStyles styles)

at System.DateTime.Parse(String s, IFormatProvider provider)

How do I handle this exception to accept date string as "dd/MM/yyyy" from datagridview?

Thanks in advance.

Answer 1

In the date column, add a datetimepicker control in the datagridview and change the format as you want. Here I am choosing to format the date as dd/MM/yyyy

//Declare this variable     
    DateTimePicker dtp = new DateTimePicker();  //DateTimePicker  
    Rectangle _Rectangle;  

    public Form1()
    {
        InitializeComponent();

        dtp.Visible = false;  //  
        dtp.Format = DateTimePickerFormat.Custom;  
    }

    private void dtp_TextChange(object sender, EventArgs e)
    {
        dataGridView1.CurrentCell.Value = dtp.Value.ToString();  //
    }

    private void dtp_CloseUp(object sender, EventArgs e)
    {
        dtp.Visible = false;
    }


    private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            connection.ConnectionString = "datasource=localhost;port=3306;username=root;password=root";
            string sql = "SELECT * FROM database.table";
            dataAdapter = new MySqlDataAdapter(sql, connection);
            dataTable = new DataTable();
            connection.Open();
            dataAdapter.Fill(dataTable);
            connection.Close();

            dataGridView1.DataSource = dataTable;
            dataGridView1.Columns["DATE"].DefaultCellStyle.Format = "dd/MM/yyyy";
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
    private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
    {
            if (e.ColumnIndex == 2)  // here 2 is the date column in datagridview1
            {
                dataGridView1.Controls.Add(dtp);  
                dtp.Value =Convert.ToDateTime(dataGridView1.CurrentCell.Value.ToString());
                dtp.CustomFormat = "dd/MM/yyyy";    // change the custom format here to display on the datetimepicker
                dtp.TextChanged += new EventHandler(dtp_TextChange); //dtp_TextChange
                dtp.Visible = true;  //  


                _Rectangle = dataGridView1.GetCellDisplayRectangle(e.ColumnIndex, e.RowIndex, true); //  
                dtp.Size = new Size(_Rectangle.Width, _Rectangle.Height); //  
                dtp.Location = new Point(_Rectangle.X, _Rectangle.Y); //  

                dtp.CloseUp+=new EventHandler(dtp_CloseUp);

            }
            else
                dtp.Visible = false;  
    }

    private void dataGridView1_ColumnWidthChanged(object sender, DataGridViewColumnEventArgs e)
    {
        dtp.Visible = false;  
    }

    private void dataGridView1_Scroll(object sender, ScrollEventArgs e)
    {
        dtp.Visible = false;  
    }

This works perfectly for me. If you get any error, let me know.

Answer 2

My Suggestion would be, you have to manually check the format of each cell. Use CellValueChanged event.

In your main class:

String msg="";
private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
    string value = dataGridView1.CurrentCell.Value.ToString();
    dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells["DATE"].Value = trapInputs(value);
    MessageBox.Show(msg);
}

public string trapInputs(string value)
{
    DateTime val = DateTime.Now;
            try
            {
                val = Convert.ToDateTime(value);
            }
            catch
            {
                if (value.Split('/').Length == 3)
                {
                    string[] arr = value.Split('/');
                    try
                    {
                        if (Convert.ToInt32(arr[1]) > Convert.ToInt32(arr[0]))
                        {
                            val = Convert.ToDateTime(arr[1] + "/" + arr[0] + "/" + arr[2]);
                        }
                    }
                    catch
                    {
                        msg = "Invalid date.";
                    }
                }
                else if(value.Split('-').Length==3)
                {
                    string[] arr = value.Split('-');
                    try
                    {
                        if (Convert.ToInt32(arr[1]) > Convert.ToInt32(arr[0]))
                        {
                            val = Convert.ToDateTime(arr[1] + "-" + arr[0] + "-" + arr[2]);
                        }
                    }
                    catch
                    {
                        msg = "Invalid date.";
                    }
                }
                else
                {
                    msg = "Invalid date.";
                }
            }
            value = String.Format("{0:yyyy-MM-dd}", val);
}