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
– Damien_The_Unbeliever, Commented Sep 21, 2011 at 9:55

iDevForFun · Accepted Answer · 2011-09-21 10:11:27Z

17

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, 2011 at 10:11

answered Sep 21, 2011 at 9:53

iDevForFun

9986 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JeremyVm Over a year ago

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 Over a year ago

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

iDevForFun Over a year ago

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

Guillaume · Accepted Answer · 2011-09-21 09:58:45Z

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, 2011 at 9:58

answered Sep 21, 2011 at 9:53

Guillaume

13.2k3 gold badges44 silver badges49 bronze badges

4 Comments

JeremyVm Over a year ago

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

JeremyVm Over a year ago

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.

Guillaume Over a year ago

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

Christian Davén Over a year ago

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

SetFreeByTruth · Accepted Answer · 2014-01-27 21:31:23Z

4

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

SqlParameter moFrom1Param;

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

answered Jan 27, 2014 at 21:31

SetFreeByTruth

8438 silver badges23 bronze badges

Comments

Davide Piras · Accepted Answer · 2011-09-21 09:55:17Z

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, 2011 at 9:55

Davide Piras

44.7k10 gold badges103 silver badges150 bronze badges

Comments

Diomedes · Accepted Answer · 2013-11-26 19:49:14Z

0

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.

answered Nov 26, 2013 at 19:49

Diomedes

65811 silver badges25 bronze badges

Collectives™ on Stack Overflow

Passing null as SQLParameter DateTime value

5 Answers 5

3 Comments

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related