Solved

Help with SqlTypeException: SqlDateTime overflow Error

Posted on 2011-09-21
8
1,002 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
[X]
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
8 Comments
 
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?
0
 

Author Comment

by:cdemott33
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

0
 

Author Comment

by:cdemott33
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?
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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
    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
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 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.

0
 
LVL 83

Expert Comment

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

Author Closing Comment

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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

696 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