asked on

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

Daniel Van Der Werken

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.

-Dman100-

ASKER

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

ASKER CERTIFIED SOLUTION

Shaun Kline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dhanasekaran Sengodan

try this

if (terminationdate == null)
user.TerminationDate ="1/1/1900";
else
user.TerminationDate = terminationdate;

cyberkiwi

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

dukestaTAI

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

-Dman100-

ASKER

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!