How to insert null into database?

Question

Hi I am trying to insert null in a database column depending on a gridview datakeys value (if being "" insert null into database) However, I am getting a space ' ' inside the database column.

string sbcId = gvTest.DataKeys[gr.RowIndex]["myColumn"].ToString();
 insgnp.Parameters.Add(new OleDbParameter("EMPID", (sbcId==""?DBNull.Value.ToString():sbcId)));

Answer 1

I prefer to use an extension method to handle instead of multiple if statements. This allows you to account for null or empty string values. Also allows it to be reusable.

    public static object GetDBNullOrValue<T>(this T val)
    {
        bool isDbNull = true;
        Type t = typeof(T);

        if (Nullable.GetUnderlyingType(t) != null)
            isDbNull = EqualityComparer<T>.Default.Equals(default(T), val);

        else if (t.IsValueType)
            isDbNull = false;

        else if (t == typeof(string) && val != null)
        {
            string temp = val as String;
            isDbNull = (temp == String.Empty);
        }

        else
            isDbNull = (val == null);

        return isDbNull ? DBNull.Value : (object)val;
    }

Then you could use

     Parameters.Add(new OleDbParameter("EMPID", sbcId.GetDBNullOrValue())); 

Answer 2

You have to rewrite your code:

if(string.IsNullOrEmpty(sbcId))
    Parameters.Add(new OleDbParameter("EMPID", DBNull.Value)); 
else
    Parameters.Add(new OleDbParameter("EMPID", sbcId)); 

The problem with the ternary if statement that you have is that its returntype must always be the same, which is why you cannot use it (string and DbNull.Value are not compatible)