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?

Your statement taxStr = DBNull.Value; should give you a compile time error. — Habib
– Habib, Commented Feb 7, 2014 at 15:01
if you need to insert null ,just omit that field from you Insert SQL Script(if there is no default value for that field).Can you add your insert script to the post? — Maryam Arshi
– Maryam Arshi, Commented Feb 7, 2014 at 15:02

ekad · Accepted Answer · 2014-02-07 15:12:39Z

23

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));

edited Feb 7, 2014 at 15:12

answered Feb 7, 2014 at 15:01

ekad

14.7k26 gold badges46 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Evan L Over a year ago

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

Manny · Accepted Answer · 2014-02-07 15:01:49Z

4

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.

answered Feb 7, 2014 at 15:01

Manny

9761 gold badge6 silver badges17 bronze badges

Collectives™ on Stack Overflow

Setting an empty string to null on Insert into database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related