NULL Values for Numeric and date data type

  • I am currently using SQL Server 2008 and VB .Net as my front end. The issue that I am encountering could be simple to resolve but I have been struggling to find the correct answer. In my VB app I have several date and numeric fields that I am not able to get them to save at the DB level as NULLS. In my application I am using the datetime picker for the date fields the min value is '01/01/1753' so when I set the value to 'nothing' I get an exception that I am outside of the min or max range. What I have done is set the text property to an empty string and I am checking for the empty string and then set my business object property for the particular date to 'nothing', I still get an exception.

    bottom line I would like the DB column to end up NULL if the user does not enter a date.

    I have a similar issue with numeric values -- the DB column always ends up with a '0' (zero) I would like this to be NULL instead of zero if the user does not enter a value. Setting it to 'nothing' in the vb code does not create a NULL on the DB side.

    I am inserting and updating the rows with stored procedures -- If I need to put some code in the SP that would also work for me.

    I really DID NOT want to do some kludge and test for the 01/01/1753 or something along these lines.

    Can someone tell me what I am doing wrong?

    Thanks

    Tom

  • This is a client code problem. Show us the VB that writes the values to the database (executes the SP...).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I have attached 3 files:

    1 file is the code behind the form - VB Code in Form.txt

    1 file is the DataAdapter code

    The 3rd file is the businessobject class that is used to pass the data to the dataadapter class

    in the businessobject I have tried different ways to fix my problem but none of them work. The same goes for the code in the Form.

    Thanks

    Tom

  • I do not see any files.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Lets try this again

  • Can you validate that any of those values in the mFunerals(i) object are actually NULL? .NET seems to "hate" NULLs, so you tend to have to work extra hard to make it give you a NULL instead of, say, a 0 or "".

    Also - As I recall, the Parameter object has an optional flag to the efffect of "Pass a NULL if you encounter the empty string" which may need to be set.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • What version of VS is this?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • VS 2008

    When the data gets to the mFuneral the value is never Null the best that I can get it is: a time value of 12:00:00 and the date portion is null. When the query tries to process it seems to do a validation on the date and converts the date that is null to '0001-01-01' and it finds that it is not in the min range for a date value and causes an execption. The last thing I was going to try is set the date to something old like '1800-01-01' and in the SP check for this date and do a set to null. I really don't like this method but I have tried just about everything I can think of. The thing that I find funny is setting a date to null is a everyday NEED. If you are doing an order system you may not know a ship date so instead of putting some date into a column you would put a NULL date into that column.

    Let me know if you have another solution. By the way how do I set a null value to a numeric column. This is going to be the same problem I am having with the dates?

    Thanks

    Tom

  • OK, I would recommend that you rewrite the properties of your BusinessObject like so:

    Imports Microsoft.SqlServer

    Imports system.Data

    Public Class Funerals

    '...

    Private mFirstName As Data.SqlTypes.SqlString = Data.SqlTypes.SqlString.Null

    Public Property FirstName() As String

    Get

    If mFirstName.IsNull Then

    Return ""

    Else

    Return mFirstName.ToString

    End If

    End Get

    Set(ByVal value As String)

    If Not value Is Nothing Then

    mFirstName = value

    Else

    mFirstName = Data.SqlTypes.SqlString.Null

    End If

    End Set

    End Property

    Public ReadOnly Property FirstName_SQL() As SqlTypes.SqlString

    Get

    Return mFirstName

    End Get

    End Property

    Then have the dataAdapter class that calls the proc use the {property}_SQL methods in your BusinessObject.

    (edit: added vb formatting tag. Nice!)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung,

    Just curious why you don't use DBNull.Value? The other thing would be to set a default value in the SP to null as in:

    create procedure insertproc

    (

    @datetimevar datetime = null

    )

    and then in the business object leave that parameter off if it has the value that equates to null.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Jonathan Kehayias (1/7/2009)


    RBarryYoung,

    Just curious why you don't use DBNull.Value? The other thing would be to set a default value in the SP to null as in:

    create procedure insertproc

    (

    @datetimevar datetime = null

    )

    and then in the business object leave that parameter off if it has the value that equates to null.

    Jonthan: The BusinessObject does not execute the command in the provided code, the DataAdapter does, processing the propeties of the BusinessObject. I didn't want to have to change both for the OP (confusing and error-prone for me to do that) so I just stuck with the BusinessObject changes.

    When I tried to assign DBNull.Value to the SQLString private property, I got the error:Value of type 'System.DBNull' cannot be converted to 'System.Data.SqlTypes.SqlString'.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Something happened to your reply. All I see is a quote of my post.

    EDIT:

    Nevermind I see it now. I understand what you were after now.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Sorry, hit "Post" accidentally. fixed now.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thanks I will try your suggestions for the BO

    As far as the SP goes, I currently code my SP to have the parameters set to null. What this does for me is the following. If I do not pass a parameter into the SP it will skip that parameter, but it will not insert or update the column to NULLS.

    I am hoping that the code you supplied for the BO will take care of my NULLS, but if not I am going to test the values in the SP and then set them to NULL there. I will set the dates to 1800-01-01 and the numerics to 999999999.99. for money columns and 999999999 for integer columns and test for these values.

    I would think Microsoft would provide a function in VB .Net to set fields to NULL and be done with it.

    Thank you for all your assistance

    Tom

  • FYI, don't forget, you will need to change the DataAdapter to use the *_SQL methods of the BO.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply