CODEWITHSUNDEEP
c#sqlsql-serverdatetime
Dejsa Cocan
Dejsa Cocan

Reputation: 1569

Allow for null datetime entry into database?

I have two calendar date selecting controls that are not required for completion of a form. However, I keep getting this exception error whenever I try to submit a form without selecting something from the calendar controls:

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

In the database, my SQL table has been set to allow nulls for both datetime fields.

Is there a way to allow for a null or blank entry for these two fields into the database without resulting in an error?

This is the code that saves the form information to the database (I cut it down to include only the 2 datetime fields):

private void SaveCustomerInfo(int CustID)
    {
        int currentUserID = AbleContext.Current.UserId;

        string addQuery = "INSERT INTO Customers (TaxExemptDate, AuthDate) VALUES(@TaxExemptDate, @AuthDate)";

        try
        {
            using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AbleCommerce"].ToString()))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand(addQuery, cn);
                cmd.Connection = cn;


                    cmd.Parameters.Add(new SqlParameter("@TaxExemptDate", TaxExemptDate.SelectedStartDate));

                    cmd.Parameters.Add(new SqlParameter("@AuthDate", AuthDate.SelectedStartDate));

                    _CustomerID = AlwaysConvert.ToInt(Request.QueryString["CustomerID"]);
                    int.TryParse(Request.QueryString["CustomerID"], out _CustomerID);
                    if (_CustomerID == 0)
                    {
                        cmd.CommandText = addQuery;
                    }

                    if (_CustomerID !=0)
                    {
                        cmd.Parameters.Add(new SqlParameter("@CustomerID", CustID));
                        cmd.CommandText = editQuery;
                    }

                    cmd.ExecuteNonQuery();
                    cn.Close();
                }
            }
            catch (Exception exception)
            {
                Logger.Warn("Admin\\People\\Customers\\EdutCustomer.aspx - SaveCustomerInfo", exception);

            }
    }

Upvotes: 1

Views: 176

Answers (1)

Muster Station
Muster Station

Reputation: 514

You don't pass null, you pass DBNull.Value instead through your SqlParameter

cmd.Parameters.Add(new SqlParameter("@TaxExemptDate", TaxExemptDate.SelectedStartDate == null ? (Object)DBNull.Value : TaxExemptDate.SelectedStartDate));

or

cmd.Parameters.Add(new SqlParameter("@TaxExemptDate", TaxExemptDate.SelectedStartDate == DateTime.MinValue ? (Object)DBNull.Value : TaxExemptDate.SelectedStartDate));

Upvotes: 8

Related Questions