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

 

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 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!!

Comments

• Anonymous
  July 09, 2008
  PingBack from http://blog.a-foton.ru/2008/07/insert-null-value-into-datetime-column-in-sql-server-from-aspx-application/

• Anonymous
  September 09, 2008
  hi when I applied above solution got this error Cast from type 'SqlDateTime' to type 'Date' is not valid.

• Anonymous
  November 05, 2008
  Thank you! I was looking all over to find a way to insert Null in a datetime column. cheers

• Anonymous
  December 18, 2008
  when i applied the above solution ,its not working

• Anonymous
  March 03, 2009
  Before this code i had tried so many code but which is not working fine but after some changes this is really working fine. Thanks

• Anonymous
  March 20, 2009
  i'm using dataset and adding record row by row in datatable of dataset and  then updating database. i'm getting error while adding null value to field of type datetime. my code look like... DataRow rowNew = m_dt_student.NewRow();            rowNew["stud_id"] = m_NextStudId;            rowNew["stud_name"] = txtbxStu_Name.Text;            rowNew["stud_age"] = txtbxStu_Age.Text;            rowNew["start_date"] = txtbxDate1.Text;            //rowNew["end_date"] = txtbxDate2.Text;            sqldatenull = SqlDateTime.Null;            if (txtbxDate2.Text == "")            {                //txtbxDate2.Text = sqldatenull;                rowNew["end_date"] = Convert.ToDateTime(sqldatenull);                }                        rowNew["end_date"] = txtbxDate2.Text;            m_dt_student.Rows.Add(rowNew);            SqlCommandBuilder cmdcon = new SqlCommandBuilder(adapter);            adapter.Update(m_dt_student);

• Anonymous
  March 25, 2009
  Really Useful and Thanks a lot

• Anonymous
  March 30, 2009
  Harsh, Why you are doing this, you are bound to get error :  rowNew["end_date"] = Convert.ToDateTime(sqldatenull); Try this  rowNew["end_date"] = sqldatenull; Thanks JAs

• Anonymous
  June 13, 2009
  話題の小向美奈子ストリップを隠し撮り！入念なボディチェックをすり抜けて超小型カメラで撮影した神動画がアップ中！期間限定配信の衝撃的映像を見逃すな

• Anonymous
  January 08, 2012
  Wouldn't it be easier to make the date a nullable type? evonet.com.au/overview-of-c-nullable-types

• Anonymous
  June 05, 2013
  Thankssssssssssssssssssssssssssssss :))))

• Anonymous
  November 13, 2013
  set datatype allow nul and "[DateEnd]=Null"

• Anonymous
  January 31, 2014
  Cast from type 'SqlDateTime' to type 'Date' is not valid.   please add namespace using System.Data.SqlTypes;