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

Question

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

IUnknown · Accepted Answer · 2012-03-01 16:24:01Z

5

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

answered Mar 1, 2012 at 16:24

IUnknown

22.5k4 gold badges41 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcin · Accepted Answer · 2012-03-01 14:45:29Z

4

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

answered Mar 1, 2012 at 14:45

Marcin

3,2935 gold badges34 silver badges50 bronze badges

4 Comments

Susan Over a year ago

I'm passing in 53 paramaters, many of which can be null. How can I dynamically set the DBNull.Value without wrapping each assignment in an IF statement?

Marcin Over a year ago

If there is possible to modify stored procedure, then modify paramateres that can be null, and make them optional in stored procedure declaration. That is most flexible solution. If this is not possible you can use SqlCommandBuilder.DeriveParameters(command); and set DBNull.Value for each of them in loop. And then assign the ones you want.

Rory Over a year ago

See comments to stackoverflow.com/a/9518215/8479: use cmd.ParametersAddWithValue("@MiddleInitial", (object)yourVal ?? DBNull.Value); or add an extension method like AddParamWithValueOrNull()

Kevin Won Over a year ago

I tried the AddWithValue("@param", null); which did not work. setting the defaults in the sproc (i.e. @param datetime = null) fixed it.

Andomar · Accepted Answer · 2012-03-01 14:44:30Z

3

Try to pass in DBNull.Value instead of null.

answered Mar 1, 2012 at 14:44

Andomar

239k55 gold badges387 silver badges412 bronze badges

6 Comments

Susan Over a year ago

I'm passing in 53 paramaters, many of which can be null. How can I dynamically set the DBNull.Value without wrapping each assignment in an IF statement?

Andomar Over a year ago

Add ?? DBNull.Value after the value, or create a function like AddValueWithNonNullParameter that does it for you

Susan Over a year ago

Like this? Intellisense is giving me an error and Intellisense doesn't recognize AddValueWithNullParameter ------- cmd.Parameters.AddWithValue("@MiddleInitial", InputData[11] ?? DBNull.Value);

Andomar Over a year ago

Yeah ?? example should work. If the value before ?? is null, it returns the value after ?? instead. The function AddValueWithNonNullParameter does not exist, you'd have to implement it yourself :)

Andomar Over a year ago

Try ((object) InputData[11]) ?? DBNull.Value ?

|

Shai · Accepted Answer · 2012-03-01 15:00:25Z

1

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

Use DBNull.Value for MiddleInitial.

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

edited Mar 1, 2012 at 15:00

answered Mar 1, 2012 at 14:45

Shai

25.7k9 gold badges47 silver badges69 bronze badges

Comments

Tony · Accepted Answer · 2014-02-02 00:19:22Z

1

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

answered Feb 2, 2014 at 0:19

Tony

1,9671 gold badge25 silver badges25 bronze badges

Comments

PraveenVenu · Accepted Answer · 2012-03-01 14:45:14Z

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

Sanjay Goswami · Accepted Answer · 2012-03-01 15:00:10Z

0

hey you have to set with store procedure

@MiddleInitial varhcar(8) = null

edited Mar 1, 2012 at 15:00

answered Mar 1, 2012 at 14:45

Sanjay Goswami

1,3866 silver badges13 bronze badges

1 Comment

Andomar Over a year ago

+1 This would actually work since the parameter becomes optional

Collectives™ on Stack Overflow

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

7 Answers 7

Comments

4 Comments

6 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

4 Comments

6 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related