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

Did you see what actual T-SQL is being executed? (ie with SQL Profiler). The ToString probably puts in the ''. But your saying there's a space ' ', which i cant see how it could happen. More likely sbcId != "", it's == " ". — RPM1984
– RPM1984, Commented Oct 12, 2010 at 9:36
Thanks RPM1984. I am using DB2. It seems that using ToString() was somehow translated into ' ' inside the database column. — Zo Has
– Zo Has, Commented Oct 12, 2010 at 9:49

Klaus Byskov Pedersen · Accepted Answer · 2010-10-12 09:37:44Z

25

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)

answered Oct 12, 2010 at 9:37

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

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

3 Comments

Zo Has Over a year ago

Thanks klausbyskov. That worked wonderfully ! =). Do you have any hint why was the column in database having value ' ' even if the string had ''.

jd0x0021 Over a year ago

Why is pictureBox1.Tag != null ? ImageOperations.ImageToBytes(pictureBox1.Image) : DBNull.Value throwing: Feature 'target-typed conditional expression' is not available in c# 7.3. Please use language version 9.0 or greater.

jd0x0021 Over a year ago

I had to do an if else for this in order to work. I'm using .net framework. Is this for .net core only?

Jon Hanna · Accepted Answer · 2010-10-12 09:56:19Z

10

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

edited Oct 12, 2010 at 9:56

answered Oct 12, 2010 at 9:36

Jon Hanna

114k10 gold badges151 silver badges258 bronze badges

3 Comments

Zo Has Over a year ago

Thanks Jon. You are right only problem being the casting into string.

Jon Hanna Over a year ago

Ah. Added something on the casting.

Zo Has Over a year ago

Thanks, that was also helpful. I +1 you as answer is already marked.

Preet Sangha · Accepted Answer · 2010-10-12 09:40:35Z

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

alt text

answered Oct 12, 2010 at 9:40

Preet Sangha

65.7k20 gold badges152 silver badges227 bronze badges

3 Comments

Zo Has Over a year ago

Thanks Preet although the problem is solved however I am puzzled why was the database column having ' ' even though the string was '' ?

Preet Sangha Over a year ago

what is the ddl for the table? Does it have a trigger?

LokizFenrir Over a year ago

@ZoHas , because the single quotes he used is text as declared by using the double quotes and tainted the result, when using string format you do not need to encapsulate the variable {0} in quotes... ie. Console.WriteLine("{0}", DBNull.Value.ToString()); would have been just fine.

Pranay Rana · Accepted Answer · 2010-10-12 09:36:36Z

1

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

answered Oct 12, 2010 at 9:36

Pranay Rana

177k37 gold badges244 silver badges267 bronze badges

1 Comment

Zo Has Over a year ago

That won't work with ternary operator but I fixed the problem. Thank you Pranay.

Joost00719 · Accepted Answer · 2017-10-04 08:30:11Z

1

SQLString.Null should do the job just fine :)

answered Oct 4, 2017 at 8:30

Joost00719

7741 gold badge5 silver badges23 bronze badges

Comments

JMIII · Accepted Answer · 2017-10-20 12:48:38Z

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

Debasis Mondal · Accepted Answer · 2015-11-25 11:55:35Z

0

If you Insert a non string value in the access database you may write the insert query in this way

Insert into tableName (column1,column2) values (NULL,NULL);

But you want to insert null value in Short Text or Long Text field in access you may write the insert query in this way

Insert into tableName (column1, column2) values('','');

answered Nov 25, 2015 at 11:55

Debasis Mondal

658 bronze badges

Comments

Zepto sys · Accepted Answer · 2016-02-12 20:24:05Z

-3

your string like that

string value = "";

now add following line

 if (value == ""){

  value = "NULL";
     }

now insert in db and check you have null value their be logical

answered Feb 12, 2016 at 20:24

Zepto sys

54 bronze badges

Collectives™ on Stack Overflow

How to insert null into database?

8 Answers 8

3 Comments

3 Comments

3 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

3 Comments

3 Comments

3 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related