Error on null values (when format is date)

Question

Hello,

how can I handle null values in the following code? The problem is if user don't fill the textfield (end_date.Text) i get an error message even thought END_DATE database field is nullable.

SqlCommand command = new SqlCommand(sproc, conn);
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@END_DATE",SqlDbType.DateTime).Value = Convert.ToDateTime(end_date); //end_date is a string

Thank you in advance.

Kirtan Patel · Answer

Friend , Use it Like Below Code SqlCommand command = new SqlCommand (sproc, conn); command.CommandType = CommandType .StoredProcedure; string DateTimeText = textBox1.Text.Trim(); if (DateTimeText != "" ) { command.Parameters.AddWithValue( "@END_DATE" , Convert .ToDateTime(textBox1.Text.Trim())); } else { command.Parameters.AddWithValue( "@END_DATE" , DBNull .Value); }

Kirtan Patel · Answer

@master billa if i enter ' ' just blank space your solution will be failed because you have not used Text.Trim(); in following line if (!string.IsNullorEmpty(textBox1.Text)) so if i enter ' ' in textBox it will not insert dbNull instead it will enter Empty String(Space ASCII 32) in Database You should use if (!string.IsNullorEmpty(textBox1.Text.Trim())) to get Optimal Result :)

Dealy · Answer

That's the solution i used. The combination of Kirtan and jinge solution. Thank you for all the help.

Master Billa · Answer

Hi I would like to given best way other than Kirtan. private void CorrectCode() { SqlCommand command = new SqlCommand(sproc, conn); command.CommandType = CommandType.StoredProcedure; if (!string.IsNullorEmpty(textBox1.Text)) { command.Parameters.AddWithValue("@END_DATE", Convert.ToDateTime(textBox1.Text.Trim())); } else { command.Parameters.AddWithValue("@END_DATE", DBNull.Value); } } thank you

Kirtan Patel · Answer

Difference is AddwithValue takes Values as Object while Add Method Enforces to Use the Same SQLdbType DataType to be entered as Database Ensures the TypeSafty in Both the method . in AddWithValue the Error Will be generated at Sql Server if DataType Passed is Mismatched and In Add Method Error will be generated in .net code :) Note :- AddWithValue needs Round trip to Server to Check the Datatype and Add method will generate Error in .net Code without round trip to the data type ( Here in Code i have used AddWithValue to reduce the complexity of program :) ) if application is small its ok to Use AddWithValue but practice to use Add() method in large scale applications

jinge · Answer

Remarks from msdn document: Remarks AddWithValue replaces the SqlParameterCollection.Add method that takes a String and an Object . The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Add overload that takes a String and a SqlDbType enumeration value where passing an integer with the string could be interpreted as being either the parameter value or the corresponding SqlDbType value. Use AddWithValue whenever you want to add a parameter by specifying its name and value. Ps: this forum does not allow to accept more than one post as answer.

Dealy · Answer

Thank you both for your answers. Kirtan, if i may ask what's the difference between Add and AddWithValue? Ps: How can i check both answers as "Answers"?

jinge · Answer

First way, try to check the value of the endate before using Converter: if (!String.IsNullOrEmpty(enddate)) { Convert the string to datetime. } Second, you can use DateTime.TryParse method instead. find the code from this link . I suggest you use the second solution since you can also pass culture information when convert the string to datetime.

Error on null values (when format is date)

Answers (8)