How to INSERT pure <NULL> Into SmallDataTime Field (MS SQL Server)

  • Thread starter Thread starter esoroka
  • Start date Start date
E

esoroka

I need to insert NULL into the field with SmallDataTime datatype.
In .NET when I tried to use "" or DBNull.Value it insert "" what
will be automatically converted into '1/1/1900' by SQL Server...
I need to have the Insert statement like this:
INSERT tblTest (Name,smDate) Values ('John',NULL)
But using pure NULL without double quotes ("") is not allowed in .NET.
When I use double quotes ("NULL"), .NET interpreted it as
character string and I got the SQL converting error.
Thank you for the help.
Eugene
 
Eugene,

You might try using SQLDateTime.Null instead of DBNull.Value.

You probably will need to import the System.Data.SQLTypes namespace.

Kerry Moorman
 
I'm not sure what your code snippet looks like - but this one for instance
works as expected (inserting Null in the smalldatetime field) :

SqlCommand cmd = new SqlCommand("INSERT INTO foo123(myid, dt) VALUES(@First,
@Second)", cn);

cmd.Parameters.Add("@First", SqlDbType.Int);

cmd.Parameters["@First"].Value = 20;

cmd.Parameters.Add("@Second", SqlDbType.SmallDateTime);

cmd.Parameters["@Second"].Value = DBNull.Value;

cn.Open();

System.Int32 i = cmd.ExecuteNonQuery();

cn.Close();
 
Hi Eugene,

This worked fine for me:
mrow = dsinvdet.Tables(0).NewRow()

mrow("invnum") = "bbbbb"

mrow("ship_dt") = DBNull.Value

dsinvdet.Tables("invdet").Rows.Add(mrow)

HTH,

Bernie Yaeger
 
Back
Top