Setting an empty string to null on Insert into database

Question

I haven't been able to find the right solution for this issue, and I know it's so simple but I have forgotten how to do it. I have a form with one textfield field that is not required by the user to fill in. I want to insert NULL into the database, not a 0 which is it currently doing. I am not sure what I am missing, though. The textbox is named taxRateTxt, and what I have currently does not work for me:

try
{
    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AbleCommerce"].ToString()))
    {
        cn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;

        cmd.Parameters.Add(new SqlParameter("@FIPSCountyCode", countyCodeTxt.Text));
        cmd.Parameters.Add(new SqlParameter("@StateCode", stateCodeList.SelectedValue));
        cmd.Parameters.Add(new SqlParameter("@CountyName", countyNameTxt.Text));

        string taxStr = taxRateTxt.Text;
        //test for an empty string and set it to db null
        if (taxStr == String.Empty)
        {
            taxStr = DBNull.Value;

        }

        cmd.Parameters.Add(new SqlParameter("@TaxRate", taxStr));

        if (AddBtn.Visible == true)

            cmd.CommandText = addQuery;

        if (EditBtn.Visible == true)
        {
            cmd.CommandText = editQuery;
        }

        cmd.ExecuteNonQuery();
        cn.Close();
    }

What am I missing here?

Solution 1

Remove this block of code

string taxStr = taxRateTxt.Text;
//test for an empty string and set it to db null
if (taxStr == String.Empty)
{
    taxStr = DBNull.Value;

}

and change this

cmd.Parameters.Add(new SqlParameter("@TaxRate", taxStr));

to this

cmd.Parameters.Add(new SqlParameter("@TaxRate", string.IsNullOrEmpty(taxRateTxt.Text) ? (object)DBNull.Value : taxRateTxt.Text));

Solution 2

Passing in Convert.DBNull (or DBNull.Value) will do it.

http://msdn.microsoft.com/en-us/library/system.convert.dbnull(v=vs.110).aspx

Of course, you'll have to check the string value first and then pass that in instead.

Comments

[Evan L]

Love the casting DBNull.Value to object trick. Saves so much time -)