Passing null as SQLParameter DateTime value

Question

I have the following query:

INSERT INTO CWS_FORWARDING_PROFILE
           (TNR_COMPANY_PROFILE,BOL_FORWARD_MAIL,BOL_FORWARD_SMS,BOL_FORWARD_MESSAGES
           ,DT_MO_FROM1,DT_MO_FROM2,DT_MO_FROM3,DT_MO_TO1,DT_MO_TO2,DT_MO_TO3
           ,DT_TU_FROM1,DT_TU_FROM2,DT_TU_FROM3,DT_TU_TO1,DT_TU_TO2,DT_TU_TO3
           ,DT_WE_FROM1,DT_WE_FROM2,DT_WE_FROM3,DT_WE_TO1,DT_WE_TO2,DT_WE_TO3
           ,DT_TH_FROM1,DT_TH_FROM2,DT_TH_FROM3,DT_TH_TO1,DT_TH_TO2,DT_TH_TO3
           ,DT_FR_FROM1,DT_FR_FROM2,DT_FR_FROM3,DT_FR_TO1,DT_FR_TO2,DT_FR_TO3
           ,DT_SA_FROM1,DT_SA_FROM2,DT_SA_FROM3,DT_SA_TO1,DT_SA_TO2,DT_SA_TO3
           ,DT_SU_FROM1,DT_SU_FROM2,DT_SU_FROM3,DT_SU_TO1,DT_SU_TO2,DT_SU_TO3)

            VALUES(@tnrProfile, @forwardMail, @forwardSms, @forwardMessages,
                    @MoFrom1, @MoFrom2, @MoFrom3, @MoTo1, @MoTo2, @MoTo3,
                    @TuFrom1, @TuFrom2, @TuFrom3, @TuTo1, @TuTo2, @TuTo3,
                    @WeFrom1, @WeFrom2, @WeFrom3, @WeTo1, @WeTo2, @WeTo3,
                    @ThFrom1, @ThFrom2, @ThFrom3, @ThTo1, @ThTo2, @ThTo3,
                    @FrFrom1, @FrFrom2, @FrFrom3, @FrTo1, @FrTo2, @FrTo3,
                    @SaFrom1, @SaFrom2, @SaFrom3, @SaTo1, @SaTo2, @SaTo3,
                    @SuFrom1, @SuFrom2, @SuFrom3, @SuTo1, @SuTo2, @SuTo3);

I add my DateTime parameters as follows:

SqlParameter moFrom1Param = new SqlParameter("@MoFrom1", dTOForwarding.MoFrom1);
            moFrom1Param.IsNullable = true;
            moFrom1Param.Direction = ParameterDirection.Input;
            moFrom1Param.SqlDbType = SqlDbType.DateTime;
            cmd.Parameters.Add(moFrom1Param);

When I execute this, but only give an actual datetime to certain parameters and all the rest is null. So to be clear, all parameters from monday till wednesday have a datetime value. The rest thursday till sunday hasn't. So those are passed as null. I get an error like this:

The parameterized query '(@tnrProfile int,@forwardMail bit,@forwardSms bit,@forwardMessag' expects the parameter '@ThFrom1', which was not supplied.

I have looked for some answers here on stackoverflow and google, but the answers I've found never worked for me..

So my question is, how can I make sure that if my DateTime parameter has null as value, that value is understood by sql and actually passed as null instead of telling me the parameter was not supplied.

Hope someone here can help me.

Thanks.

edit: This is the solution:

SqlParameter moFrom1Param = new SqlParameter("@MoFrom1", dTOForwarding.MoFrom1 == null ?
                (Object)DBNull.Value : dTOForwarding.MoFrom1);
            moFrom1Param.IsNullable = true;
            moFrom1Param.Direction = ParameterDirection.Input;
            moFrom1Param.SqlDbType = SqlDbType.DateTime;
            cmd.Parameters.Add(moFrom1Param);

Rubber

Have you considered normalizing your data so that you only have to store From/To pairs that actually exist, and you can cope with more than 3? – Paedo

Answer 1

SqlParameter moFrom1Param = new SqlParameter("@MoFrom1", dTOForwarding.MoFrom1 == null ? DBNull.Value : dTOForwarding.MoFrom1);
            moFrom1Param.IsNullable = true;
            moFrom1Param.Direction = ParameterDirection.Input;
            moFrom1Param.SqlDbType = SqlDbType.DateTime;
            cmd.Parameters.Add(moFrom1Param);

Ventilate

When I trie this I get following error: "Type of conditional expression cannot be determined because ther is no implicit conversion between 'System.DBNull' and 'System.Data.SqlClient.SqlParameter' – Rubber

Thanks! This helped me to get the correct answer :) Just had to add a cast to DBNull.Value in order to make it work. – Rubber

Surprised it needed a cast but glad you got it sorted anyway :) – Ventilate

Thanks Jeremy. I needed (object)DBNull.Value – Daughterly

Answer 2

Have you tried DBNull.Value ?

SqlParameter moFrom1Param;
if (dTOForwarding.MoFrom1 != null)
    moFrom1Param = new SqlParameter("@MoFrom1", dTOForwarding.MoFrom1);  
else
    moFrom1Param = new SqlParameter("@MoFrom1", DBNull.Value);  

also, your code shows "@MoFrom1" but the error is about @ThFrom1

Cymogene

Guillaume,I've just tried this. It doesn't compile. "Operator '??' cannot be applied to operands of type SqlParameter and DBNull". – Rubber

Guillaume, I pass al the parameters. I just showed how I add 1 parameter. I do the same for all the rest. 47 in total of which 42 are DateTime. – Rubber

Edited my answser to remove the ?? operator and do the logic on the correct value. – Cymogene

This solution worked for me, with nullable DateTime. The other solutions did not work. – Surgy

Answer 3

Use the null coalescing operator ?? in conjuction with DBNull.Value:

SqlParameter moFrom1Param;

moFrom1Param = new SqlParameter( "@MoFrom1", dTOForwarding.MoFrom1 ?? DBNull.Value );  

Aside

Answer 4

it looks like you are not assigning the null value, something like this:

var thFrom1Param = new SqlParameter("@ThFrom1", SqlDbType.SqlDateTime);
thFrom1Param.Value = DBNull.Value;
thFrom1Param.Direction = ParameterDirection.Input;

Polluted

Answer 5

Modifying the stored procedure works, but I think its a bit sloppy.

You can handle it in code, this work for me:

        DateTime? myDate;

    if (TextBoxWithDate.Text != "")
    {
        myDate = DateTime.Parse(TextBoxWithDate.Text);
    }
    else
    {
        myDate = null;
    }

Make myDate DateTime type but nullable, if the value from the text box is null, make myDate null and send it to the stored procedure.

Uird