C# ASP.Net Parameters.AddWithValue rejecting null value for parameter
Solution 1
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);
Solution 2
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);
}
Solution 3
Try to pass in DBNull.Value
instead of null
.
Solution 4
You need to declare on everything - even if it's null.
Use DBNull.Value for MiddleInitial
.
cmd.Parameters.AddWithValue("@MiddleInitial",DBNull.Value);
Solution 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);
}
}
Susan
Newbie ASP.Net developer having just received a 2-year degree from a technical college. Attempting to build commercial application using: ASP.Net 4.0, C#, membership provider, SQL, Linq to Entities. I am about 3/4 complete.
Updated on June 21, 2022Comments
-
Susan about 2 years
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(); }