How can I insert a null value into Sql Server date column using asp.net and command parameters

Question

I have the following code:

SqlDateTime sqldatenull = SqlDateTime.Null; 
sSql = "INSERT INTO mydb.dbo.myTable(FromPeriod) Values (@FromPeriod)";

if (sFromPeriod.Length == 0) {
    cmd.Parameters.Add("@FromPeriod", SqlDbType.DateTime);
    cmd.Parameters["@FromPeriod"].Value = sqldatenull;
} else {
    cmd.Parameters.AddWithValue("@FromPeriod", sTempFromPeriod);
}

cmd.ExecuteNonQuery();

sFromPeriod is a date and when it's length is zero, I receive error: Conversion failed when converting date and/or time from character string. When sFromPeriod has a value, the code works fine. I have also used DBNull.Value in place of sqldatenull and received the same error. What am I missing?

Are you aware that sFromPeriod and sTempFromPeriod is not the same thing? — Andrew Savinykh
– Andrew Savinykh, Commented Nov 17, 2013 at 21:43

slfan · Accepted Answer · 2013-11-17 21:59:07Z

2

Consider using DBNull.Value instead of SqlDateTime.Null.

For example:

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

edited Nov 17, 2013 at 21:59

slfan

9,129115 gold badges69 silver badges81 bronze badges

answered Nov 17, 2013 at 21:41

Jairy

213 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I insert a null value into Sql Server date column using asp.net and command parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related