Insert Null value into DateTime column in SQL server from ASPX application

http://blogs.msdn.com/jaskis/archive/2008/07/09/insert-null-value-into-datetime-column-in-sql-server-from-aspx-application.aspx

Scenario
========

We have following web form asking user to enter name and joining date

 

In SQL express we are having following columns

Error
======
When ever user does not enter joining date instead of getting null value added into join_date column we end with exception on browser

Code Behind
===========

    SqlCommand cmd1 = new SqlCommand("INSERT INTO emp(emp_name, join_date) VALUES(@emp_name1,@join_date1)", con1);

    cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar).Value = name.Text;
    cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime).Value = joindate.Text;

    cmd1.ExecuteNonQuery();
    Label1.Text = " Record added";

 

Solution
========

Make a check(if) for empty string and insert specific SqlDateTime.null value

C#

cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar);
cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime);

cmd1.Parameters["@emp_name1"].Value = name.Text;

System.Data.SqlTypes.SqlDateTime getDate;
//set DateTime null
getDate = SqlDateTime.Null;

if (joindate.Text == "")
{
    cmd1.Parameters["@join_date1"].Value = getDate;
}
else
{
    cmd1.Parameters["@join_date1"].Value = joindate.Text;
}

 

VB.NET

cmd1.Parameters.Add("@emp_name1", SqlDbType.NVarChar)
cmd1.Parameters.Add("@join_date1", SqlDbType.DateTime)

cmd1.Parameters("@emp_name1").Value = name.Text

Dim getDate As SqlDateTime
getDate = SqlDateTime.Null

If joindate.Text = "" Then
   cmd1.Parameters("@join_date1").Value = getDate
Else
   cmd1.Parameters("@join_date1").Value = joindate.Text
End If

 

Hope this helps!!