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:
- String was not recognized as a valid DateTime.
- Value of type 'System.DBNull' cannot be converted to 'String'.
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
- System.Data.SqlClient/ System.Data.OleDb
- System.Data.SqlTypes
- Code for System.Data.SqlClient
C#
- string sqlStmt;
- string conString;
- SqlConnection cn = null;
- SqlCommand cmd = null;
- SqlDateTime sqldatenull;
- try {
- sqlStmt = "insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) ";
- conString = "server=localhost;database=Northwind;uid=sa;pwd=;";
- cn = new SqlConnection(conString);
- cmd = new SqlCommand(sqlStmt, cn);
- cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 11));
- cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 40));
- cmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));
- sqldatenull = SqlDateTime.Null;
- cmd.Parameters["@FirstName"].Value = txtFirstName.Text;
- cmd.Parameters["@LastName"].Value = txtLastName.Text;
- if (txtDate.Text == "") {
- cmd.Parameters["@Date"].Value = sqldatenull;
-
- }
- else {
- cmd.Parameters["@Date"].Value = DateTime.Parse(txtDate.Text);
- }
- cn.Open();
- cmd.ExecuteNonQuery();
- Label1.Text = "Record Inserted Succesfully";
- }
- catch(Exception ex) {
- Label1.Text = ex.Message;
- }
- finally {
- cn.Close();
- }
VB.NET
- Dim sqlStmt As String
- Dim conString As String
- Dim cn As SqlConnection
- Dim cmd As SqlCommand
- Dim sqldatenull As SqlDateTime
- Try
- sqlStmt = "insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date) "
- conString = "server=localhost;database=Northwind;uid=sa;pwd=;"
- cn =New SqlConnection(conString)
- cmd =New SqlCommand(sqlStmt, cn)
- cmd.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar, 11))
- cmd.Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar, 40))cmd.Parameters.Add(New SqlParameter("@Date", SqlDbType.DateTime))
- sqldatenull = SqlDateTime.Null
- cmd.Parameters("@FirstName").Value = txtFirstName.Text
- cmd.Parameters("@LastName").Value = txtLastName.Text
- If (txtDate.Text = "") Then
- cmd.Parameters("@Date").Value = sqldatenull
-
- Else
- cmd.Parameters("@Date").Value = DateTime.Parse(txtDate.Text)
- End If
- cn.Open()
- cmd.ExecuteNonQuery()
- Label1.Text = "Record Inserted Succesfully"
- Catch ex As Exception
- Label1.Text = ex.Message
- Finally
- cn.Close()
- End Try
Code for System.Data.SqlClient.
C#
- string sqlStmt;
- string conString;
- OleDbConnection cn = null;
- OleDbCommand cmd = null;
- try {
- sqlStmt = "insert into Emp (FirstName,LastName,Date) Values (?,?,?) ";
- conString = "Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost";
- cn = new OleDbConnection(conString);
- cmd = new OleDbCommand(sqlStmt, cn);
- 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;
- cmd.Parameters["@LastName"].Value = txtLastName.Text;
- if ((txtDate.Text == "")) {
- cmd.Parameters["@Date"].Value = DBNull.Value;
- }
- else {
- cmd.Parameters["@Date"].Value = DateTime.Parse(txtDate.Text);
- }
- cn.Open();
- cmd.ExecuteNonQuery();
- Label1.Text = "Record Inserted Succesfully";
- }
- catch(Exception ex) {
- Label1.Text = ex.Message;
- }
- finally {
- cn.Close();
- }
VB.NET
- Dim sqlStmt As String
- Dim conString As String
- Dim cn As OleDbConnection
- Dim cmd As OleDbCommand
- Try
- sqlStmt = "insert into Emp (FirstName,LastName,Date) Values (?,?,?) "
- conString = "Provider=sqloledb.1;user id=sa;pwd=;database=northwind;data source=localhost"
- cn =New OleDbConnection(conString)
- cmd =New OleDbCommand(sqlStmt, cn)
- 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
- cmd.Parameters("@LastName").Value = txtLastName.Text
- If (txtDate.Text = "") Then
- cmd.Parameters("@Date").Value = DBNull.Value
- Else
- cmd.Parameters("@Date").Value = DateTime.Parse(txtDate.Text)
- End If
- cn.Open()
- cmd.ExecuteNonQuery()
- Label1.Text = "Record Inserted Succesfully"
- Catch ex As Exception
- Label1.Text = ex.Message
- Finally
- cn.Close()
- End Try
The data entered in database: