1

I am using ASP.NET MVC and I am trying to save values from my view in a SQL Server table using stored procedures. The problem that the procedure doesn't work because of an issue with the Date attribute. I don't get any error or exception but when I debug, the debugger goes direct to catch after opening the SQL Server connection and I can catch this exception :

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

This is what I wrote in my controller :

[HttpPost]
public ActionResult Save_Bestellung(Bestellung bs)
{
        var errors = ModelState.Values.SelectMany(v => v.Errors);

        try
        {
            if (ModelState.IsValid)
            {
                BestellungManagement bdb = new BestellungManagement();

                if (bdb.AddBestellung(bs))
                {
                    return View("~/Views/Bestellung/Index.cshtml");
                    ViewBag.Message = "Bestellung saved Successfully";
                    ModelState.Clear();
                }
            }

            return RedirectToAction("Index");
        }
        catch
        {
            return JavaScript("alert('Order is Wrong ! Please verify your data !!')");
        }
}

and this is my class BestellungManagement :

public bool AddBestellung(Bestellung bs)
{
        try
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DeliveryCon"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand("AddNewBestellung", con))
                {
                    cmd.Parameters.AddWithValue("@Date", bs.Date);

                    con.Open();
                    int i = cmd.ExecuteNonQuery();
                    con.Close();

                    if (i >= 1)
                       return true;
                    else
                       return false;
                }
            }
        }
        catch(Exception ex)
        {
            string e = ex.Message;
            return false;
        }
}

In model Bestellung.cs I have:

public DateTime Date { get; set; }

Database Schema :

  [Date]         DATETIME      NOT NULL,

In the view :

<label>DATE : </label>
 <input id="datepicker" name="Text" type="text" value="mm/dd/yyyy" onfocus="this.value = '';" onblur="if (this.value == '') {this.value = 'mm/dd/yyyy';}" required="">

the datepicker Function :

<script>
    $(function() 
     {
      $("#datepicker").datepicker({ minDate: 0 });
      });
 </script>
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Exact
  • 269
  • 6
  • 18
  • 1
    Take a peek at https://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server – John Cappelletti Oct 02 '17 at 07:56
  • 2
    Use `DATETIME2` instead of `DATETIME` - [Date and Time Data Types](https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql) –  Oct 02 '17 at 07:57
  • The problem exists in your DB - `DateTime.MinValue` doesn't apply to `datetime` data type. Change corresponding column data type to `datetime2` instead. – Tetsuya Yamamoto Oct 02 '17 at 08:05
  • 1
    [never use dateime again](https://blogs.msdn.microsoft.com/cdnsoldevs/2011/06/22/why-you-should-never-use-datetime-again/) – Zohar Peled Oct 02 '17 at 08:06
  • @StephenMuecke , Tetsuya Yamamto ok but what I have to use in Model coz there is no type datatime2 in C# – Exact Oct 02 '17 at 08:07
  • 2
    @Exact `datetime2` is SQL data type, change `datetime` to that data type in your DB - C#'s `DateTime` uses same range as `datetime2` in SQL. – Tetsuya Yamamoto Oct 02 '17 at 08:08
  • @TetsuyaYamamoto I did it but the problem still exist, the value of date is always strange with too much zeros – Exact Oct 02 '17 at 08:11
  • @Exact How was the date value exactly formed (with those zeros, please give an example)? `$("#datepicker").datepicker({ minDate: 0 });` sets today as minimum date, can it related to the zero-values? – Tetsuya Yamamoto Oct 02 '17 at 08:23
  • 1
    Change `cmd.Parameters.AddWithValue("@Date", bs.Date);` to `cmd.Parameters.Add("@Date", SqlDbType.DateTime2).Value = bs.Date;`. Read [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) – Zohar Peled Oct 02 '17 at 08:26
  • @TetsuyaYamamoto yes also when I set is today, the Date got this value : Date = {01.01.0001 00:00:00} – Exact Oct 02 '17 at 08:27
  • @ZoharPeled I try it but the same problem still exist and the date still getting this value {01.01.0001 00:00:00}, i think that the problem is with the JS Function – Exact Oct 02 '17 at 08:31
  • I was helping with the SqlDateTime overflow. The JS function is another problem (one that sadly, I can't help with since the last time I wrote js was too many years ago) – Zohar Peled Oct 02 '17 at 08:33
  • @Exact Is that `bs.Date` belongs to `DateTime.MinValue` every time the form submitted, or the datepicker showing `DateTime.MinValue` value even it set to today's date? I suspected your binding doesn't work correctly if first condition apply. – Tetsuya Yamamoto Oct 02 '17 at 08:33
  • Is this a model binding issue? Your input's name is "Text" but your model's property name is "Date". Therefor, MVC isn't able to bind the input's value to the model, and so it will always be null. – SlightlyMoist Oct 02 '17 at 08:43
  • @TetsuyaYamamoto I don't know if i understood what you mean it right, but this value of Zero still occurs when I also let the Field date empty – Exact Oct 02 '17 at 08:43
  • @ZoharPeled i am not sure if a JS Problem or SQL issue but anyway thanks a lot for you help – Exact Oct 02 '17 at 08:44
  • @SlightlyMoist you mean that I have to change the field Text to date or how ? Can you please more explain your suggestion, thx – Exact Oct 02 '17 at 08:46
  • @Exact Seems that a model binding issue occurred. `Bestellung.Date` property have `DateTime.MinValue` as default value when it not bind to anything, and your view doesn't mention `Date` property. You can try using `@model Bestellung` & use `@Html.TextBoxFor(model => model.Date, new { id = "datepicker" })` helper, set the datepicker to that textbox. – Tetsuya Yamamoto Oct 02 '17 at 08:46
  • @Exact - try changing the name of your input to match your model. i.e. . Without this, MVC is unable to match the values in the view and use them to populate your model. Also, try using HtmlHelper wherever possible to avoid this sort of thing. i.e. @Html.TextBoxFor(x => x.Date) – SlightlyMoist Oct 02 '17 at 08:51
  • @TetsuyaYamamoto before trying your solution, just I wnt to let you know that I changed the name from "Text" to "date" in View like SlightlyMoist suggesed and the null's value disappear, I get now something like that Date = {10.02.2017 00:00:00} when I set Today's date but it still not record in Database – Exact Oct 02 '17 at 08:56
  • @SlightlyMoist thx your suggestion solves the problem of Null Values, I get now only Null in Time coz I don't have how I fill value of Times Date = {10.02.2017 00:00:00} but it still not storing in Db – Exact Oct 02 '17 at 08:59
  • @Exact Check `bs.Date` property after submitting, is it still contain `DateTime.MinValue` then? If it's false, the time part should contain value from datepicker (if it written as `10.02.2017 xx:xx:xx`, x = any number). Otherwise, you need to bind the property using HTML helper. – Tetsuya Yamamoto Oct 02 '17 at 09:03
  • @TetsuyaYamamoto yes after submitting, it still contain this Value {10.02.2017 00:00:00}, and the problem with HTML Helper that my view don't accept the model Bestellung – Exact Oct 02 '17 at 09:11

0 Answers0