Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1232
  • Last Modified:

Insert Null into DateTime column in SQL from C#

trying to insert null value into datetime column in sql server but getting

Insert Unsuccessful on Record 0. Error Message The conversion of a varchar data
type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

tried DBNull.Value also couldnt :<
SqlDateTime dtLeaveEarlierBy = SqlDateTime.Null;
                                                                        sqlcmd.CommandText = "INSERT INTO StudentAttendance (attendancestatus, lateduration, actualtime, arrivedtime, leavetime, actualleavetime, leavestatus, earlierduration, absensereason, leaveearlierby, markby, datecreated, datemodified) VALUES ('" + dsSAA.Tables[0].Rows[0]["attendancestatus"].ToString() +
                                                                        "', '" + dsSAA.Tables[0].Rows[0]["lateduration"].ToString() + "', '" + Convert.ToDateTime(dsSAA.Tables[0].Rows[0]["actualtime"]) + "', '" + Convert.ToDateTime(dsSAA.Tables[0].Rows[0]["arrivedtime"]) + "', '" + Convert.ToDateTime(dsSAA.Tables[0].Rows[0]["leavetime"]) + "', '" + Convert.ToDateTime(dsSAA.Tables[0].Rows[0]["actualleavetime"]) +
                                                                        "', '" + dsSAA.Tables[0].Rows[0]["leavestatus"].ToString() + "', '" + dsSAA.Tables[0].Rows[0]["earlierduration"].ToString() + "', '" + dsSAA.Tables[0].Rows[0]["absensereason"].ToString() + "', " + dtLeaveEarlierBy + ", " + Convert.ToInt64(dsSAA.Tables[0].Rows[0]["markby"]) + ", '" + Convert.ToDateTime(dsSAA.Tables[0].Rows[0]["datecreated"]) +
                                                                        "', '" + Convert.ToDateTime(dsSAA.Tables[0].Rows[0]["datemodified"]) + "')";
                                                                        sqlcmd.ExecuteNonQuery();

Open in new window

0
doramail05
Asked:
doramail05
  • 2
1 Solution
 
nixkuroiCommented:
You should be able to just leave the column name and value out of the insert statement and it will insert a null by default.  
0
 
Dale BurrellDirectorCommented:
I would recommend using SqlParameters to carry out your update. They take all the hard work out of it for you.

However as you are using a string you need to use null in the string e.g.

"insert into MyTable (MyField) values (null)"

Obviously that need to fit in with the rest of your code. I don't think setting the variable to sql null as you are doing helps in this case (it would if you used an SqlParameter).  

Instead of "', " + dtLeaveEarlierBy + ", "

you want

either "', null, " or "', " + (SomeTest ? "null" : "Some Other DateTime Value") + ", "
0
 
Kevin CrossChief Technology OfficerCommented:
It is because you are passing this into a string versus using SqlParameter passed to your SqlCommand.
You can either switch to parameters and use DBNull.Value OR you can use an IF condition to concatenate "NULL" or "', " + dtLeaveEarlierBy + ", " based on condition IsDBNull(dtLeaveEarlierBy).
0
 
Kevin CrossChief Technology OfficerCommented:
Ha. dale_burrell beat me to it. :) I would go with what he said. ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now