Solved

VB.net / MS SQL Null Date question

Posted on 2009-04-02
10
1,052 Views
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)

        Else

            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)

        Else

            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)

        Else

            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)

        Else

            updateCommand.Parameters.AddWithValue("@OldMarkForDeletion", OldTicket.MarkForDeletion)

        End If
 

        Try

            connection.Open()

            Dim count As Integer = updateCommand.ExecuteNonQuery

            If count > 0 Then

                Return True

            Else

                Return False

            End If

        Catch ex As SqlException

            Throw ex

        Finally

            connection.Close()

        End Try

    End Function
 

=======================

Open in new window

4-2-2009-10-57-10-AM.png
0
Comment
Question by:mkarkoukli
  • 4
  • 4
  • 2
10 Comments
 
LVL 48

Accepted Solution

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

Author Comment

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

Assisted Solution

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

Change to this
& "CloseDate = @NewCloseDate, " _
 
0
 
LVL 69

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
0
 
LVL 48

Assisted Solution

by:jpaulino
jpaulino earned 300 total points
ID: 24052954
Isn't pkTicketID and unique ID ? It should be enough for the clause WHERE
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:mkarkoukli
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".
0
 

Author Comment

by:mkarkoukli
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.
0
 

Author Comment

by:mkarkoukli
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".
0
 
LVL 69

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))
else
            updateCommand.Parameters.AddWithValue("@NewCloseDate", dbnull.value)
        End If
0
 
LVL 48

Expert Comment

by:jpaulino
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
http://msdn.microsoft.com/en-us/library/aa258277(SQL.80).aspx
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#  
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
With most software applications trying to cater to multiple user needs nowadays, the focus is to make them as configurable as possible. For e.g., when creating Silverlight applications which will connect to WCF services, the service end point usuall…
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. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now