SQL Server DateTime Accept NULL

Question

I've tried to make my code as compact as possible.

Using Microsoft SQL Server, .NET 2.0

I have a date field in my database which accepts null values

LeaseExpiry(datetime, null)

I grab the value of the the textbox and convert it to datetime.

DateTime leaseExpiry = Convert.ToDateTime(tbLeaseExpiry.Text);

INSERT_record(leaseExpiry);

The problem I'm having is if the form is submitted and the textbox is empty. I get this error back:

String was not recognized as a valid DateTime.

How do I set my code up so that if the textbox is empty, the row is created in the database with NULL?

I've tried initializing my variable to NULL but get an error in Visual Studio

DateTime leaseExpiry = null;

Cannot convert null to 'System.DateTime' because it is a non-nullable value type.

Here's the Data Access Layer if that helps

public string INSERT_record(DateTime leaseExpiry)
{
     //Connect to the database and insert a new record 
     string cnn = ConfigurationManager.ConnectionStrings[connname].ConnectionString;

     using (SqlConnection connection = new SqlConnection(cnn))
     {
        string SQL = string.Empty;
        SQL = "INSERT INTO [" + dbname + "].[dbo].[" + tblAllProperties + "] ([LeaseExpiry]) VALUES (@leaseExpiry);

         using (SqlCommand command = new SqlCommand(SQL, connection))
         {
                command.Parameters.Add("@leaseExpiry", SqlDbType.DateTime);
                command.Parameters["@leaseExpiry"].Value = leaseExpiry;
         }

         try
         {
                connection.Open();
                command.ExecuteNonQuery();
                return "Success";
         }
         catch (Exception ex)
         {
                return ex.Message;
         }
     }
}

Thank you

Answer 1

Try using a nullable DateTime and TryParse()

DateTime? leaseExpirey = null;
DateTime d;
if(DateTime.TryParse(tbLeaseExpiry.Text, out d))
{
    leaseExpirey = d;
}

INSERT_record(leaseExpirey);

Answer 2

Indeed, DateTime cannot be null. But: DateTime? can be. Note also that on a parameter, null means "don't send"; you would need:

public string INSERT_record(DateTime? leaseExpirey)
{
    // ...
    command.Parameters.Add("@leaseExpirey", SqlDbType.DateTime);
    command.Parameters["@leaseExpirey"].Value =
                ((object)leaseExpirey) ?? DBNull.Value;
    // ...
}

Answer 3

You could make leaseExpirey a nullable DateTime - i.e. DateTime? leaseExpirey

Then you can say:

DateTime? leaseExpirey;
if (!string.IsNullOrEmpty(tbLeaseExpiry.Text.Trim()))
    leaseExpirey = Convert.ToDateTime(tbLeaseExpiry.Text);

INSERT_record(leaseExpirey);

You'd also need to change INSERT_record to accept a DateTime? parameter instead of DateTime.