SqlDateTime overflow error

Question

User1052662409 posted

Hi All,

When I edit my model using razor, I can see my textboxes with values. See below

But when I try to update by clicking submit button it shows the error.

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

I am wondering when it has value in textboxes why it gets null on click.

Fo reference I am pasting my razor code for textboxes

  <div class="form-group">
                            @Html.LabelFor(model => model.DateOfJoining, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.TextBoxFor(model => model.DateOfJoining,  new { @class = "form-control", @type = "date", @Value = Model.DateOfJoining.ToString("yyyy-MM-dd") })
                              
                                @Html.ValidationMessageFor(model => model.DateOfJoining, "", new { @class = "text-danger" })
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.DateOfEnd, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.TextBoxFor(model => model.DateOfEnd, new { @class = "form-control", @type = "date", @Value = Model.DateOfEnd.ToString("yyyy-MM-dd") })
                                
                                @Html.ValidationMessageFor(model => model.DateOfEnd, "", new { @class = "text-danger" })

                            </div>
                        </div>

Please suggest.

Answer 1

User-194957375 posted

look in fiddler what data the post \ get form request sends.

Answer 2

User475983607 posted

demoninside9

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

I am wondering when it has value in textboxes why it gets null on click.

Most likely the model property names do not match the input names.  Unfortunately, you did not share the most important bits of code which is the model, action, and entire view. 

Share all the relevant code if you want the community to debug your code.  Otherwise, take a moment to do basic troubleshooting.

Answer 3

User1052662409 posted

Unfortunately, you did not share the most important bits of code which is the model, action, and entire view. 

Model

public class UserAdministratorModel
    {
        public long id { get; set; }
        public DateTime CreatedOn { get; set; }
        public DateTime LastUpdateOn { get; set; }
        [Required]
        [Display(Name = "manage_project_status", ResourceType = typeof(Resource))]
        public bool Status { get; set; }
        [Required]
        [Display(Name = "a_e_subadm_band", ResourceType = typeof(Resource))]
        public int BandId { get; set; }
        [Required]
        [Display(Name = "a_e_email_teamname", ResourceType = typeof(Resource))] 
        public int TeamId { get; set; }
        [Required]
        [Display(Name = "a_e_subadm_fname", ResourceType = typeof(Resource))]
        public string FisrtName { get; set; }
        [Required]
        [Display(Name = "a_e_subadm_lname", ResourceType = typeof(Resource))]
        public string LastName { get; set; }
        [Required]
        [Display(Name = "manage_email_email", ResourceType = typeof(Resource))]
        public string Email { get; set; }
        //[Required]
        [Display(Name = "a_e_subadm_mobile", ResourceType = typeof(Resource))]
        public string Mobile { get; set; }
        [Required]
        [Display(Name = "a_e_subadm_username", ResourceType = typeof(Resource))]
        public string UserName { get; set; }
        [Required]
        [Display(Name = "a_e_smtp_password", ResourceType = typeof(Resource))]
        public string Password { get; set; }
        [Display(Name = "a_e_subadm_confirm", ResourceType = typeof(Resource))]
       // [Compare("Password", ErrorMessage = "Password and Confirmation Password must match.")]
        public string ConfirmPassword { get; set; }
        public string AdminType { get; set; }
        public DateTime LastLoginDate { get; set; }
       // [Required]
        [Display(Name = "a_e_subadm_proassgn", ResourceType = typeof(Resource))]
        public string ProjectNumber { get; set; }
        public string ProjectName { get; set; }
        [Required]
        [Display(Name = "a_e_subadm_sapno", ResourceType = typeof(Resource))]
        public string SAPNo { get; set; }
      //  [Required]
        [Display(Name = "a_e_subadm_superior", ResourceType = typeof(Resource))]
        public long Supervisor { get; set; }
        [Required]
        [DataType(DataType.Date)]
        [Display(Name = "a_e_subadm_doj", ResourceType = typeof(Resource))]
        public DateTime DateOfJoining { get; set; }
        [Required]
        [DataType(DataType.Date)]
        [Display(Name = "a_e_subadm_dol", ResourceType = typeof(Resource))]
        public DateTime DateOfEnd { get; set; }
        public float JoinYear { get; set; }
        public float EndYear { get; set; }
        public float JoinMinus { get; set; }
        public float EndMinus { get; set; }
        public bool ChangePassword { get; set; }
        public DateTime DateAdded { get; set; }
        public DateTime ModifyDate { get; set; }
        public long AddedBy { get; set; }
        public long UpdatedBy { get; set; }
        //[Required]
        [Display(Name = "a_e_subadm_optsuperior", ResourceType = typeof(Resource))]
        public long SecondSupervisor { get; set; }
        [Required]
        [Display(Name = "a_e_subadm_userrol", ResourceType = typeof(Resource))]
        public string UserRole { get; set; }
        [Required]
        [Display(Name = "a_e_subadm_country", ResourceType = typeof(Resource))]
        public long CountryId { get; set; }
       // [Required]
        [Display(Name = "a_e_subadm_skypid", ResourceType = typeof(Resource))]
        public string SkypeId { get; set; }
      //  [Required]
        [Display(Name = "a_e_subadm_uplodphoto", ResourceType = typeof(Resource))]
        public string Photo { get; set; }
        [Required]
        [Display(Name = "a_e_subadm_voipno", ResourceType = typeof(Resource))]
        public string Voip { get; set; }
        public string Extension { get; set; }
        [Required]
        [Display(Name = "a_e_subadm_telno", ResourceType = typeof(Resource))]
        public string TelePhoneNumber { get; set; }
        [Required]
        [Display(Name = "a_e_subadm_optsuperior", ResourceType = typeof(Resource))]
        public DateTime LastUpdatedpassword { get; set; }
        [Required]
        [Display(Name = "a_e_cuntry_salutation", ResourceType = typeof(Resource))]
        public string Salutation { get; set; }
       // [Required]
        [Display(Name = "a_e_cuntry_gender", ResourceType = typeof(Resource))]
        public string Gender { get; set; }
        public long CarryYear { get; set; }
        public long CarryLoseYear { get; set; }
        public string PasswordLinkGen { get; set; }

        public DateTime PasswordLinkDate { get; set; }
        public string EmpType { get; set; }
        public DateTime AccountLockDate { get; set; }


        #region For Extra Customised Fields
        public string ProjectFullString { get; set; }
        public string CountryCode { get; set; }
        public string Prefix { get; set; } 
        #endregion






    }

@model CP.Models.UserAdministratorModel
@if (Session["MenuMaster"] != null)
{
    List<CP.Models.Language_List> lang = (List<CP.Models.Language_List>)Session["LanguageList"];
    var MenuMaster = (List<CP.Models.Branches>)Session["menu_branches"];


    @Html.Partial("Menu", lang);
    @Html.Partial("Left_Panel", MenuMaster);
}
@using (Html.BeginForm("Edit", "UserAdministrator", FormMethod.Post))
{
    <script language="JavaScript" type="text/javascript" src="/../Scripts/functions.js"></script>
    <div class="wrapper-content" style="margin-left: 272px;">
        <div class="row">
            <div class="col-lg-12">

                <div class="widgets-container">

                    <div class="form-horizontal m-t-xs">
                        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
                        <div class="form-group">
                            @Html.LabelFor(model => model.TeamId, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.DropDownListFor(model => model.TeamId, new SelectList(ViewBag.Roles, "Value", "Text"), "Select", new { @id = "ddlTeam", @class = "form-control" })
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.BandId, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.DropDownListFor(model => model.BandId, new SelectList(ViewBag.Band, "Value", "Text"),  new { @id = "ddlBand", @class = "form-control" })
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.Salutation, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.DropDownListFor(model => model.Salutation, new SelectList(ViewBag.Salutation, "Value", "Text"),  new { @id = "ddlSalutation", @class = "form-control" })
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.FisrtName, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.EditorFor(model => model.FisrtName, new { htmlAttributes = new { @class = "form-control" } })
                                @Html.ValidationMessageFor(model => model.FisrtName, "", new { @class = "text-danger" })

                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
                                @Html.ValidationMessageFor(model => model.LastName, "", new { @class = "text-danger" })

                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.Email, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.EditorFor(model => model.Email, new { htmlAttributes = new { @class = "form-control" } })
                                @Html.ValidationMessageFor(model => model.Email, "", new { @class = "text-danger" })

                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.UserName, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.EditorFor(model => model.UserName, new { htmlAttributes = new { @class = "form-control", @readonly = "readonly" } })
                                @Html.ValidationMessageFor(model => model.UserName, "", new { @class = "text-danger" })
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.Password, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.TextBoxFor(model => model.Password, new { @class = "form-control" , @type = "password" })
                                @Html.ValidationMessageFor(model => model.Password, "", new { @class = "text-danger" })
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.ConfirmPassword, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.TextBoxFor(model => model.Password, new { @class = "form-control",@type="password" })
                                @Html.ValidationMessageFor(model => model.Password, "", new { @class = "text-danger" })
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.ProjectName, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.TextBoxFor(model => model.ProjectFullString, null, new { @class = "form-control", id= "txtProject" })
                                @Html.ValidationMessageFor(model => model.ProjectName, "", new { @class = "text-danger" })
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.Supervisor, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                
                                @Html.DropDownListFor(model => model.Supervisor, new SelectList(ViewBag.Supervisors, "Value", "Text"), "Select", new { @id = "ddlSupervisor", @class = "form-control" })

                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.SecondSupervisor, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.DropDownListFor(model => model.SecondSupervisor, new SelectList(ViewBag.Supervisors, "Value", "Text"), "Select", new { @id = "ddlDeputySupervisor", @class = "form-control" })
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.DateOfJoining, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.TextBoxFor(model => model.DateOfJoining,  new { @class = "form-control", @type = "date", @Value = Model.DateOfJoining.ToString("yyyy-MM-dd") })
                                @*@Html.EditorFor(model => model.DateOfJoining, new { htmlAttributes = new { @class = "form-control", @type = "date" } })*@
                                @Html.ValidationMessageFor(model => model.DateOfJoining, "", new { @class = "text-danger" })
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.DateOfEnd, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.TextBoxFor(model => model.DateOfEnd, new { @class = "form-control", @type = "date", @Value = Model.DateOfEnd.ToString("yyyy-MM-dd") })
                                @*@Html.EditorFor(model => model.DateOfEnd, new { htmlAttributes = new { @class = "form-control", @type = "date" } })*@
                                @Html.ValidationMessageFor(model => model.DateOfEnd, "", new { @class = "text-danger" })

                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.TelePhoneNumber, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                <div class="col-md-1">
                                    @Html.TextBoxFor(model=>model.CountryCode , null, new { @class = "form-control",@id= "txtCountryCode" })
                                </div>
                                <div class="col-md-2">
                                    @Html.TextBoxFor(model => model.Prefix, null, new { @class = "form-control",@id= "txtPrefix" })
                                </div>
                                <div class="col-md-2">
                                    @Html.TextBoxFor(model => model.TelePhoneNumber, null, new { @class = "form-control",@id= "txtTelephoneNo" })
                                </div>
                                <div class="col-md-3">
                                    @Html.TextBoxFor(model => model.Extension, null, new { @class = "form-control",@id= "txtExtension" })
                                </div>

                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.Mobile, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                <div class="col-md-2">
                                    @Html.TextBoxFor(model => model.CountryCode, null, new { @class = "form-control",@id= "txtCountryCodeMobile" })
                                </div>
                                <div class="col-md-6">
                                    @Html.TextBoxFor(model => model.Mobile, null, new { @class = "form-control",@id= "txtMobile" })
                                </div>
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.SAPNo, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.EditorFor(model => model.SAPNo, new { htmlAttributes = new { @class = "form-control" } })
                                @Html.ValidationMessageFor(model => model.SAPNo, "", new { @class = "text-danger" })

                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.Voip, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.EditorFor(model => model.Voip, new { htmlAttributes = new { @class = "form-control" } })
                                @Html.ValidationMessageFor(model => model.Voip, "", new { @class = "text-danger" })

                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.SkypeId, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.EditorFor(model => model.SkypeId, new { htmlAttributes = new { @class = "form-control" } })
                                @Html.ValidationMessageFor(model => model.SkypeId, "", new { @class = "text-danger" })

                            </div>
                        </div>

                        <div class="form-group">
                            @Html.LabelFor(model => model.Photo, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.TextBox("file", "", new { type = "file" })
                                @Html.ValidationMessageFor(model => model.Photo, "", new { @class = "text-danger" })
                            </div>
                        </div>
                        <div class="form-group">

                            @Html.LabelFor(model => model.Status, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.DropDownListFor(model => model.Status, new SelectList(
                                    new[]
                                    {
                                        new { Value = "true", Text = "Activate" },
                                        new { Value = "false", Text = "De-Activate" },
                                    },
                                    "Value",
                                    "Text",
                                    Model ),
                                new { @class = "form-control" })
                                @Html.ValidationMessageFor(model => model.Status, "")
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.UserRole, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.DropDownListFor(model => model.UserRole, new List<SelectListItem>
                                {
                                    new SelectListItem{ Text="Main user", Value = "MAIN" },
                                    new SelectListItem{ Text="Country AV", Value = "INAV" },
                                    new SelectListItem{ Text="Guest user", Value = "GUEST" }

                                }, new { @class = "form-control" })
                                @Html.ValidationMessageFor(model => model.UserRole, "", new { @class = "text-danger" })
                            </div>
                        </div>
                        <div class="form-group">
                            @Html.LabelFor(model => model.CountryId, htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-6">
                                @Html.DropDownListFor(model => model.CountryId, new SelectList(ViewBag.Country, "Value", "Text"), new { @id = "ddlCountry", @class = "form-control" })

                            </div>
                        </div>
                        <div class="form-group">
                            @Html.Label("Module", htmlAttributes: new { @class = "control-label col-md-2" })
                            <div class="col-md-4">
                                @Html.Hidden("myListBox1Values")
                                @Html.ListBox("myListBox1", new MultiSelectList(ViewBag.UnAssignedMudules, "id", "Module"), new { @class = "form-control",
                               size = "10",
                               style = "height: 122px"
                           });

                            </div>
                            <div class="col-sm-2 center">
                                <br /><br />
                                <input class="btn aqua block bottom15" id="bt_LR" onclick="javascript:addOnCon(document.getElementById('myListBox1'),document.getElementById('myListBox2'));"
                                       type="button" value=">>" name="bt_LR" title="Left to Right" />
                                <br />
                                <input class="btn aqua block bottom15" id="bt_RL" onclick="javascript:removeOnCon(document.getElementById('myListBox1'),document.getElementById('myListBox2'));"
                                       type="button" value="<<" name="bt_RL" title="Right to Left" />
                            </div>
                            <div class="col-md-4">
                                @Html.Hidden("myListBox2Values")
                                @Html.ListBox("myListBox2", new MultiSelectList(ViewBag.AssignedMudules, "id", "Module"), new { @class = "form-control",
                               size = "10",
                               style = "height: 122px"
                           });
                            </div>
                        </div>

                    </div>
                    <div class="form-group center">
                        <div class="col-sm-12">
                            <input type="submit" value="@Resources.Resource.submit" class="btn aqua block bottom15" />
                        </div>
                    </div>

                </div>
            </div>
        </div>
        <div class="row  top20">
            <!-- Horizontal Form start -->
            <div class="col-sm-12">
                <div class="ibox">

                    @*@if (ViewData["info"] != null)
                        {
                            @Html.Partial("Info", ViewData["info"]);
                        }*@
                </div>
            </div>
        </div>
    </div>
    <script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/jquery-ui.min.js" type="text/javascript"></script>
    <link href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/themes/blitzer/jquery-ui.css" />
    <script type="text/javascript">
        $(function () {
            $("#txtProject").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: '/UserAdministrator/AutoComplete/',
                        data: "{ 'prefix': '" + request.term + "'}",
                        dataType: "json",
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        success: function (data) {
                            response($.map(data, function (item) {
                                return item;
                            }))
                        },
                        error: function (response) {
                            alert(response.responseText);
                        },
                        failure: function (response) {
                            alert(response.responseText);
                        }
                    });
                },
                select: function (e, i) {
                    $("#hfCustomer").val(i.item.val);
                },
                minLength: 1
            });
        });
    </script>
    <script>
        $(document).ready(function () {
            $('form').submit(function () {
                var json = "";
                var json1 = "";
                $("#myListBox1 option").each(function () {
                    value = $(this).val()
                    text = $(this).html()

                    if (json.length > 1)
                        json += ",";

                    json += value + ":" + text;
                });

                $("#myListBox2 option").each(function () {
                    value = $(this).val()
                    text = $(this).html()

                    if (json1.length > 1)
                        json1 += ",";

                    json1 += value + ":" + text;
                });

                $('#myListBox1Values').val(json.toString());
                $('#myListBox2Values').val(json1.toString());
            });
        });
    </script>
}

 // GET: UserAdministrator/Edit/5
        public ActionResult Edit(int id)
        {
            
            GetDropDownListData();
            UserUnAssignedModules(id);
            UserAssignedModules(id);
            GetAllModules();
            DataAccessLayer objDB = new DataAccessLayer();
            
            return View(objDB.User_SelectById(id));
        }

        // POST: UserAdministrator/Edit/5
        [HttpPost]
        public ActionResult Edit(UserAdministratorModel UAM, string myListBox2, string myListBox2Values, string myListBox1, string myListBox1Values, FormCollection form)
        {
            if (ModelState.IsValid)
            {
                try
                {
                     GetDropDownListData();
                                       
                    DataAccessLayer objDB = new DataAccessLayer();
                    objDB.User_Update(UAM);
                    AssignModules(myListBox2, myListBox2Values, Convert.ToInt32(UAM.id));
                    UnAssignModules(myListBox1, myListBox1Values, Convert.ToInt32(UAM.id));
                    return RedirectToAction("Index");
                }
                catch (Exception ex)
                {
                    return View();
                }
            }
            else
            {
                var query = from state in ModelState.Values
                            from error in state.Errors
                            select error.ErrorMessage;

                var errorList = query.ToList();
                
                return View();
            }
        }

Answer 4

User475983607 posted

I build a test using your code and the model binder populated the UserAdministratorModel properly.  My best guess is you have bugs in your data access layer...

objDB.User_Update(UAM);

The data access code is not shared.   Once again, the community can only guess where the bugs are located.

You're been on this forum for a long time (2600+ posts) and should know how to use the Visual Studio debugger; set a breakpoint and step through the code.  Most importantly, the error message shows you the exact line of code that caused the error.  I'm bewildered why you did not share this very important bit of information.  It took me a long time build test code.  If you posted the error message I would not have wasted a bunch time...  

Debug your code!

Answer 5

User1520731567 posted

Hi demoninside9,

I am wondering when it has value in textboxes why it gets null on click.

Do you mean your datatime display null after clicking?

But it didn't show in my project,there is no such click event in javascript code.

And I also didn't reproduce your issue.

As @mgebhard said,the final problem will be located on DataAccessLayer.

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

But according to this error,the usual solution is change datatime to datatime2,you could try this way firstly.

https://stackoverflow.com/a/16078824/11741806

If not work,please post more detail about DataAccessLayer.

Best Regards.

Yuki Tao

Answer 6

User753101303 posted

Hi,

Not sure what User_Update does (it  just uses EF or you have written your own db access code ?). The next logical step is to check which values you try to assign.