Procedure or function expects parameter, which was not supplied. A nullable column throwing exception

8
votes

I am creating/updating a record in sql db with a stored procedure. I am supplying around 30 parameters from my C# Data Access Layer. sql table has all the columns null able apart from its primary key column. here when i supply a null value to a null able column, it is throwing an exception of "Procedure or function 'spFullUpdate' expects parameter '@App1TAPAYears', which was not supplied.". In my c# code I can clearly see the column is supplied with a null value. Could please anyone tell me how I can rectify this issue. Following is my code snippet. Setting value to the data object is as follow

Dt.TimeAtPreviousAddressYears = int.TryParse(TimeatPreviousAddressYears.Text, out intOut) ? intOut : (int?)null;

following is the nullable property in my entity class

        public int? TimeAtPreviousAddressYears { get; set; }

My data access layer adding parameter code is as follow

cmd.Parameters.Add("@App1TAPAYears", SqlDbType.Int).Value = dataObject.TimeAtPreviousAddressYears;
SqlDataAdapter da = new SqlDataAdapter(cmd);
                conn.Open();
                cmd.ExecuteNonQuery();

It can clearly be seen that the parameter is added and null value is supplied to a null able column but it still producing exception. Anyone's help will really be appreciated.

Kind Regardds

3
c#sqlstored-procedures
Please show the code where you add all the parameters until you execute the SP. ( Edit your post ) - Heslacher
the code is fine as it doesn't produce exception when i supply a value to this parameter. Exception only raised when the value is null. - Learning Curve
just wanted to be sure. Have seen to much questions about parameter not supplied which has been caused by creating the command object again just before the query. - Heslacher
In my case command parameter is created successfully. The problem is with the null value. - Learning Curve
In command parameter list, the IsNullable property of @App1TAPAYears is coming as set to false and Value is 'null'. - Learning Curve

3 Answers

20
votes

nullable != DBNull.Value

So you can't pass (int?)null to the parameter value but instead pass DBNull.Value

Like:

if (dataObject.TimeAtPreviousAddressYears.HasValue) 
{ 
    cmd.Parameters.Add("@App1TAPAYears", SqlDbType.Int).Value =dataObject.TimeAtPreviousAddressYears;
}else
{
    cmd.Parameters.Add("@App1TAPAYears", SqlDbType.Int).Value = DBNull.Value;
}
7
votes

This is a really annoying problem, which threw me out recently because I'd not come across it for a long time.

I settled on adding this in my RepositoryBase class

    protected object ValueOrNull(object value)
    {
        return value ?? DBNull.Value;
    }

and this this in my actual repo code for any optional items

cmd.Parameters.Add(new SqlParameter("@VoucherCode", SqlDbType.VarChar)).Value = ValueOrNull(promo.VoucherCode);
1
votes

You should try with

if (dataObject.TimeAtPreviousAddressYears.HasValue) 
{ 
    cmd.Parameters.Add("@App1TAPAYears", SqlDbType.Int).Value =dataObject.TimeAtPreviousAddressYears;
}else
{
    cmd.Parameters.Add("@App1TAPAYears", SqlDbType.Int).Value = (object)DBNull.Value;
}

The issue is that (as the error message indicates) the conditional expression needs identical types on both branches.