Solved / MS SQL Null Date question

Posted on 2009-04-02
Last Modified: 2013-11-27
I'm trying to update an existing record in MS SQL Database, My program checks for access concurrency to make sure records are not updated by someone else. However I'm been having a difficult time dealing with null date and I've been getting the following error:

sqldate time overflow. must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I will attach the code
Public Shared Function UpdateTicket(ByVal OldTicket As Tickets, ByVal NewTicket As Tickets) As Boolean
        Dim connection As SqlConnection = DBConnection.GetConnection(True)
        Dim updateStatement As String _
            = "UPDATE tblTicketsQueue SET " _
                & "TicketPriority = @NewTicketPriority, " _
                & "TicketType = @NewTicketType, " _
                & "TicketNumber = @NewTicketNumber, " _
                & "fkUser = @NewfkUser, " _
                & "fkCategory = @NewfkCategory, " _
                & "fkProduct = @NewfkProduct, " _
                & "fkStatus = @NewfkStatus, " _
                & "TicketDescription = @NewTicketDescription, " _
                & "CloseDate = @NewCloseDate,DateTime, " _
                & "AssignedTo = @NewAssignedTo, " _
                & "MarkForDeletion = @NewMarkForDeletion " _
            & "WHERE pkTicketID = @OldTicketID " _
                & "AND TicketPriority = @OldTicketPriority " _
                & "AND TicketType = @OldTicketType " _
                & "AND TicketNumber = @OldTicketNumber " _
                & "AND fkUser = @OldfkUser " _
                & "AND fkCategory = @OldfkCategory " _
                & "AND fkProduct = @OldfkProduct " _
                & "AND fkStatus = @OldfkStatus " _
                & "AND TicketDescription = @OldTicketDescription " _
                & "AND CloseDate = @OldCloseDate " _
                & "AND AssignedTo = @OldAssignedTo " _
                & "AND MarkForDeletion = @OldMarkForDeletion"
        Dim updateCommand As New SqlCommand(updateStatement, connection)
        'updateCommand.Parameters.AddWithValue("@NewDateEntered", OldTicket.DateEntered)
        updateCommand.Parameters.AddWithValue("@NewTicketPriority", NewTicket.Priority)
        updateCommand.Parameters.AddWithValue("@NewTicketType", NewTicket.TicketType)
        updateCommand.Parameters.AddWithValue("@NewTicketNumber", OldTicket.TicketNumber)
        updateCommand.Parameters.AddWithValue("@NewfkUser", NewTicket.User)
        updateCommand.Parameters.AddWithValue("@NewfkCategory", NewTicket.Category)
        updateCommand.Parameters.AddWithValue("@NewfkProduct", NewTicket.Product)
        updateCommand.Parameters.AddWithValue("@NewfkStatus", NewTicket.Status)
        updateCommand.Parameters.AddWithValue("@NewTicketDescription", NewTicket.TicketDescription)
        Dim N_NewDateClosed As Nullable(Of DateTime)
        N_NewDateClosed = NewTicket.DateClosed
        If Not IsDBNull(N_NewDateClosed) Then
            updateCommand.Parameters.AddWithValue("@NewCloseDate", CType(NewTicket.DateClosed, DateTime))
        End If
        Dim N_NewAssignedTo As Nullable(Of Long)
        N_NewAssignedTo = NewTicket.AssignedTo
        If N_NewAssignedTo = 0 Then
            updateCommand.Parameters.AddWithValue("@NewAssignedTo", 0)
            updateCommand.Parameters.AddWithValue("@NewAssignedTo", NewTicket.AssignedTo)
        End If
        Dim N_NewMarkedForDeletion As Nullable(Of Long)
        N_NewMarkedForDeletion = NewTicket.MarkForDeletion
        If N_NewMarkedForDeletion = 0 Then
            updateCommand.Parameters.AddWithValue("@NewMarkForDeletion", 0)
            updateCommand.Parameters.AddWithValue("@NewMarkForDeletion", NewTicket.MarkForDeletion)
        End If
        updateCommand.Parameters.AddWithValue("@OldTicketID", OldTicket.TicketId)
        updateCommand.Parameters.AddWithValue("@OldDateEntered", OldTicket.DateEntered)
        updateCommand.Parameters.AddWithValue("@OldTicketPriority", OldTicket.Priority)
        updateCommand.Parameters.AddWithValue("@OldTicketType", OldTicket.TicketType)
        updateCommand.Parameters.AddWithValue("@OldTicketNumber", OldTicket.TicketNumber)
        updateCommand.Parameters.AddWithValue("@OldfkUser", OldTicket.User)
        updateCommand.Parameters.AddWithValue("@OldfkCategory", OldTicket.Category)
        updateCommand.Parameters.AddWithValue("@OldfkProduct", OldTicket.Product)
        updateCommand.Parameters.AddWithValue("@OldfkStatus", OldTicket.Status)
        updateCommand.Parameters.AddWithValue("@OldTicketDescription", OldTicket.TicketDescription)
        Dim N_OldDateClosed As Nullable(Of DateTime)
        N_OldDateClosed = OldTicket.DateClosed
        If Not IsDBNull(N_OldDateClosed) Then
            updateCommand.Parameters.AddWithValue("@OldCloseDate", CType(OldTicket.DateClosed, DateTime))
        End If
        Dim N_OldAssingedTo As Nullable(Of Long)
        N_OldAssingedTo = OldTicket.AssignedTo
        If N_OldAssingedTo = 0 Then
            updateCommand.Parameters.AddWithValue("@OldAssignedTo", 0)
            updateCommand.Parameters.AddWithValue("@OldAssignedTo", OldTicket.AssignedTo)
        End If
        Dim N_OldMarkForDeletion As Nullable(Of Long)
        N_OldMarkForDeletion = OldTicket.MarkForDeletion
        If N_OldMarkForDeletion = 0 Then
            updateCommand.Parameters.AddWithValue("@OldMarkForDeletion", 0)
            updateCommand.Parameters.AddWithValue("@OldMarkForDeletion", OldTicket.MarkForDeletion)
        End If
            Dim count As Integer = updateCommand.ExecuteNonQuery
            If count > 0 Then
                Return True
                Return False
            End If
        Catch ex As SqlException
            Throw ex
        End Try
    End Function

Open in new window

Question by:mkarkoukli
  • 4
  • 4
  • 2
LVL 48

Accepted Solution

jpaulino earned 300 total points
ID: 24052840
After the:
 N_NewDateClosed = NewTicket.DateClosed
What is the result of  NewDateClosed  ? Is it Nothing ?

Author Comment

ID: 24052876
Date closed is coming as 12:00:00 AM. When I view the table it shows the word NULL
LVL 48

Assisted Solution

jpaulino earned 300 total points
ID: 24052936
But you have this
& "CloseDate = @NewCloseDate,DateTime, " _

Change to this
& "CloseDate = @NewCloseDate, " _
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 200 total points
ID: 24052944
can you try this:

        If Not IsDBNull(NewTicket.DateClosed) andalso NewTicket.DateClosed <> date.minvalue Then
            updateCommand.Parameters.AddWithValue("@NewCloseDate", CType(NewTicket.DateClosed, DateTime))
        End If
LVL 48

Assisted Solution

jpaulino earned 300 total points
ID: 24052954
Isn't pkTicketID and unique ID ? It should be enough for the clause WHERE

Author Comment

ID: 24053025
Thank you, that was a typo from trying different ways.. I changed it but I continue to get the same error.
Do you recommend using specific data type in the SQL table for date? It seems that what I conceive I have in the table is something different from what what actually stored in the table. In other words, when I compare the data it does not match. When I was checking for a NULL value in the date field, although it was actually showing a NULL in the table my code (in the debug) shows "12:00:00 AM".

Author Comment

ID: 24053043
pkTicketID is a unique ID, however since i'm dealing with disconnected database model, if during editing a record someone else made changes, I want to prevent the user from overwriting the changes by warning them.

Author Comment

ID: 24053157
I tried the following as you recommended and received the following error:

If Not IsDBNull(NewTicket.DateClosed) andalso NewTicket.DateClosed <> date.minvalue Then
            updateCommand.Parameters.AddWithValue("@NewCloseDate", CType(NewTicket.DateClosed, DateTime))
        End If

Must declare teh scalar variable "@NewCloseDate".
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 200 total points
ID: 24053192
so do this:

If Not IsDBNull(NewTicket.DateClosed) andalso NewTicket.DateClosed <> date.minvalue Then
            updateCommand.Parameters.AddWithValue("@NewCloseDate", CType(NewTicket.DateClosed, DateTime))
            updateCommand.Parameters.AddWithValue("@NewCloseDate", dbnull.value)
        End If
LVL 48

Expert Comment

ID: 24053949
I don't have 100% sure but you cannot insert only time in a smalldatetime field. SQL also stores the date and the minimum date for SQL is different then minimum date in .NET Framework.
The minimum from SmallDateTime datatype is January 1, 1900
Check this article from MSDN
If you want to insert only the time you could use a dummy date and then your time, like #1/1/1900 12:01:00 PM#  

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Data Tools for Visual Studio 2015 - backward compatibility. 2 115 2010 11 36
Modal Popup Extender control 1 22
Expression Evaluater 3 27
What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question