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

You should be able to use DbNull.Value or null directly

Answer 2

Try below by removing ToString() after DBNull.Value

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

Answer 3

Use DBNull.Value, not DBNull.Value.ToString. (With other parameter collection types in .NET just using null would also work, but I'm not 100% sure about OleDbParameter).

As for the tertiary operator. Don't cast to string, but cast the string to object:

sbcId=="" ? DBNull.Value : (object)sbcId

Answer 4

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)

Answer 5

You need to use DBNull.Value not DBNull.Value.ToString()

DBNull.Value.ToString() is ''

This program gives

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("'{0}'", DBNull.Value.ToString());
        }
    }
}