'how to pass null value as a datetime to sql server (DAL Layer)

i have got a column (nullable but it is Datetime)in sql server ..

if user is not selected any date i need to pass the null value for this field for that purpose I have done like this below

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        DateTime expiryDate;
        DateTime? expDate = null;
         if (chkExpDate.Checked == true)
        {
            expiryDate = Convert.ToDateTime(txtExpiryDate.Text);
        }
        else if (expDate.HasValue)
        {
            expiryDate = expDate.Value;
        }
        else
            expiryDate = Convert.ToDateTime(DBNull.Value);
         // here I am getting error like "object cannot be cast from DBNULL or value

what i need to do if i have to overcome this problem .. I need to pass null value if user is not selected any date how can i do this..

would any one please help on this.. Many thanks

DAL for this 

public bool ReAssignLicense(string certificateID, string serialNumber, string newEmail, string ticketID, string backupBy, string customerName, DateTime expDate)
    {
        List<SqlParameter> ParaList = new List<SqlParameter>();
        ParaList.Add(new SqlParameter("@certificate", certificateID));
        ParaList.Add(new SqlParameter("@certSN", serialNumber));
        ParaList.Add(new SqlParameter("@newemail", newEmail));
        ParaList.Add(new SqlParameter("@ticket", ticketID));
        ParaList.Add(new SqlParameter("@bkpBy", backupBy));
        ParaList.Add(new SqlParameter("@customer_name", customerName));
        ParaList.Add(new SqlParameter("@exp_date", expDate));

        return SqlHelper.ExecuteNonQuery(new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString),CommandType.StoredProcedure,"sp_Update",ParaList.ToArray()) > 0;
    }
c++sharpsql-serverdatetimenullnullable


Solution 1:[1]

You can do like this 

SqlParameter moFrom1Param = new SqlParameter("@MoFrom1", expairydate == null 
           ? DBNull.Value : expairydate);
            moFrom1Param.IsNullable = true;
            moFrom1Param.Direction = ParameterDirection.Input;
            moFrom1Param.SqlDbType = SqlDbType.DateTime;
            cmd.Parameters.Add(moFrom1Param);

You can make your DatTime variable as nullable variable that will handle your issue without doing anything.

as your expiryDate is nullable filed you can pass thsi filed directly in sql command or query you are calling form the dabase layer.

you can change this line like this 

expiryDate = DateTime.MinValue;

Check this post : http://www.dotnetperls.com/datetime-minvalue

//
    // This won't compile!
    //
    // DateTime dateTime1 = null;
    //
    // This is the best way to null out the DateTime.
    //
    DateTime dateTime2 = DateTime.MinValue;

Solution 2:[2]

Check this

protected void btnSubmit_Click(object sender, EventArgs e)
{
  DateTime? expDate = null;
  if (chkExpDate.Checked == true)
  {
    expDate = Convert.ToDateTime(txtExpiryDate.Text);
  }

I set your DateTime variable here to nullable.

public bool ReAssignLicense(string certificateID, string serialNumber, string newEmail, string ticketID, string backupBy, string customerName, DateTime? expDate)
{
  List<SqlParameter> ParaList = new List<SqlParameter>();
  ParaList.Add(new SqlParameter("@certificate", certificateID));
  ParaList.Add(new SqlParameter("@certSN", serialNumber));
  ParaList.Add(new SqlParameter("@newemail", newEmail));
  ParaList.Add(new SqlParameter("@ticket", ticketID));
  ParaList.Add(new SqlParameter("@bkpBy", backupBy));
  ParaList.Add(new SqlParameter("@customer_name", customerName));

  if (expDate != null)
    ParaList.Add(new SqlParameter("@exp_date", expDate));

  return SqlHelper.ExecuteNonQuery(new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString), CommandType.StoredProcedure, "sp_Update", ParaList.ToArray()) > 0;
}

Solution 3:[3]

Hi If I understood your problem correctly then you ant to set expiryDate as null in last condition.

For the same you will be required to change the variable declaration DateTime expiryDate as nullable type same you did for DateTime? expDate = null;

So your revised syntax as posted above will be as follows

        DateTime? expiryDate;
        DateTime? expDate = null;
        if (chkExpDate.Checked == true)
        {
            expiryDate = Convert.ToDateTime(txtExpiryDate.Text);
        }
        else if (expDate.HasValue)
        {
            expiryDate = expDate.Value;
        }
        else

            expiryDate = null;

Hope that will work for you.

Solution 4:[4]

In SQL Server just insert NULL in the Datetime column: 

INSERT INTO Table(name, datetimeColumn, ...)
VALUES('foo bar', NULL, ..);

Or, you can make use of the DEFAULT constaints:

...
DateTimeColumn DateTime DEFAULT NULL,
...

Then you can ignore it completely in the INSERT statemnt and it will be inserted withe the NULL value:

INSERT INTO Table(name, ...)
VALUES('foo bar', ..);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 DotNet Team
Solution 4

Related Questions