Introduction

Inserting a null value to the DateTime Field in SQL Server is one of the most common issues giving various errors. Even if one enters null values the value in the database is some default value as 1/1/1900 12:00:00 AM.

The Output of entering the null DateTime based on the code would in most cases have errors as:

Or no error but DataTime entered in Database would be as 1/1/1900 12:00:00 AM

So lets write the code to enter null values in the DataBase.

The user interface is as follows:

Namespaces used

C#

  1. string sqlStmt;
  2. string conString;
  3. SqlConnection cn = null;
  4. SqlCommand cmd = null;
  5. SqlDateTime sqldatenull;
  6. try {
  7. sqlStmt = "insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) ";
  8. conString = "server=localhost;database=Northwind;uid=sa;pwd=;";
  9. cn = new SqlConnection(conString);
  10. cmd = new SqlCommand(sqlStmt, cn);
  11. cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 11));
  12. cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 40));
  13. cmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));
  14. sqldatenull = SqlDateTime.Null;
  15. cmd.Parameters["@FirstName"].Value = txtFirstName.Text;
  16. cmd.Parameters["@LastName"].Value = txtLastName.Text;
  17. if (txtDate.Text == "") {
  18. cmd.Parameters["@Date"].Value = sqldatenull;
  19. //cmd.Parameters["@Date"].Value = DBNull.Value;
  20. }
  21. else {
  22. cmd.Parameters["@Date"].Value = DateTime.Parse(txtDate.Text);
  23. }
  24. cn.Open();
  25. cmd.ExecuteNonQuery();
  26. Label1.Text = "Record Inserted Succesfully";
  27. }
  28. catch(Exception ex) {
  29. Label1.Text = ex.Message;
  30. }
  31. finally {
  32. cn.Close();
  33. }
VB.NET

  1. Dim sqlStmt As String
  2. Dim conString As String
  3. Dim cn As SqlConnection
  4. Dim cmd As SqlCommand
  5. Dim sqldatenull As SqlDateTime
  6. Try
  7. sqlStmt = "insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) "
  8. conString = "server=localhost;database=Northwind;uid=sa;pwd=;"
  9. cn =New SqlConnection(conString)
  10. cmd =New SqlCommand(sqlStmt, cn)
  11. cmd.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar, 11))
  12. cmd.Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar, 40))cmd.Parameters.Add(New SqlParameter("@Date", SqlDbType.DateTime))
  13. sqldatenull = SqlDateTime.Null
  14. cmd.Parameters("@FirstName").Value = txtFirstName.Text
  15. cmd.Parameters("@LastName").Value = txtLastName.Text
  16. If (txtDate.Text = "") Then
  17. cmd.Parameters("@Date").Value = sqldatenull
  18. 'cmd.Parameters("@Date").Value = DBNull.Value
  19. Else
  20. cmd.Parameters("@Date").Value = DateTime.Parse(txtDate.Text)
  21. End If
  22. cn.Open()
  23. cmd.ExecuteNonQuery()
  24. Label1.Text = "Record Inserted Succesfully"
  25. Catch ex As Exception
  26. Label1.Text = ex.Message
  27. Finally
  28. cn.Close()
  29. End Try
Code for System.Data.SqlClient.

C#

  1. string sqlStmt;
  2. string conString;
  3. OleDbConnection cn = null;
  4. OleDbCommand cmd = null;
  5. try {
  6. sqlStmt = "insert into Emp (FirstName,LastName,Date) Values (?,?,?) ";
  7. conString = "Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost";
  8. cn = new OleDbConnection(conString);
  9. cmd = new OleDbCommand(sqlStmt, cn);
  10. cmd.Parameters.Add(new OleDbParameter("@FirstName", OleDbType.VarChar, 40));
  11. cmd.Parameters.Add(new OleDbParameter("@LastName", OleDbType.VarChar, 40));
  12. cmd.Parameters.Add(new OleDbParameter("@Date", OleDbType.Date));
  13. cmd.Parameters["@FirstName"].Value = txtFirstName.Text;
  14. cmd.Parameters["@LastName"].Value = txtLastName.Text;
  15. if ((txtDate.Text == "")) {
  16. cmd.Parameters["@Date"].Value = DBNull.Value;
  17. }
  18. else {
  19. cmd.Parameters["@Date"].Value = DateTime.Parse(txtDate.Text);
  20. }
  21. cn.Open();
  22. cmd.ExecuteNonQuery();
  23. Label1.Text = "Record Inserted Succesfully";
  24. }
  25. catch(Exception ex) {
  26. Label1.Text = ex.Message;
  27. }
  28. finally {
  29. cn.Close();
  30. }
VB.NET
  1. Dim sqlStmt As String
  2. Dim conString As String
  3. Dim cn As OleDbConnection
  4. Dim cmd As OleDbCommand
  5. Try
  6. sqlStmt = "insert into Emp (FirstName,LastName,Date) Values (?,?,?) "
  7. conString = "Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost"
  8. cn =New OleDbConnection(conString)
  9. cmd =New OleDbCommand(sqlStmt, cn)
  10. cmd.Parameters.Add(New OleDbParameter("@FirstName", OleDbType.VarChar, 40))cmd.Parameters.Add(New OleDbParameter("@LastName", OleDbType.VarChar, 40))cmd.Parameters.Add(New OleDbParameter("@Date", OleDbType.Date))cmd.Parameters("@FirstName").Value = txtFirstName.Text
  11. cmd.Parameters("@LastName").Value = txtLastName.Text
  12. If (txtDate.Text = "") Then
  13. cmd.Parameters("@Date").Value = DBNull.Value
  14. Else
  15. cmd.Parameters("@Date").Value = DateTime.Parse(txtDate.Text)
  16. End If
  17. cn.Open()
  18. cmd.ExecuteNonQuery()
  19. Label1.Text = "Record Inserted Succesfully"
  20. Catch ex As Exception
  21. Label1.Text = ex.Message
  22. Finally
  23. cn.Close()
  24. End Try
The data entered in database: