none
Null and SQL Server 2005 RRS feed

  • Question

  • This might be the wrong forum for this question, but I wasnt 100% which one it should be in.

     

    Basically, I'm writing a VB.NET database application which hooks up to an SQL 2005 server. I'm having a problem with nulls - when I try to assign a value of a field which is null to field on a VB.NET form, I get the error "Conversion from type 'DBNull' to type 'String' is not valid."

     

    Now, I know whats causing this, and have read up on how to get around it (check the field is null before doing anything with it). This to me seems a bit combersom - a lot of extra effect which I will have to maintain right throughout the system.

     

    Much easier I think, would be to just set up a default value for all of the fields in my database (blank for strings, zero for numbers), that way I wont have any nulls in my database at all (with perhaps the exception of dates).

     

    What I want to know is, is there a downside to this? (besides that extra space the non-nulls take up).

    Friday, August 3, 2007 11:07 AM

All replies

  • Yeah, you lose something pretty major. Say you've got an int column, and the value is zero. You won't be able to tell if it's zero because it was set to zero or because it's just defaulted to zero. If it's zero when it's set to zero and null when it hasn't been set to anything then you can differentiate between them.

    You may think you'll never want to differentiate between those two cases, but chances are you will.

    Here's another thread on the topic of nulls in DataSets, you may find it interesting.

    Regards,

    Sean
    Friday, August 3, 2007 12:01 PM
  • What you are talking about can cause data integrity issues most Nullable types issues just run searches on Google and you will find existing code for it.  The first links is C# so use the code converter to convert the code to VB.  This will get you started in the right direction, the thread you are directed most of the posters don't know ANSI SQL NULL which means unknown and not empty string. 


    http://www.codeproject.com/cs/database/NullableReaders.asp

    http://www.c-sharpcorner.com/UploadFile/sd_patel/EnterNullValuesForDateTime11222005015742AM/EnterNullValuesForDateTime.aspx

     

    http://www.carlosag.net/Tools/CodeTranslator/Default.aspx

     

    Friday, August 3, 2007 4:57 PM
  • OK - maybe I should have been more specific about where I'm having the problem.

    I'm not using a datareader - I'm using a dataset and dataadapter. I'm making a form to view records, where I am manually reading and writing values back to the dataset, before updating to the database, with code such as:

    Code Snippet

    txtCode.Text = dsStock.Tables("Stock").Rows(inc).Item(0)


    to populate the txtbox, and
    Code Snippet

    dsStock.Tables("Stock").Rows(inc).Item(0) = txtCode.Text

    to update the dataset.

    The problem I'm having, is that the first example up there will error if the field in the record is null. To get around this, I need to use something like this:
    Code Snippet

            If dsStock.Tables("Stock").Rows(inc).Item("Brand_Code") Is DBNull.Value Then
                txtBrandCode.Text = String.Empty
            Else
                txtBrandCode.Text = dsStock.Tables("Stock").Rows(inc).Item("Brand_Code")
            End If


    Which seems to be a lot of extra hassle, and will ultimatly update the database with a blank string rather then the original null anyway.

    So basically, I'm looking for an easy to to display/update records, without nulls tripping me up.
    Friday, August 3, 2007 10:08 PM
  • These cover what you need but you will still need the C-Sharpconner code because it covers how to pass the NULL with a textbox.  In .NET 2.0 run a search of ObjectDataSource it does two way data binding.  What I mean is 2.0 tools deals with DBNULL better that DataSet and DataAdapter.


    http://msdn2.microsoft.com/en-us/library/6d1wk41s(VS.71).aspx

    http://msdn2.microsoft.com/en-us/library/33y2221y(vs.71).aspx

     

    Friday, August 3, 2007 10:47 PM
  • OK. I had a look at thte C-Sharepcorner code - I tried this (changing it a little, as it was coding for a datetime field, where I am doing it for nvarchar). As far as I can see, it is saying something along the lines of "If the field is blank, then assign null to the field in the database".

    This seems to contradict what Sean Fowler said (the first reply), in that nulls are important, as they tell you if the field is blank, or if it has been intentionally made blank by the user. With this code, it seems if the user changes the value to <blank>, then the underlying database field will be set to null. (I tried to confirm this, and yes, it sets field which previously were non-null to null).

    Correct me if I'm wrong here, but that would defeat the purpose of having a null, as we no longer know if the field is null because it was never assigned a value, or if the user specifically wanted it to be empty.


    Saturday, August 4, 2007 4:20 AM
  • Depending on the controls you use it can all get a bit messy when you have the data maintained by a UI (rather than being system-generated).

    For example the standard DatePicker control can't handle nulls, so you end up with DateTime.MinValue getting put in the database, which is plain nasty. I use the Infragistics DatePicker instead as it can handle nulls. Their UltraTextEditor control can also handle null values. Another option is to have a CheckBox next to the standard DatePicker, and checking it enables the DatePicker. Then if it's unchecked the value is null, otherwise it's the value of the DatePicker.

    You can use the latter method for other controls too, including TextBoxes, but it's not particularly user-friendly and requires some extra coding (although you could make a generic user control) I'd only use it for TextBoxes when I particularly needed to be able to differentiate between "not entered" and "blank".

    In my experience it's generally not necessary to differentiate for strings, but for dates I really don't want DateTime.MinValue going in the database, and for integral values I generally don't want zero going in the database unless the user has specifically entered it. For example it may be a requirement that the price doesn't have to entered when an order record is first created. If zero is always put into the database how do you know if it's a freebie or just hasn't been entered yet?

    You'll encounter this problem wherever your default value is a value that the user may also validly enter. So for example it may never be valid that OrderQty is zero, in which case having zero in there would mean that it just hasn't been entered yet, and you don't really have a problem. However for the Price column zero could be a valid amount, and you have the problem.

    This is an impedance mis-match between the database and the UI. With DataSets you also have an impedance mis-match between DataSets and the database, largely because they don't yet use the nullable types. The latter you can get rid of by using normal (preferably generated) objects rather than DataSets, the former is tricker.

    There's a free trial of the Infragistics controls, and I'm sure other 3rd-party control must have similar functionality. I don't know how well they tie in with DataSets.

    I wish I could give you a nice simple solution; the 3rd-party controls route isn't bad but they aren't particularly cheap.

    Regards,

    Sean

    Edit: Actually I think that DateTime.MinValue is outside the range of valid dates in Sql, so you'd get an exception if you tried to put that into the database. I haven't checked this though.

    Saturday, August 4, 2007 10:50 AM
  • > Which seems to be a lot of extra hassle, and will ultimatly update the database with a blank string rather
    > then the original null anyway.


    What's a lot of extra hassle is writing any of that code at all.

    Use a BindingSource.  Add a BindingSource to your form, choose your DataSet as its DataSource, and your table as its DataMember.  Drop a BindingNavigator to your form and set its BindingSource.

    Now create a TextBox for each of the columns in your table.  Bind the Text property of each TextBox to the column in the BindingSource.  You're done.

    You now have a form that will let you navigate through your DataTable and that will allow you to add and modify rows.  All of the null handling issues are taken care of for you by the BindingSource.  If you add a new row, the nullable columns in that row will be set to DBNull (unless you've set a default in the DataTable).  If you modify a row, columns whose value before the edit was DBNull will remain DBNull unless you change them.

    The only code you absolutely need to write is the code that calls the TableAdapter's Update method; if you don't do this, none of your changes will get written back to the database.

    The BindingSource is not without its problems.  You'll run into issues if you start manipulating the DataTable programmatically while binding is active, for instance.  It doesn't make issues like the one Sean pointed out with the DateTimePicker go away.  Before long, you'll want to bind a ComboBox to a column that's a foreign key to a lookup table, and you'll discover the joy of controls that are bound to two different BindingSources.  But for all that, it frees you from actually having to touch the DataTable in your UI code.  And that's worth a lot.



    Sunday, August 5, 2007 5:00 AM