Unable to pass null value in string.Format()

Question

I have one ComboBox name as cmbCity which has dataSource as DisplayMember "CityName" (string type) and ValueMember "ValueID" (Number type).

Now for update dataTable I have query

string query = string.Format("Update TableName Set StringTypeColmName = '{0}' , NumberTypeColmName = {1}", 
                             "StringValue", 
                             !string.IsNullOrEmpty(cmbCity.Text) ? cmbCity.SelectedValue : DBNull.Value);`. 

Its work fine if user select value form cmbCity, I get value by cmbCity.SelectedValue by if user dont select any value then I am trying to pass null value in database for that field but after formating string, I get value like

Update TableName Set StringTypeColmName = 'StringValue' , NumberTypeColmName = " 

in this output string null value not present and finally while executing query I got exception Syntax error in UPDATE statement.. Any help how to add null value in output query. I am using MS-Accesss database and VS2010.

Answer 1

You should probably (no, scratch that, definitely) not use string formatting for building parametrised SQL queries. Instead, use the appropriate classes, e.g.:

using (var cmd = new OleDbCommand("Update TableName Set StringTypeColmName = ?, NumberTypeColmName = ?", connection))
{
    cmd.Parameters.AddWithValue("string", "StringValue");
    cmd.Parameters.AddWithValue("num", !string.IsNullOrEmpty(cmbCity.Text) ? cmbCity.SelectedValue : DBNull.Value);

    cmd.ExecuteNonQuery();
}

Since this uses the database driver to handle replacement of the parameters this is actually safe to do, even if a ToString() on a value would result in the empty string.

Answer 2

This should work, you should pass "null" string if the value is not present:

string.Format("Update TableName Set StringTypeColmName = '{0}' , NumberTypeColmName = {1}", 
"StringValue", !string.IsNullOrEmpty(cmbCity.Text) ? cmbCity.SelectedValue.ToString() : "null");