Solved

Help with SqlTypeException: SqlDateTime overflow Error

Posted on 2011-09-21
8
917 Views
Last Modified: 2012-06-21
I'm attempting to Update a row in my Database and I'm getting the following error:

 System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

I have 4 columns in the row I'm updating.  2 are Date and 2 are DateTime.  Using the debug option in Visual Studio I see the values being passed to the Date columns are:

#8/3/2011#

... and the values being passed to the DateTime columns is just the time.

#8:00:00 AM#

I'm guessing it might be the DateTime column but I'm not 100% sure.  Could someone tell me what I need to do in order to make the Update work.

My Update code is below if it helps.
Thanks!
Protected Sub btnUpdateRoom_Click(sender As Object, e As System.EventArgs) Handles btnUpdateRoom.Click

        ' Variables
        Dim roomName As String = Trim(Server.HtmlEncode(tbRoomName.Text))
        Dim roomSize As Integer = tbRoomSize.Text
        Dim dateStart As Date = tbStartDate.Text
        Dim dateEnd As Date = tbEndDate.Text
        Dim timeStart As DateTime = ddlStartTime.SelectedValue
        Dim timeEnd As DateTime = ddlEndTime.SelectedValue
        Dim location As String = Trim(Server.HtmlEncode(tbLocation.Text))
        Dim roomNotes As String = Trim(Server.HtmlEncode(tbNotes.Text))



        Try
            roomLogic.UpdateRoomDetails(RoomID, dateStart, dateEnd, timeStart, timeEnd, roomName, roomSize, location, roomNotes)

            lbUpdateStatus.Visible = True
            lbUpdateStatus.ForeColor = Drawing.Color.Green
            lbUpdateStatus.Text = "Update Successful!"
        Catch ex As Exception
            lbUpdateStatus.Visible = True
            lbUpdateStatus.ForeColor = Drawing.Color.Red
            lbUpdateStatus.Text = "WARNING: Update Failed. ERROR: " & ex.ToString
        End Try

    End Sub

Open in new window

0
Comment
Question by:cdemott33
  • 4
  • 3
8 Comments
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
What are the values in dateStart, dateEnd, timeStart and timeEnd. You give only 2 values in your explanation of the problem.

Could we see the code in UpdateRoomDetails?

What is the SQL code that is being executer?
0
 

Author Comment

by:cdemott33
Comment Utility
Hi James -

The other values are similar, but here they are so you have them

dateStart = #8/4/2011#
dateEnd = #8/5/2011#
timeStart = #8:00:00 AM#
timeEnd = #5:00:00 PM#

Also, my Update function is attached for your review.
Public Function UpdateRoomDetails(ByVal RoomID As Integer, _
                            ByVal DateStart As Nullable(Of Date), _
                            ByVal DateEnd As Nullable(Of Date), _
                            ByVal TimeStart As Nullable(Of DateTime), _
                            ByVal TimeEnd As Nullable(Of DateTime), _
                            ByVal RoomName As String, _
                            ByVal RoomSize As Nullable(Of Integer), _
                            ByVal Location As String, _
                            ByVal RoomNotes As String) As Boolean

        Dim dt As MeetingTrackerDAL.MeetingRoomDataTable = Adaptor.GetRoomDetails(RoomID)

        If dt.Count = 0 Then
            ' no matching record found, return false
            Return False
        End If

        Dim datarow As MeetingTrackerDAL.MeetingRoomRow = dt(0)

        If Not DateStart.HasValue Then datarow.SetdateStartNull() Else datarow.dateStart = DateStart
        If Not DateEnd.HasValue Then datarow.SetdateEndNull() Else datarow.dateEnd = DateEnd
        If Not TimeStart.HasValue Then datarow.SettimeStartNull() Else datarow.timeStart = TimeStart
        If Not TimeEnd.HasValue Then datarow.SettimeEndNull() Else datarow.timeEnd = TimeEnd
        If RoomName Is Nothing Then datarow.SetroomNameNull() Else datarow.roomName = RoomName
        If Not RoomSize.HasValue Then datarow.SetroomSizeNull() Else datarow.roomSize = RoomSize
        If Location Is Nothing Then datarow.SetlocationNull() Else datarow.location = Location
        If RoomNotes Is Nothing Then datarow.SetroomNameNull() Else datarow.roomNotes = RoomNotes
        datarow.creationDate = Now()

        ' Update the record
        Dim rowsAffected As Integer = Adaptor.Update(dt)

        ' Return true if precisely one row was updated, otherwise false
        Return rowsAffected = 1

    End Function

Open in new window

0
 

Author Comment

by:cdemott33
Comment Utility
Hey James-

Just and update.  Adding 1/1/1900 to the timeStart and timeEnd values seams to solve the problem.

        Dim timeStart As DateTime = "1/1/1900 " & ddlStartTime.SelectedValue
        Dim timeEnd As DateTime = "1/1/1900 " & ddlEndTime.SelectedValue

I'm sure there is a more code centric/elegant way of doing this.  Any suggestions?
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
The Update command does not affect only the row that you are dealing with. It affects all the rows that have been either modified, deleted or added in the DataTable. You problem might be in one of these rows. Maybe an empty row at the end of the table.

Then, we do not see the commands associated with the DataAdaptor. The problem could be there also.
I suggest that you trap the events to the DataAdapter. You can get those by declaring the DataAdapter at the module/form/class level this way:
Dim WithEvents Adaptor As New System.Data.SqlClient.SqlDataAdapter

Open in new window

You will then be able to trap the Adaptor events the same way you do with a control. The RowUpdating is probably the most interesting in your case. It is fired just before the command is sent to the database, and gives you the command:
Private Sub Adaptor_RowUpdating(sender As Object, e As System.Data.SqlClient.SqlRowUpdatingEventArgs) Handles Adaptor.RowUpdating
    Debug.WriteLine(e.Command.CommandText)
End Sub

Open in new window

Having the command would help in trying to understand why the dates do not pass.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
Comment Utility
I just saw something else in your code that did not catch my attention the first time, and I think the problem comes from a misunderstanding you have about dates and times in .NET. It didn't catch me first because in never have to deal only with time in my applications, always datetime... and maybe that is also what you should do.

You seem to think that there is a difference between a DateTime and a Date variable. They are exactly the same thing. Date is the name in VB, DataTime is the name in .NET. Both refers to the same type of variable. There is always a date and there is always a time, even in a Date variable. The time is simply set to midnight if you do not specify it.

The same way, DateTime always contains the Date. If you do not specify it, .NET enters 1/1/0001. 1/1/0001 is not appropriate for SQL Server. That is the root of your problem.

Your solution works because you are adding a valid date. But it might also be dangerous, because that 1/1/1900 date has nothing to do with your data. Some processing later on could choke on that date. Any application that use the database should be aware of that fact.

It now jumps at me in plain sight. You do not have to record the date and time separately. If they are related as they look to be, they should be recorded in the same variable, and saved as a unit in a datetime field in SQL Server, not as 2 separate fields. They are very easy to split later on if you want.

If you really need to keep them separate, then you need to be in SQL Server 2008 that now has 3 types of date fields : date, time, and the good old datetime. When you need only the date you use date. time for time. datetime for both. This way, you can prevent problems such as the one you had.

0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
I would suggest you store the time as a offset from midnight. For example 12:25AM can be stored as 25 and then you can convert it into actual time by AddMinutes function if the date.
0
 

Author Closing Comment

by:cdemott33
Comment Utility
Thanks again James.  Excellent feedback.  Also, you take the time to explain which is GREATLY appreciated.  Keep up the great work.
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
Yes, I like to take the time to explain. I think that a solution is useless otherwise.

And this is good practice for me. Most of my job for the last few years has been training. I give VB.NET training in French, which is my language (james burger is a translation of my real name). Explaining concepts is harder for me in English. If forces me to be more careful with my words, more precise in what I say. If I can say it in English, then it is a lot easier for me later to say it in French.

So, I consider explaining it to you as training myself for my job as a trainer.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 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

9 Experts available now in Live!

Get 1:1 Help Now