none
Formatting and Advanced Binding Window and Null Values

    Question

  • The popup window that comes up when you click on the
    Control Properties Window->DataBindings->Advanced
    allows you to set the value that the control uses to display a DBNull value. Unfortunately, if you leave the 'Null value' field of this window blank, the DBNull property for the binding generated behind the scenes is set to null. How do you set the 'Null Value' field to an empty string using the UI (not manually changing the binding in your code)?

    Wednesday, March 21, 2007 8:45 PM

All replies

  • Hi,

     

    I'm going to attempt an answer as to why it's like that.

     

    In this "null value" field, you are given a chance to enter some text to let the user know that this field is actually null (or DBNull).

     

    This "null value" string is not actually stored in the DataSource, but rather it is written to the Control during the Format event of the Binding class.

     

    Now, I don't really think there would be any advantage putting an string.Empty value for this field, as it would leave the TextBox blank anyway (using TextBox as an example here), which is already accomplished by leaving the "null value" field blank.

     

    On the other hand, if you were thinking of having a default value at the data source level, then you should take care of it at that level, not the UI level. You could, for example, specify a NullValue in the DataSet designer for that particular column. Or you could even (depending on your database server) specify a default value for that column so that it's never actually null.

     

    Maybe you have a specific use-case for wanting this ? If so, could you give us a bit more detail ?

     

    Regards,

     

    Luc

    Wednesday, April 04, 2007 4:37 PM
  •  

    You make a very good point. However, the scenario where this is problematic is when you bind to a non-string type such as an int or DateTime. For example, when you use a TextBox to data bind to a nullable column of type int in a DataTable, Windows Forms will not allow you to leave the TextBox until a valid int has been entered, or the user enters the string specified in the 'null value' field. In this case, it would be really nice to have an empty textbox equate to a null value. You can do this by explicitly setting the binding's DBNull property to string.Empty. You have to manually do this in code. It would be nice if the designer would do it for you.

    Wednesday, April 04, 2007 5:16 PM
  • Tyler,

     

    I have tried to reproduce the behavior where the TextBox would not let me tab away if a proper int is not entered, but I couldn't. I tried with either AllowDBNull  false or true. I can still tab away from the TextBox.

     

    Could you explain how to reproduce it ?

     

    Luc

    Wednesday, April 04, 2007 8:54 PM
  • Luc enter a space into the textbox bound to a number column which allows nulls and delete it.  Then try and tab off the textbox it won't let you.  To fix it try something like this

     

     

    Code Snippet

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'NorthwindDataSet.Products' table. You can move, or remove it, as needed.
            Me.ProductsTableAdapter.Fill(Me.NorthwindDataSet.Products)
            Dim b As Binding = Me.SupplierIDTextBox.DataBindings("Text")
            AddHandler b.Parse, AddressOf AllowNull

        End Sub

     

        Private Sub AllowNull(ByVal sender As Object, ByVal cevent As ConvertEventArgs)
            If String.IsNullOrEmpty(cevent.Value.ToString) Then
                cevent.Value = DBNull.Value
            End If
        End Sub

     

     

    Thursday, April 05, 2007 12:26 AM
  • Ken,

     

    I don't seem to be able to reproduce this behavior.

     

    I have a typed DataSet with one DataTable. It has an Int32 column, and its AllowDBNull value is set to true.

     

    I have a BindingSource whose DataSource is the DataTable.

     

    Finally, I have this TextBox who's bound to this column through the BindingSource. The DataSourceUpdateMode is set to OnValidation.

     

    I can tab off wether I enter some numer then delete it, or even text, a space etc. Whatever I enter (or not) in this TextBox, I can tab off.

     

    Maybe you guys have some other settings, or are handling some event that will prevent tabbing off ?

     

    Funny.

     

    Luc

    Thursday, April 05, 2007 1:50 AM
  • I dragged the table from the datasource window to the form.  Sounds like you manually bound the textbox
    Thursday, April 05, 2007 10:37 AM
  • Ken,

     

    You are correct. If I drag from the Data Source window, I can't tab off.

     

    I tried to see if there were differences in the serialized code (InitializeComponents), but can't see any.

     

    Do you have any idea as to what might cause this ?

     

    Luc

    Thursday, April 05, 2007 11:21 AM
  • this is an missing feature of "Formatting and Advanced Binding" for "Null value". they should have a checkbox there to allow people to use blank as null.

     

    here is the quick fix. it will be good if something can report this to MS.. I cannot find a place to submit this problem. even in VS2008 and SP1, they still have problem!!!

     

     

    quick fix:

    1. click on the textbox, Data, Adanced to get to "Formatting and Adcanced Binding"

    2. in the "Null Value" enter "7878r" and ok all the way out.

    3. open xxxx.Designer.cs (the designer of the file you have that textbox), replace "7878r" to ""

    4. test it. you should be good now.

     

     

    reason:

    if you didn't enter anything in the "Null Value" then it thinks you don't need it so it doesn't code "" for you.

     

    disadvantage:

    one you enter advanced again for that textbox, you need to do it again in the designer.cs

     

     

    followup:

    someone please reports the feature missing to MS... this has been taking to long for them to fix this problem and I saw a lot of people have this issue.

     

    thanks!

     

     

     

     

    • Proposed as answer by D. Kelley Thursday, October 25, 2012 4:23 PM
    • Unproposed as answer by D. Kelley Thursday, October 25, 2012 4:24 PM
    • Proposed as answer by D. Kelley Thursday, October 25, 2012 4:24 PM
    Monday, July 07, 2008 10:26 PM
  • Thank you so much, BobYang, for your answer.  It is simple, concise and to the point.  I've been looking for this answer all over the place and found parts and pieces of this same answer but nothing that was as simple as your answer (which helped me tremendously).

    To clarify steps 2 & 3, you don't need to enter "7878r" into the "Null value" textbox, just enter anything there then search for that same string in the xxxx.Designer.cs file.

    Other threads have almost given the same answer. But what was missing was these 4 simple steps Bob laid out.  To tie answers in other threads to this answer, the point is that the designer creates the binding as expected (see Ken Tucker's comments in this thread).  And the following lines would be created in the xxxx.Designer.cs file:

                // 
                // AgeTextBox
                // 
                AgeTextBox.DataBindings.Add(new Binding("Text", AccountBindingSource, "Age", true));
                AgeTextBox.Location = new Point(68, 45);
                AgeTextBox.Name = "AgeTextBox";
    etc.

    But you then need to edit AgeTextBox.DataBindings.Add() as follows:

    // // AgeTextBox // AgeTextBox.DataBindings.Add(new Binding("Text", AccountBindingSource, "Age", true, DataSourceUpdateMode.OnValidation, "")); AgeTextBox.Location = new Point(68, 45); AgeTextBox.Name = "AgeTextBox";
    etc.

    Apparently, the two extra parameters --> DataSourceUpdateMode.OnValidation, "" <-- will put the empty string into the textbox after the validation process has accepted and saved the null value to the database.

    So the problem and solution from what I've read and understood on this subject is, if you delete an existing number from that textbox and tab away (leaving an empty string), the validation process will then take that empty string from the textbox and convert it to a null to save to the database (assuming your number field accepts nulls).  Once that null is saved, the binding source then re-reads that null from the database and attempts to display it back in the textbox - which is a string - and that is the key here.  When the system attempts to place that null back into the textbox (which is a string) it doesn't work - you cannot assign a null value to a string.  Therefore, you must implement the change as BobYang suggested here which tells the system (the binding source? the validation process?) to finally convert that null to an empty string in order to display it in the textbox.

    Sincerely,

    D. Kelley

    (BTW, sorry to propose and unpropose your reply as "The Answer" - the system told me I had already started a reply and wouldn't let me actually enter this reply after proposing your reply as the answer.  blah, blah, blah.  I first hit Reply - then Propose as Answer - then tried to hit Reply again ....)

    Thursday, October 25, 2012 5:06 PM