Passing NULL values to SqlCommand.Parameters.AddWithValue

  • Thread starter Thread starter Giammarco
  • Start date Start date
G

Giammarco

Hi All,

I have the following code:

SqlCommand sqlCmd = new SqlCommand(sqlStatment, dbConn);
sqlCmd.Parameters.AddWithValue("@Name", name);
sqlCmd.Parameters.AddWithValue("@Surname", surname);

If surname is NULL I get the following error message:

Parameterized Query xyz expects parameter Surname which was not
supplied.

Do you know any workaround? I basically would like to pass NULL values
to the SQL statment, instead of writing different SQL statment
depending on what values the user provides.

Thanks,
Giammarco
 
This may work
C# Solution

sqlCmd.Parameters[0].IsNullable = true;
sqlCmd.Parameters[1].IsNullable = true;

let me know
 
Thanks guys,
I've solved in the following way:

SqlCommand sqlCmd = new SqlCommand(sqlStatment, dbConn);
sqlCmd.Parameters.AddWithValue("@Name", name);
sqlCmd.Parameters.AddWithValue("@Surname", surname);
// I have more parameters here

foreach (SqlParameter Parameter in sqlCmd.Parameters)
{
if (Parameter.Value == null)
{
Parameter.Value = DBNull.Value;
}
}

Hope it's the best way to do this!

Regards,
Giammarco Schisani
 
Hey,
Just an update on the issue: I am using the following workaround:

private SqlMoney? money;
public SqlMoney? Money
{
get { return money; }
set { money = value; }
}

if (Money != null)
{
sqlCmd.Parameters.AddWithValue("@Money", Money);
}
else
{
sqlCmd.Parameters.AddWithValue("@Money", DbNull.Value);
}


Hope it helps!
Giammarco
 
sqlCmd.Parameters.AddWithValue("@Surname", ((surname == null) ? (object)DBNull.Value ? (object)surname));
 
Back
Top