How to insert zero in date type column in sql server

Question

I have a table that has some date type columns in it. I am using a stored procedure to insert values ​​into a table. In the stored procedure, I have variables that are null to be inserted into the table. My problem is that when I try to insert a null value into the datetime column of the table, it causes an error. my code is like.

System.Data.SqlTypes.SqlDateTime getDate; //set DateTime null getDate = SqlDateTime.Null; if (InsertDate.Text == "") { cmd1.Parameters["@InsertDate"].Value = getDate; } else { cmd1.Parameters["@InsertDate"].Value = InsertDate.Text; } 

Answer 1

 cmd1.Parameters["@InsertDate"].Value = getDate ?? DBNull.Value; 

DBNull.Value key. I assume that you have a DateTime variable with a null value, i.e. DateTime? someVariable DateTime? someVariable . You can check it for zero, and if it uses DBNull.Value (as shown above).

Answer 2

 if (//some condition) { cmd.Parameters.AddWithValue("@dateofBirth", txtdob.text); } else { cmd.Parameters.AddWithValue("@dateofBirth", DBNull.Value); } 

Answer 3

To pass null to db, you can use DBNull.Value. Please try this

  if (string.IsNullOrWhiteSpace(InsertDate.Text)) { cmd1.Parameters["@InsertDate"].Value =DBNull.Value; } else { cmd1.Parameters["@InsertDate"].Value = InsertDate.Text; } 

Answer 4

First of all, to insert null into the database column, the column must accept null s. Open the table in the designer and see if the corresponding column matches or not. If this is not the case and you try to insert a null value, you will receive an error message:

Cannot insert a NULL value in the column "InsertDate", table "TableName"; column does not allow null.

However, since you use stored procedures to insert data, I would recommend using the default values ​​for parameters at the procedure level, rather than burdening the application code. Then you can simply not set the parameter on the application side, and it will work correctly:

  create procedure InsertIntoTable -- ....other parameters here... @InsertDate datetime = null -- this means that if the value is not supplied, -- null is used by default as begin insert into TableName (InsertDate) values (@InsertDate) end 

and on the C # side, just do:

 if (!string.IsNullOrWhitespace(InsertDate.Text)) { // check to see if the entered text is actually a date DateTime insertDate = ... some parsing/verification code...; cmd1.Parameters["@InsertDate"].Value = insertDate; } 

Note that there is no else branch, so if InsertDate empty, the parameter is not sent at all.

Answer 5

You can simply specify null directly as a parameter value:

 if (String.IsNullOrWhitespace(InsertDate.Text)) { cmd1.Parameters["@InsertDate"].Value = null; } else { cmd1.Parameters["@InsertDate"].Value = InsertDate.Text; } 

Also switches to using String.IsNullOrWhitespace() to check for an empty string.

Answer 6

Try something like this using Add, not AddWithValue.

 DB.Parameters.Add("@date", SqlDbType.DateTime).Value = DBNull.Value; 

Answer 7

In the stored procedure, do the following:

 insert into table(date) values(case when @InsertDate ='' then null else @insertDate end) 

Answer 8

Try using:

 if (string.IsNullOrWhiteSpace(InsertDate.Text)) { cmd1.Parameters["@InsertDate"].Value = getDate; } else { cmd1.Parameters["@InsertDate"].Value = InsertDate.Text; } 

Answer 9

try using

 System.Data.SqlTypes.SqlDateTime.Null