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

[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

 

[Guest]

Eugene,

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

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

Kerry Moorman

 

[W.G. Ryan eMVP]

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();

 

[Bernie Yaeger]

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