C # ASP.Net Parameters.AddWithValue rejects null for parameter

Question

I populate the tables using a stored procedure. The table allows "zero" for the middle initial name, but I get the following error message:

The procedure or function 'uspInsertPersonalAccountApplication' expects the parameter '@MiddleInitial', which was not provided.

Thanks in advance!

public void ProcessForm(string[] InputData) { string ConnString = System.Configuration.ConfigurationManager.ConnectionStrings["AssociatedBankConnectionString"].ToString(); SqlConnection conn = new SqlConnection(ConnString); SqlCommand cmd = new SqlCommand("uspInsertPersonalAccountApplication", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@AccountType", "Savings"); cmd.Parameters.AddWithValue("@AccountSubType", "Savings"); cmd.Parameters.AddWithValue("@ExistingCustomer","No"); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } 

Answer 1

There are two options here:

Modify the stored procedure and make the optional parameter @MiddleInitial (which is currently not optional, so an error occurs)

 @MiddleInitial nvarchar(10) = NULL 

Or add the following line to your code:

 cmd.Parameters.AddWithValue("@MiddleInitial", null); 

Answer 2

You can add to the project and use the following extension method:

 public static SqlParameter AddWithValueSafe(this SqlParameterCollection parameters, string parameterName, object value) { return parameters.AddWithValue(parameterName, value ?? DBNull.Value); } 

Answer 3

Try passing DBNull.Value instead of null .

Answer 4

You need to declare everything, even if it is null.

Use DBNull.Value for MiddleInitial .

 cmd.Parameters.AddWithValue("@MiddleInitial",DBNull.Value); 

Answer 5

I created an extension method to deal with this problem. Marcin's suggestion is also worth considering if you can update the stored procedure.

 cmd.Parameters.AddString("@MyParamName", myValue); public static class SQLExtension { public static void AddString(this SqlParameterCollection collection, string parameterName, string value) { collection.AddWithValue(parameterName, ((object)value) ?? DBNull.Value); } } 

Answer 6

add parameter for MiddleInitial also with zero value

 public void ProcessForm(string[] InputData) { string ConnString = System.Configuration.ConfigurationManager.ConnectionStrings["AssociatedBankConnectionString"].ToString(); SqlConnection conn = new SqlConnection(ConnString); SqlCommand cmd = new SqlCommand("uspInsertPersonalAccountApplication", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@AccountType", "Savings"); cmd.Parameters.AddWithValue("@AccountSubType", "Savings"); cmd.Parameters.AddWithValue("@ExistingCustomer","No"); cmd.Parameters.AddWithValue("@MiddleInitial",DBNull.Value); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } 

Answer 7

Hey you have to install using the storage procedure

 @MiddleInitial varhcar(8) = null