Procedure or function expects parameter, which was not supplied. A nullable column throwing exception
Solution 1
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;
}
Solution 2
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);
Solution 3
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.
Learning Curve
Updated on July 21, 2022Comments
-
Learning Curve almost 2 years
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
-
Learning Curve almost 11 yearsI m getting an error of 'Type of conditional expression cannot be determined because there is no implicit conversion between 'int?' and 'System.DBNull'
-
Heslacher almost 11 yearsSo this
shortcircuit
or however this assignment is named won`t work in this case so you need to use the classic if else. Updated my answer. -
Learning Curve almost 11 yearsThank you very much it works fine now. Really appreciate your help.
-
codeMonkey almost 7 yearsThis fixed my problem, but only because in my stored proc I had
@paramname int NULL
instead of@paramname int = NULL
d'ohh. With the equals-sign you can pass innull
from C# all day long. -
Eric over 4 yearsOr with slightly more markup but no extra method:
cmd.Parameters.Add(new SqlParameter("@VoucherCode", SqlDbType.VarChar)).Value = (object)Value ?? DBNull.Value;