none
C# winform remove date from datagridview / Access database RRS feed

  • Question

  • I have a winform with datagridview MS Access database. I have 2 columns linked to datetimepicker. In code I copy the date from one column to the other and the copied cell is blank. when I reopen the form the blank cell shows 12/30/1899. How can i keep it blank until I enter another date?

    This is the code I use to copy from one cell to the other (button click)

    private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
           {
    
               if (e.ColumnIndex == dataGridView1.Columns [19].Index)
               {
    
                   {
                       dataGridView1.CurrentRow.Cells[16].Value = dataGridView1.CurrentRow.Cells[17].Value;
                       dataGridView1.CurrentRow.Cells[17].Value =  "";
                   }
               }
           }
       }
       }

    I have tried the below code also. With no luck

     private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
            {
                if (formatting.Value != null)
                {
                    if (e.ColumnIndex == 17)
                    {
                       
                        if ((DateTime)dataGridView1.CurrentRow.Cells[17].Value == DateTime.MinValue)
                        {
                            formatting.Value = "";
                        }
                    }
                }
            }
        }
    }


    Booney440

    • Moved by CoolDadTx Monday, January 20, 2020 4:51 PM Winforms related
    Monday, January 20, 2020 4:35 PM

Answers

  • Hi Booney,
     
    I believe the problem is that TableAdapters don't handle DBNull properly. I think it handles it by setting that "empty" column to DateTime.MinValue, which then of course gets stored in the database that way. You could handle this if you use a Stored Procedure for your TableAdapter.Update().  In the StoredProcedure, you could check if the date parameter is '1899-12-30' and set the date column in your database to NULL. This can only be done with a Stored Procedure. I've only worked with SQL Server databases, so I don't know the "format" for dates in Access (so you may have to use a different format than '1899-12-30').

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by Booney440 Sunday, February 16, 2020 5:00 PM
    Thursday, February 13, 2020 10:19 PM

All replies

  • Hi Booney440, 
    Based on your description, what is relationship between datetimepicker and dataGridview column? I noticed that your winform is accessed to the database, so did you update the database after copying the date from one column to the other?
    If not, you can update the database by following code.
    SqlDesigner SqlDesigner = new SqlDesigner();
    private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
    {
                
        if (e.ColumnIndex == dataGridView1.Columns[0].Index)
        {             
                dataGridView1.CurrentRow.Cells[1].Value = dataGridView1.CurrentRow.Cells[5].Value;
                string currentIndex = dataGridView1.CurrentRow.Cells[2].Value.ToString();
                SqlDesigner.ExecuteNoQuery("update test set Date='"  + "'where Id='" + currentIndex + "'");
        }
    }
    
     class SqlDesigner
        {
            private static string connStr ="connectstring";
            public static int ExecuteNoQuery(string sql)
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        return cmd.ExecuteNonQuery();
    
                    }
                }
            }
         }

    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, January 21, 2020 5:58 AM
  • Hello,

    It's best not to touch the DataGridView, only work with the underlying data source. In the following example clicking on the second column if not a null value sets the third column to the second column value then sets the second column value to null.

    Note that the DataTable is mocked and that represents data read from a database. If your code is using a DataAdapter then when saving the changes done in the code below will be reflected in the database table.

    using System;
    using System.Data;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form1 : Form
        {
            BindingSource _bindingSource = new BindingSource();
            public Form1()
            {
                InitializeComponent();
                Shown += Form1_Shown;
                dataGridView1.CellClick += DataGridView1_CellClick;
            }
    
            private void DataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
            {
                if (dataGridView1.Columns[e.ColumnIndex].Name != "d1") return;
                if (_bindingSource.Current == null) return;
    
                var row = ((DataRowView)_bindingSource.Current).Row;
    
                if (row["d1"] != DBNull.Value)
                {
                    if (row.Field<DateTime>("d1") == DateTime.MinValue)
                    {
                        return;
                                
                    }
    
                    row.SetField("d2", row.Field<DateTime>("d1"));
                    row.SetField("d1", DateTime.MinValue);
                }
                else
                {
                    MessageBox.Show("NULL");
                }
            }
    
            /// <summary>
            /// Imagine the DataTable is loaded from a database table
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void Form1_Shown(object sender, EventArgs e)
            {
                var dt = new DataTable();
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "Event", DataType = typeof(string) });
    
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "d1", DataType = typeof(DateTime) });
    
                dt.Columns.Add(new DataColumn()
                    { ColumnName = "d2", DataType = typeof(DateTime) });
    
                dt.Rows.Add("Jog", null);
                dt.Rows.Add("Run", DateTime.Now);
    
                dt.Rows.Add("walk", DateTime.MinValue);
                dt.Rows.Add("rest", DateTime.Now.AddDays(2));
    
                _bindingSource.DataSource = dt;
                dataGridView1.DataSource = _bindingSource;
            }
          
        }
    }
    
    I've always used a BindingSource with a DataGridView as it makes it easy to perform task without touching a DataGridView or any other control that is bound to the underlying data from a database or mocked data.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, January 23, 2020 11:54 AM
    Moderator
  • I get the sane results below are Current , date move and after I reopen the database.

    testTrialRosterBindingSource.EndEdit();
                    test_Trial_RosterTableAdapter.Update(rosterDataSet.Test_Trial_Roster);
                    dataGridView1.Refresh();


    Booney440

    Sunday, January 26, 2020 5:26 PM
  • I wonder if setting the column's value to DBNull.Value instead of to DateTime.MinValue might work?

    I haven't tested anything, this is just a guess and what I would try.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, February 6, 2020 6:01 AM
  • Hi Booney440,
    Has your problem been solved? If it is resolved, we suggest that you mark it as the answer. So it can help other people who have the same problem find a solution quickly. If not solved, what problem did you encounter?
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 6, 2020 6:16 AM
  • Hi BonnieB,
    Has your problem been solved? 

    Booney / Bonnie … I can see how you got the two of us mixed up, Daniel!!  ;0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, February 6, 2020 3:24 PM
  • still the same issue


    Booney440

    Wednesday, February 12, 2020 5:19 PM
  • Hi Booney,
     
    I believe the problem is that TableAdapters don't handle DBNull properly. I think it handles it by setting that "empty" column to DateTime.MinValue, which then of course gets stored in the database that way. You could handle this if you use a Stored Procedure for your TableAdapter.Update().  In the StoredProcedure, you could check if the date parameter is '1899-12-30' and set the date column in your database to NULL. This can only be done with a Stored Procedure. I've only worked with SQL Server databases, so I don't know the "format" for dates in Access (so you may have to use a different format than '1899-12-30').

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by Booney440 Sunday, February 16, 2020 5:00 PM
    Thursday, February 13, 2020 10:19 PM