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);

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? — Damien_The_Unbeliever, Sep 21 '11 at 09:55

iDevForFun · Accepted Answer · 2011-09-21T10:11:27.713

16

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);

edited Sep 21 '11 at 10:11

answered Sep 21 '11 at 09:53

iDevForFun

978
6
10

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' – JeremyVm Sep 21 '11 at 09:59
1

Thanks! This helped me to get the correct answer :) Just had to add a cast to DBNull.Value in order to make it work. – JeremyVm Sep 21 '11 at 12:01
Surprised it needed a cast but glad you got it sorted anyway :) – iDevForFun Sep 21 '11 at 12:08
6

Thanks Jeremy. I needed `(object)DBNull.Value` – Ian Nov 20 '17 at 16:47

Guillaume · Answer 2 · 2011-09-21T09:58:45.133

10

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

edited Sep 21 '11 at 09:58

answered Sep 21 '11 at 09:53

Guillaume

12,824
3
40
48

Guillaume,I've just tried this. It doesn't compile. "Operator '??' cannot be applied to operands of type SqlParameter and DBNull". – JeremyVm Sep 21 '11 at 09:57
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. – JeremyVm Sep 21 '11 at 09:58
Edited my answser to remove the ?? operator and do the logic on the correct value. – Guillaume Sep 21 '11 at 09:59
This solution worked for me, with nullable DateTime. The other solutions did not work. – Christian Davén Apr 17 '15 at 09:00

score 3 · Answer 3 · answered Jan 27 '14 at 21:31

3

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

SqlParameter moFrom1Param;

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

answered Jan 27 '14 at 21:31

SetFreeByTruth

819
8
23

score 1 · Answer 4 · answered Sep 21 '11 at 09:55

1

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;

answered Sep 21 '11 at 09:55

Davide Piras

43,984
10
98
147

score 0 · Answer 5 · answered Nov 26 '13 at 19:49

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.

Passing null as SQLParameter DateTime value

5 Answers5

Linked