CODEWITHSUNDEEP
c#sql-server
angfreak
angfreak

Reputation: 1003

How to insert null in datetime type column in sql server

I have a table in which there are some datetime type columns. I use a stored procedure to insert values into the table. In the stored procedure I have variables that accept null for inserting into the table. My problem is when I try to insert a null value into my table's datetime column it throws error. my code is as.

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

Upvotes: 13

Views: 95173

Answers (11)

Fuat
Fuat

Reputation: 809

You can use this. There i no shortcut!!

if (InsertDate.Text == "") cmd.Parameters.AddWithValue("@enddate", DBNull.Value); else cmd.Parameters.AddWithValue("@enddate", Convert.ToDateTime(InsertDate.Text));

Upvotes: 0

Shanmuga priya
Shanmuga priya

Reputation: 1

Try this logic if DBNull.Value is not worked .. use DateTime? null

Ensure that ur database field should allow null

dtexpiry!=null? dtexpiry :(DateTime?) null

Upvotes: 0

Samad Kakkad
Samad Kakkad

Reputation: 73

try using this 

System.Data.SqlTypes.SqlDateTime.Null

Upvotes: 4

Cuteboy_Max
Cuteboy_Max

Reputation: 131

Try something like this, using Add rather than AddWithValue..

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

Upvotes: 2

zxc
zxc

Reputation: 1526

In the stored procedure do something like this:

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

Upvotes: 0

Rahul
Rahul

Reputation: 91

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

Upvotes: 9

RAJESH KUMAR
RAJESH KUMAR

Reputation: 517

To pass null value 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;
  }

Upvotes: 5

SWeko
SWeko

Reputation: 30882

First of all, to be able to insert null into a database column, the column must accept nulls. Open the table in the designer, and see if the relevant column is nullable or not. If it's not and you try to insert a null value, you'll get the error:

Cannot insert the value NULL into column 'InsertDate', table 'TableName'; column does not allow nulls.

That said, since you are using stored procedures to insert data, I would recommend using default values for the parameters on the procedure level, instead of burdening the aplication code. Than, you can just not set the parameter on the application side, and it would 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 the InsertDate is empty, the parameter is not sent at all.

Upvotes: 3

beastieboy
beastieboy

Reputation: 883

Try using:

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

Upvotes: 0

Lloyd
Lloyd

Reputation: 29668

You should just be able to supply null directly as a parameter value:

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

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

Upvotes: 1

Dr Schizo
Dr Schizo

Reputation: 4352

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

The key being DBNull.Value. I imagine you have a nullable DateTime variable, i.e. DateTime? someVariable. You can test it for null and if it is use DBNull.Value (as shown above).

Upvotes: 17

Related Questions