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

 

Scenario
========

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

WebForm 

In SQL express we are having following columns

Column

 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

error_str

 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 nullgetDate = 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<br>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!!