CODEWITHSUNDEEP
c#sqldatetimenull
MarsOne
MarsOne

Reputation: 2186

How to pass a NULL value to Stored Procedure

I am passing a DateTime Param to my Stored Procedire which inserts this value along with other valus in a TABLE.

The code i use for passing this variable is 

cmdProc.Parameters.AddWithValue("@pi_TDC_DATETIME_STAMP", Convert.ToDateTime(Param[7]));

Now this Array 'Param gets its value from a TSV file and in some of the rows of the file the datetime is blank which is Ok. So i would need to pass a null variable. But my code fails as soon as it hits such an entry and this is the exception it throws up.

String was not recognized as a valid DateTime. at System.DateTime.Parse(String s, IFormatProvider provider) at System.Convert.ToDateTime(String value)

I know for sure that the code and the Stored procedure to insert values works because it inserts 10 values and only breaks when it hits an empty DATETIME COLUMN. How can i overcome this issue

Upvotes: 0

Views: 99

Answers (3)

Marco Alves
Marco Alves

Reputation: 2856

DateTime? dtVariable = null;
if(!string.IsNullOrEmpty(Param[7]))
dtVariable = Convert.ToDateTime(Param[7]);
cmdProc.Parameters.AddWithValue("@pi_TDC_DATETIME_STAMP", dtVariable);

Upvotes: 1

Steve
Steve

Reputation: 216253

Supposing that your Param array contains strings then

cmdProc.Parameters.AddWithValue("@pi_TDC_DATETIME_STAMP", 
                                string.IsNullOrWitheSpace(Param[7])? 
                                DBNull.Value : Convert.ToDateTime(Param[7]));

(BEWARE, the answer from Habib is more correct because it handles also the invalid date time situation, this answer is good only if you are sure that you have blanks (or whitespaces) or valid datetime strings representations for your locale)

Upvotes: 1

Habib
Habib

Reputation: 223207

use DateTime.TryParse to check if there is a valid date, otherwise pass DBNull.Value

DateTime dt;
if (DateTime.TryParse(Param[7], out dt))
{
    cmdProc.Parameters.AddWithValue("@pi_TDC_DATETIME_STAMP", dt);
}
else
{
    cmdProc.Parameters.AddWithValue("@pi_TDC_DATETIME_STAMP", DBNull.Value);
}

Upvotes: 2

Related Questions