CODEWITHSUNDEEP
c#datetimesqlparameter
JeremyVm
JeremyVm

Reputation: 405

Passing null as SQLParameter DateTime value

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

Upvotes: 15

Views: 45322

Answers (5)

SetFreeByTruth
SetFreeByTruth

Reputation: 847

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

SqlParameter moFrom1Param;

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

Upvotes: 4

Diomedes
Diomedes

Reputation: 658

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.

Upvotes: 0

iDevForFun
iDevForFun

Reputation: 988

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

Upvotes: 17

Guillaume
Guillaume

Reputation: 13128

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

Upvotes: 10

Davide Piras
Davide Piras

Reputation: 44605

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;

Upvotes: 1

Related Questions