Passing NULL values to SqlCommand.Parameters.AddWithValue

[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

 

[em00guy]

This may work
C# Solution

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

let me know

 

[Miha Markic [MVP C#]]

pass DBNull.Value instead

 

[Giammarco]

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

 

[Mark Ashton]

the parameter IsNullable is an unused DesignTime only property

 

[Giammarco]

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

 

[moonshot]

sqlCmd.Parameters.AddWithValue("@Surname", ((surname == null) ? (object)DBNull.Value ? (object)surname));