Making datetime field null, SQL

aqzman_

Hey guys, I have a question that I hope isn't too hard to get an anwser too, although even after all the Googling, and searching Code Project I've done I haven't had much luck yet. What I'm trying to do is make a Datetime field in a SQL database null (it's already set to allow nulls), but no matter how many different ways I try I either get an error or the default lowest time is just filled in (1900/01/01 12:00:00 AM). A time being there relys on the users input, so in some cases it might need to be filled in, in other cases it doesn't. Anyway, here is my code, if anymore is needed just ask!

    Dim DateClosed As Date
    State = (ddlState.SelectedItem.Value)
    If State = 0 Then
        DateClosed = CDate(lstYear2.SelectedValue & "/" & lstMonth2.SelectedValue & "/" & lstDay2.SelectedValue)
        Time = " " & ddlHour2.SelectedItem.Value + ":" + ddlMin2.SelectedItem.Value + ddlAMPM2.SelectedItem.Value
        DateClosed += Time
    Else
        DateClosed = Nothing
    End If

Just incase anyone needs to know, State is a drop down box with two selections. Open or closed. Open = 1 and Closed = 0. Thanks, aqzman

Dave Kreskowiak

Well, this code doesn't have anything to do with SQL. This only applies to VB.NET 2005 (.NET Framework 2.0) and above! It will NOT work with the .NET Framework 1.1 and below! But, Date, which is just an alias for the DateTime type, normally cannot be Nothing, or null in C#. You'd have to declare it a special way to get it to work.

Dim myDate As New Nullable(Of DateTime)
myDate = New Date(2007, 7, 7)
If myDate.HasValue Then
    ' whatever you want if there is a value
Else
    ' myDate is Nothing, so...
End If

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007

aqzman_

Hi, Thanks a lot for the reply! I've actually tried the As New Nullable(Of DateTime) code before, but there's a problem with that in other parts of my code. One part is where I use a '+' symbol it says "Operator '+' is not defined for types 'System.Nullable(Of Date)'" and it also causes an error on my SQL insert statement which is "Operator '&' is not defined for types 'String' and 'System.Nullable(Of Date)'." I'm fairly new to VB.NET, SQL, and ASP.NET but those things seem to be almost totally unrelated as far as a nullable variable goes, guess not. Oh, and just to let you know, I am using the .NET 2.0 framework. Thanks again for the reply, aqzman

Dave Kreskowiak

aqzman_ wrote:

I've actually tried the As New Nullable(Of DateTime) code before, but there's a problem with that in other parts of my code. One part is where I use a '+' symbol it says "Operator '+' is not defined for types 'System.Nullable(Of Date)'" and it also causes an error on my SQL insert statement which is "Operator '&' is not defined for types 'String' and 'System.Nullable(Of Date)'."

That's because you have to get the value out of the Nullable type before you can use it.

Dim myDate As Nullable(Of DateTime)
myDate = New Date(2007, 7, 7)

' myDate.Value returns a Date object, IF there is a value...
If myDate.HasValue Then
    Debug.WriteLine("DATE: " & **myDate.Value**.ToShortDateString())
Else
    Debug.WriteLine("DATE: Nothing")
End If

-- modified at 13:46 Monday 16th July, 2007

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007

aqzman_

Thanks a lot for your help! I really appreciate it.