Error on null values (when format is date)

Dealy

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.

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kirtan Patel17y ago
Accepted 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 Patel17y ago
@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 :)
Dealy17y ago
That's the solution i used. The combination of Kirtan and jinge solution.

Thank you for all the help.
Master Billa17y ago
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 Patel17y ago
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
jinge17y ago
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.
Dealy17y ago
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"?
jinge17y ago
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.