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

 


Comments (13)

  1. jagdish says:

    hi

    when I applied above solution got this error

    Cast from type ‘SqlDateTime’ to type ‘Date’ is not valid.

  2. Kostas says:

    Thank you! I was looking all over to find a way to insert Null in a datetime column.

    cheers

  3. sudhir says:

    when i applied the above solution ,its not working

  4. Sandeep Shakya says:

    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

  5. harsh says:

    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);

  6. Anbarasu says:

    Really Useful and Thanks a lot

  7. Jas says:

    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

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

  9. Bartek says:

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

  10. Fatima says:

    Thankssssssssssssssssssssssssssssss :))))

  11. kh says:

    set datatype allow nul and

    "[DateEnd]=Null"

  12. nilesh says:

    Cast from type 'SqlDateTime' to type 'Date' is not valid.  

    please add namespace

    using System.Data.SqlTypes;