Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Help with SqlTypeException: SqlDateTime overflow Error

Posted on 2011-09-21
Medium Priority
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:


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

Question by:cdemott33
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 40
ID: 36575097
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?

Author Comment

ID: 36575594
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


Author Comment

ID: 36575710
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?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 40
ID: 36575755
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.
LVL 40

Accepted Solution

Jacques Bourgeois (James Burger) earned 2000 total points
ID: 36575992
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.

LVL 83

Expert Comment

ID: 36576161
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.

Author Closing Comment

ID: 36581359
Thanks again James.  Excellent feedback.  Also, you take the time to explain which is GREATLY appreciated.  Keep up the great work.
LVL 40
ID: 36581473
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.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

636 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