mkarkoukli
asked on
VB.net / MS SQL Null Date question
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
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
=======================
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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".
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".
ASKER
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.
ASKER
I tried the following as you recommended and received the following error:
If Not IsDBNull(NewTicket.DateClo
updateCommand.Parameters.A
End If
Must declare teh scalar variable "@NewCloseDate".
If Not IsDBNull(NewTicket.DateClo
updateCommand.Parameters.A
End If
Must declare teh scalar variable "@NewCloseDate".
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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/
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#
The minimum from SmallDateTime datatype is January 1, 1900
Check this article from MSDN
http://msdn.microsoft.com/
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#
ASKER