Help with SqlTypeException: SqlDateTime overflow Error

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:


... 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.
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))

            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

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Jacques Bourgeois (James Burger)Connect With a Mentor PresidentCommented:
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.

Jacques Bourgeois (James Burger)PresidentCommented:
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?
cdemott33Author Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

cdemott33Author Commented:
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?
Jacques Bourgeois (James Burger)PresidentCommented:
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
End Sub

Open in new window

Having the command would help in trying to understand why the dates do not pass.
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.
cdemott33Author Commented:
Thanks again James.  Excellent feedback.  Also, you take the time to explain which is GREATLY appreciated.  Keep up the great work.
Jacques Bourgeois (James Burger)PresidentCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.