A procedure or function expects a parameter that has not been specified. Exclusive column exception

Question

I am creating / updating a record in sql db with a stored procedure. I supply about 30 parameters from my C # data access level. The sql table has all columns with a null value different from the primary key column. here, when I put a null value in a column with a null value, it throws an exception from the "Procedure or function" spFullUpdate 'expects the parameter' @ App1TAPAYears', which was not specified. ". In my C # code, I clearly see that the column is set to zero. Can someone tell me how I can fix this problem. Below is a snippet of code. The parameter value for the data object is as follows:

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

the following nullable property in an entity class

  public int? TimeAtPreviousAddressYears { get; set; } 

My access code for data access settings is as follows:

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

You can clearly see that the parameter is added and the null value is passed to the column with the null value, but it still throws an exception. Any help would be really appreciated.

Good wishes

Answer 1

nullable != DBNull.Value

Therefore, you cannot pass the (int?)null parameter value, but pass DBNull.Value

how

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

Answer 2

This is a very annoying problem that recently caused me because I have not encountered it for a long time.

I decided to add this to my RepositoryBase class

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

and this is in my actual repo code for any additional elements

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

Answer 3

You should try

 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 problem is that (as the error message indicates) the conditional expression requires identical types for both branches.