CODEWITHSUNDEEP
c#asp.netsqlstored-procedures
Susan
Susan

Reputation: 1832

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

Upvotes: 7

Views: 12798

Answers (7)

Tony
Tony

Reputation: 1937

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

Upvotes: 1

IUnknown
IUnknown

Reputation: 22448

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

Upvotes: 5

Shai
Shai

Reputation: 25619

You need to declare on everything - even if it's null.

Use DBNull.Value for MiddleInitial.

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

Upvotes: 1

Sanjay Goswami
Sanjay Goswami

Reputation: 1386

hey you have to set with store procedure

@MiddleInitial varhcar(8) = null

Upvotes: 0

Marcin
Marcin

Reputation: 3262

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

Upvotes: 4

PraveenVenu
PraveenVenu

Reputation: 8337

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

Upvotes: 0

Andomar
Andomar

Reputation: 238086

Try to pass in DBNull.Value instead of null.

Upvotes: 3

Related Questions