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

Question

I am working updating customer details in gridview. Here, I am using 3 tier architecture. I am trying to update certain fields. But, I end up getting above mentioned error. Here is my code.

protected void MyProfileGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        int customerId = Convert.ToInt32(Session["CustomerID"]);

        TextBox name = (TextBox)MyProfileGridView.Rows[e.RowIndex].FindControl("txt_Name");
        TextBox shopName = (TextBox)MyProfileGridView.Rows[e.RowIndex].FindControl("txt_ShopName");
        TextBox address = (TextBox)MyProfileGridView.Rows[e.RowIndex].FindControl("txt_Address");
        TextBox mobile1 = (TextBox)MyProfileGridView.Rows[e.RowIndex].FindControl("txt_Mobile1");
        TextBox mobile2 = (TextBox)MyProfileGridView.Rows[e.RowIndex].FindControl("txt_Mobile2");
        TextBox password = (TextBox)MyProfileGridView.Rows[e.RowIndex].FindControl("txt_Password");

        Customer customer = new Customer();
        customer.CustomerID = customerId;
        customer.CustomerName = name.Text;
        customer.ShopName = shopName.Text;
        customer.Address = address.Text;
        customer.Mobile1 = mobile1.Text;
        customer.Mobile2 = mobile2.Text;
        customer.Password = password.Text;

        CustomerBL.UpdateCustomer(customer);
        MyProfileGridView.EditIndex = -1;
        MyProfileGridView.DataSource = CategoryBL.GetCategories();
        MyProfileGridView.DataBind();
    }

Business Logic Layer code for updating customer.

public static void UpdateCustomer(Customer customer)
    {
        string query = "UPDATE [Customers] SET [LoginID] = @LoginID, [Password] = @Password, [CustomerName] = @CustomerName, [ShopName] = @ShopName, [Address] = @Address, [Mobile1] = @Mobile1, [Mobile2] = @Mobile2, [ReferenceNumber] = @ReferenceNumber, [SignUpDate] = @SignUpDate, [Enabled] = @Enabled WHERE [CustomerID] = @CustomerID";
        SqlCommand cmd = new SqlCommand(query);

        cmd.Parameters.AddWithValue("@LoginID", SqlDbType.Text).Value = customer.LoginID;
        cmd.Parameters.AddWithValue("@Password", SqlDbType.Text).Value = customer.Password;
        cmd.Parameters.AddWithValue("@CustomerName", SqlDbType.Text).Value = customer.CustomerName;
        cmd.Parameters.AddWithValue("@ShopName", SqlDbType.Text).Value = customer.ShopName;
        cmd.Parameters.AddWithValue("@Address", SqlDbType.Text).Value = customer.Address;
        cmd.Parameters.AddWithValue("@Mobile1", SqlDbType.Text).Value = customer.Mobile1;
        cmd.Parameters.AddWithValue("@Mobile2", SqlDbType.Text).Value = customer.Mobile2;
        cmd.Parameters.AddWithValue("@ReferenceNumber", SqlDbType.Text).Value = customer.ReferenceNumber;
        cmd.Parameters.AddWithValue("@SignUpDate", SqlDbType.DateTime2).Value = customer.SignUpDate;
        cmd.Parameters.AddWithValue("@Enabled", SqlDbType.Bit).Value = customer.Enabled;
        cmd.Parameters.AddWithValue("@CustomerID", SqlDbType.Text).Value = customer.CustomerID;

        DbUtility.UpdateDb(cmd);
    }

Kindly help me with this. Thanks in advance.

Where did you set SignUpDate? It seems you forgot set DateTime for that field. — nguyenhoai890, Aug 30 '16 at 09:12
Possible duplicate of [Error - SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM](http://stackoverflow.com/questions/468045/error-sqldatetime-overflow-must-be-between-1-1-1753-120000-am-and-12-31-999) — Andrey Korneyev, Aug 30 '16 at 09:12
The error message is pretty self-explanatory. And since the only DateTime in your code is `customer.SignUpDate`, there is probably a conversion problem. — VDWWD, Aug 30 '16 at 09:12

Alex · Accepted Answer · 2016-08-30T16:21:36.057

2

Your SignUpDate is not being populated, therefore DateTime.MinValue is being used, which is not valid for SqlDbType.DateTime
However.. the somewhat confusing part to me is that you specify SqlDbType.DateTime2 when adding your parameters.
I suspect when you created your table, you in fact use SqlDbType.DateTime

Anyhow, you'll need to populate the date, something like

    Customer customer = new Customer();
    customer.SignUpDate = DateTime.Now; //notice this line
    customer.CustomerID = customerId;
    customer.CustomerName = name.Text;
    //etc...

edited Aug 30 '16 at 16:21

answered Aug 30 '16 at 09:12

Alex

37,502
51
204
332

It should be valid for `DateTime2` though. It's range is 0001-01-01 through 9999-12-31. It's not valid for `DateTime` – Chris Dunaway Aug 30 '16 at 16:15
true- this means the error the OP is receiving suggests that although he specified `DateTime2` it's not actually using it - the out of range error suggests it's using `DateTime` - weird – Alex Aug 30 '16 at 16:17
Agreed. Your answer however states _DateTime.MinValue is being used, which is **not valid** for SqlDbType.DateTime2_. I was just pointing out that it _is_ valid for `DateTime2` – Chris Dunaway Aug 30 '16 at 16:19
amended the answer – Alex Aug 30 '16 at 16:21

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

1 Answers1