none
Cannot delete data from a date/time field

    Question

  • I am using VB 2012 and an Access 2007 database.  I have a data-bound date/time field called "birthdate" that I can change, but if I delete the data in it and the field property "CausesValidation" of the date/time field is set to true, the LostFocus event persists and I cannot move off the record.

    I tried setting the field property "CausesValidation" of the date/time field to false and I can navigate among records but the data in the field doesn't change because the "dataset.has changes" does not show true.  I then tried to force a null as follows:

        Private Sub textBoxDOB_LostFocus(sender As Object, e As EventArgs) Handles textBoxDOB.LostFocus
            Dim index As Integer
            If textBoxDOB.Text <> "" Then
                If validate_date(textBoxDOB.Text) = False Then
                    MsgBox("invalid DOB")
                Else
                    calculate_age()
                End If
            Else
                index = Me.ContactBindingSource.Position
                Me.ContactsDataSet.contact.Rows(index).Item("birthdate") = DBNull.Value
            End If
        End Sub

     This works on the selected record but there must be an easier and better way.  Any suggestions?



    RONATMOODYLAKE


    Tuesday, October 22, 2013 7:10 PM

Answers

All replies

  • Hello,

    Could you please share some codes that you have used to delete the data and the regarding tabel structure so that we can do a test with these information.

    And for deleting data from a date/time field in Access 2007, there is a walkthrough for it:
    http://office.microsoft.com/en-in/access-help/insert-create-or-delete-a-field-that-stores-date-values-HA010096321.aspx

    You can check it.

    Thanks&Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 23, 2013 9:55 AM
    Moderator
  • I am deleting the data by highlighting the field with the mouse and pressing the delete key.  The data field is date/time, format short date, required:no, no validation rule.  The referenced article is no help.  Deleting all fields except date/time fields works just fine.

    RONATMOODYLAKE

    Wednesday, October 23, 2013 12:58 PM
  • Hi,

    Could you please share your program codes and the table structure so that we can study on it or you can upload it to skydive?

    Thanks &Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 24, 2013 9:12 AM
    Moderator
  • I have already posted the code here, and I have described the data field as it appears on the Access 2007 design screen.  What more can I do?

    RONATMOODYLAKE

    Thursday, October 24, 2013 1:51 PM
  • Hi Ron,

    You don't need to post any more code. I understand what your problem is. 

    One thing I always do is create my own sub-classes of the WinForm controls. See my blog post about this:

    http://geek-goddess-bonnie.blogspot.com/2009/10/why-and-how-to-sub-class-base-classes.html

    One thing you could do is to create a DateTextBox class, inherited from TextBox and use this DateTextBox class anytime you need to capture date data. There are a number of ways to handle this, but probably the easiest is to handle the Binding's Parse event. The Parse event is what controls what gets saved back to the databound item (in other words, gets saved back to the column in your DataTable). Sorry this is in C#, but you can use an online converter:

    public class DateTextBox : TextBox
    {
        // set up handlers in the constructor
        public DateTextBox()
        {
            this.DataBindings.CollectionChanged += new CollectionChangeEventHandler(DataBindingChangeHandler);
        }
        
        private void DataBindingChangeHandler(object sender, CollectionChangeEventArgs e)
        {
            if (this.DataBindings.Count == 1)
            {
                this.DataBindings[0].Format += new ConvertEventHandler(FormatHandler);
                this.DataBindings[0].Parse  += new ConvertEventHandler(ParseHandler);
            }
        }
    
        protected virtual void FormatHandler(object sender, ConvertEventArgs e)
        {
            // you could put some code in here if you wanted to display your date differently
        }
        protected virtual void ParseHandler(object sender, ConvertEventArgs e)
        {
            // This is what gets save back to your DataTable
            if (e.Value.ToString == "")
                e.Value = DBNull.Value;
    }
    


    ~~Bonnie Berent DeWitt [C# MVP]

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

    Thursday, October 24, 2013 2:55 PM
  • This looks good.  Could you please give an example of how/when to use it in my code.  I have a couple of data-bound date fields that I want to format and use to calculate someone's age.

    Thanks again for your help.


    RONATMOODYLAKE

    Monday, October 28, 2013 12:47 PM
  • Use your DateTextBox class any time you need a TextBox that will be databound to a DateTime column. As I mentioned in my blog post, this class can be added to the ToolBox and you can simply drag/drop onto your form/control in the IDE, as you do with any control.

    You might want to make another sub-class for birthdates to calculate someone's age ...

    public class BirthdateTextBox : DateTextBox
    {
        public int CalculateAge()
        {
            // Code here to return a calculated age
            // I'd include some code, but I don't have an algorithm 
            // memorized and I currently don't have VS up and running yet this morning
        }
    }


    ~~Bonnie Berent DeWitt [C# MVP]

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

    Monday, October 28, 2013 1:55 PM
  • I haven't got a clue how to define this thing.  I am sure there is a BIG difference using Visual Studio 2012 VB.  I would really like to add this class to my toolbox.

    I have added a class named CustomControls

    Here is my pathetic start - trying to define the constructor but nothing seems to work.  Am I on the right track?

    This is what's in the CustomControls class so far:

    Imports System
    Imports System.Drawing
    Imports System.Collections
    Imports System.ComponentModel
    Imports System.Windows.Forms
    Imports System.Data

    Namespace CustomControls
        Public Class DateTextBox
            Public DateTextBox As TextBox

        End Class
    End Namespace


    RONATMOODYLAKE

    Tuesday, October 29, 2013 2:37 PM
  • I tried code translators and got:

    Public Class DateTextBox
        Inherits TextBox
        ' set up handlers in the constructor
        Public Sub New()
            Me.DataBindings.CollectionChanged += New CollectionChangeEventHandler(AddressOf DataBindingChangeHandler)
        End Sub

        Private Sub DataBindingChangeHandler(sender As Object, e As CollectionChangeEventArgs)
            If Me.DataBindings.Count = 1 Then
                Me.DataBindings(0).Format += New ConvertEventHandler(AddressOf FormatHandler)
                Me.DataBindings(0).Parse += New ConvertEventHandler(AddressOf ParseHandler)
            End If
        End Sub

        Protected Overridable Sub FormatHandler(sender As Object, e As ConvertEventArgs)
            ' you could put some code in here if you wanted to display your date differently
        End Sub
        Protected Overridable Sub ParseHandler(sender As Object, e As ConvertEventArgs)
            ' This is what gets save back to your DataTable
            If e.Value.ToString = "" Then
                e.Value = DBNull.Value
            End If
        End Sub
    End Class

    The Me. statements are in error:

    Error 1 'Public Event CollectionChanged(sender As Object, e As System.ComponentModel.CollectionChangeEventArgs)' is an event, and cannot be called directly. Use a 'RaiseEvent' statement to raise an event.

    What syntax can be used for 'RaiseEvent'?


    RONATMOODYLAKE

    Tuesday, October 29, 2013 6:45 PM
  • Ron,

    VB adds event handlers differently than C#. I believe you need syntax similar to this:

    AddHandler Me.DataBindings.CollectionChanged, New CollectionChangeEventHandler(AddressOf DataBindingChangeHandler)


    ~~Bonnie Berent DeWitt [C# MVP]

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

    Tuesday, October 29, 2013 7:45 PM
  • Got it to compile!!  Now, how can I get it into the tool box so I can actually put it on a form?

    This is pretty neat!


    RONATMOODYLAKE

    Tuesday, October 29, 2013 8:23 PM
  • Got it to compile!!  Now, how can I get it into the tool box so I can actually put it on a form?

    This is pretty neat!


    Glad you like the idea! Now putting into the ToolBox probably varies between VS versions and might also be somewhat different in the VB IDE than in the C# IDE. I'm using VS 2010, so I'm kind of out of date as far as giving you a step-by-step. How about if you post another question into either the Windows Forms Data Controls And DataBinding Forum or maybe the VB forum.

    ~~Bonnie Berent DeWitt [C# MVP]

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

    Tuesday, October 29, 2013 8:34 PM
  • Now that I have fixed the date-bound fields, I discover that my textbox fields that are bound to numbers (like integers or long integers) behave the same way.  I am using Access 2007.  I am starting to wonder if there are serious flaws in the data binding operations.  If I enter a numerical value in one of these numerical fields everything works fine, but if I highlight the numerical value and press the delete key to clear the field, my form locks up entirely.  I cannot move the binding navigator to another record and I cannot even move the focus to another field on the form.  This lock-up does not occur if I set the "CausesValidation' property of the text box to False, but if I do that, the data is not preserved - ie, the deleted field is still not blank.  Others should have seen this behavior.  I know I can work around this by re-defining the Access numerical fields to text, and that's OK.  I can also introduce a custom text box (as in the datetextbox, above) but that seems strange.  Have I found a bug?  Someone at Microsoft should be interested in this odd behavior.

    RONATMOODYLAKE


    Wednesday, October 30, 2013 8:53 PM
  • Yes, there are some issues with databinding numeric fields in a TextBox, but nothing that can't be overcome. This is why I stressed in a previous post here (with a link to my blog post) about always sub-classing any controls you are going to be using on your Forms. You never know when you want to add some additional behavior/workarounds to the standard .NET control ... and sub-classing them and always using your sub-classes is the probably the best way to do that.

    So, make your own NumericTextBox class (inherited from TextBox or from a base MyTextBox class you may want to write). Add something like this for the Format/Parse handlers:

    protected override void FormatHandler(object sender, ConvertEventArgs e)
    {
    	if (this.DisplayDefaultZero) // property of sub-class
    	{
    		if (e.Value == System.DBNull.Value)
    			e.Value = (int)0;
    	}
    	if (e.DesiredType == typeof(string))
    	{
    		if (e.Value is Decimal)
    			e.Value = ((decimal)e.Value).ToString(this.m_FormatString);
    		else if (e.Value is int)
    			e.Value = ((int)e.Value).ToString(this.m_FormatString);
    		else if (e.Value is double)
    			e.Value = ((double)e.Value).ToString(this.FormatString);
    	}
    }
    protected override void ParseHandler(object sender, ConvertEventArgs e)
    {
    	if (((string)e.Value).Trim().Length == 0)
    		e.Value = "0";
    
    	if (e.DesiredType == typeof(decimal) || e.DesiredType == typeof(double))
    		e.Value = Decimal.Parse((string)e.Value, System.Globalization.NumberStyles.Any);
    	else if (e.DesiredType == typeof(int))
    	{
    		string[] s = ((string)e.Value).Trim().Split('.');
    		e.Value = Decimal.Parse(s[0], System.Globalization.NumberStyles.Any);
    	}
    }

    Yes, it's some work up-front ... but it makes is so much easier going forward.


    ~~Bonnie Berent DeWitt [C# MVP]

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

    Wednesday, October 30, 2013 10:09 PM
  • Almost there!  Cannot compile "If Me.DisplayDefaultZero" and "Me.m_FormatString" statements.  How to fix?

    RONATMOODYLAKE

    Wednesday, October 30, 2013 11:12 PM
  • Sorry ... those were properties I had on my own NumericTextBox class. You can add those properties to your class ... I defaulted mine as follows: DisplayDefaultZero is true and FormatString "##0"

    ~~Bonnie Berent DeWitt [C# MVP]

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

    Thursday, October 31, 2013 1:14 AM
  • This has been a great help!

    I am glad that someone finally admits that "there are some issues with numeric fields".

    I see that the data type "currency" doesn't seem to be supported.  How do you manage currency data?  Do you use text boxes?  If so, do you have another control for "CurrencyTextBoxes"?


    RONATMOODYLAKE

    Thursday, October 31, 2013 9:43 PM
  • Hi Ron,

    In my line of business I've never had to use Currency ... however, you could make your NumericTextBox work by setting the FormatString property to one for currency once you have the NumericTextBox instance on your Form  ... I don't know what that string is off the top of my head, but you could easily find it with a quick Google search.


    ~~Bonnie Berent DeWitt [C# MVP]

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

    Saturday, November 02, 2013 3:40 PM