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