SqlDateTime overflow error

I have an update method that is calling down into my data layer, which is a typed dataset.  When I attempt to update and leave one of the fields "terminationdate" empty in my gridview (it is a nullable field), I get the following error message:

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

See my method code below.

The field should accept a null value.

Thanks for any help.
[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Update, true)]
        public bool UpdateUser(string firstname, string lastname, string middleinitial, string phone, string email, string alternatephone, string ssn, string costcenter, int status, DateTime terminationdate, string notes, int userid)
        {
            TechCenter.UsersDataTable users = Adapter.GetUserByUserID(userid);

            if (users.Count == 0)
                return false;

            TechCenter.UsersRow user = users[0];

            user.FirstName = firstname;

            user.LastName = lastname;

            if (middleinitial == null)
                user.SetMiddleInitialNull();
            else
                user.MiddleInitial = middleinitial;

            if (phone == null)
                user.SetPhoneNull();
            else
                user.Phone = phone;

            if (email == null)
                user.SetEmailNull();
            else
                user.Email = email;

            if (alternatephone == null)
                user.SetAlternatePhoneNull();
            else
                user.AlternatePhone = alternatephone;

            if (ssn == null)
                user.SetSSNNull();
            else
                user.SSN = ssn;

            if (costcenter == null)
                user.SetCostCenterNull();
            else
                user.CostCenter = costcenter;

            user.Status = status.ToString();

            if (terminationdate == null)
                user.SetTerminationDateNull();
            else
                user.TerminationDate = terminationdate;

            if (notes == null)
                user.SetNotesNull();
            else
                user.Notes = notes;

            int rowsAffected = Adapter.Update(user);

            return rowsAffected == 1;

        }

Open in new window

-Dman100-Software ConsultantAsked:
Who is Participating?
 
Shaun KlineLead Software EngineerCommented:
DateTime variables do not have a null value. If you want to have a nullable DateTime variable, use the DateTime? data type.

You can then check if the variable is null by using the <variable>.HasValue property.
0
 
Daniel Van Der WerkenIndependent ConsultantCommented:
Are you setting the date to DateTime.MinValue somewhere in the code and then trying to write that out to the database?  That will cause a problem because DateTime.MinValue is 1/1/0001 (or something like that) and the minimum SQL datetime is 1/1/1753, so you'll get this error.

You need to ensure it's really either a real DB NULL or set it to 1/1/1753 instead of DateTime.MinValue.
0
 
-Dman100-Software ConsultantAuthor Commented:
The only place I am setting the value in code is in my update method:

if (terminationdate == null)
                user.SetTerminationDateNull();
            else
                user.TerminationDate = terminationdate;

In my gridview, I'm just leaving the termination field blank, which should branch into the if statement where terminationdate equals null and call SetTerminationDateNull().

SetTerminationDateNull() wouild be DB Null, correct
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Dhanasekaran SengodanCommented:
try this

if (terminationdate == null)
                user.TerminationDate ="1/1/1900";
            else
                user.TerminationDate = terminationdate;
0
 
cyberkiwiCommented:
Hi there,

The problem will be in SetTerminationDateNull() can you show it?
It should be something simple as user.TerminationDate = DbNull.Value instead of
user.TerminationDate = <xxx>

where <xxx> is out of the valid date range
0
 
dukestaTAICommented:
Read this : http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldatetime.minvalue.aspx

Namespace:  System.Data.SqlTypes
Assembly:  System.Data (in System.Data.dll)

Try using SqlDateTime.MinValue
0
 
-Dman100-Software ConsultantAuthor Commented:
Changing the terminationdate datatype to DateTime? for my parameter and then updating the lines where I set the value to the following:

if (terminationdate == null)
                user.SetTerminationDateNull();
            else
                user.TerminationDate = (DateTime)terminationdate;

Worked!

Thanks for the help!
0
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.