C# ASP.Net Parameters.AddWithValue rejecting null value for parameter

I am populating tables using a stored procedure. The table allows a ‘null’ for the middle initial of the name, but I’m getting the following error message:

Procedure or function ‘uspInsertPersonalAccountApplication’ expects parameter ‘@MiddleInitial’, which was not supplied.

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can add to project and use following extension method:

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

Method 2

There are two options here:

Modify you stored procedure and make @MiddleInitial param optional (which is currently not optional that’s why error is thrown)

@MiddleInitial nvarchar(10) = NULL

Or add following line to your code:

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

Method 3

Try to pass in DBNull.Value instead of null.

Method 4

You need to declare on everything – even if it’s null.

Use DBNull.Value for MiddleInitial.

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

Method 5

I created an extension method to battle 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);
    }
}

Method 6

add the parameter for MiddleInitial also with Null 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();
    }

Method 7

hey you have to set with store procedure

@MiddleInitial varhcar(8) = null

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0