Inserting Null into a database with C#

Question

I'm pretty new to C# and am struggling with the following query.

I'm trying to insert values into a database, but it won't accept null as a value. I need to differentiate between 0 and nothing in this field as it shows whether a calculation has occurred or not.

Here's what I have so far:

System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection();
var connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\Projects\database.accdb";
System.Data.OleDb.OleDbCommand ins = new System.Data.OleDb.OleDbCommand();

objConn.ConnectionString = connStr;
objConn.Open();

ins.Connection = objConn;
ins.CommandText = "INSERT INTO AutomatedDecisionProcess(item1, item2, item3) VALUES(@item1, @item2, @item3)";

ins.Parameters.AddWithValue("@item1", SolveBlanks(row[0]));
ins.Parameters.AddWithValue("@item2", SolveBlanks(row[1]));
ins.Parameters.AddWithValue("@item3", SolveBlanks(row[2]));

ins.ExecuteNonQuery();
objConn.Close();
ins.Dispose();

Console.WriteLine("success?");

and I am trying this function I've written:

public static string SolveBlanks(object number)
{
        string answer = number.ToString();

        if (answer == "")
        {
            answer = null;
        }
        return answer;
}

Any ideas?

What happens when you try to insert null? "it won't work" doesn't provide any indication of what the issue might be. Also, clever username. I like it. — user47589
– user47589, Commented Dec 13, 2019 at 14:55
It's not recommended to use AddWithValue, especially if the value is null this method may not be able to set the correct parameter type. Create and add the parameter yourself instead. — René Vogt
– René Vogt, Commented Dec 13, 2019 at 14:55
@RenéVogt interesting, it's all I've found to solve my queries so far, I'll look into creating parameters, thanks! — joebody's business
– joebody's business, Commented Dec 13, 2019 at 15:07

Nahue Gonzalez · Accepted Answer · 2019-12-13 14:58:33Z

3

Answer from : What is the difference between null and System.DBNull.Value?

This may help you...

Well, null is not an instance of any type. Rather, it is an invalid reference.

However, System.DbNull.Value, is a valid reference to an instance of System.DbNull (System.DbNull is a singleton and System.DbNull.Value gives you a reference to the single instance of that class) that represents nonexistent* values in the database.

*We would normally say null, but I don't want to confound the issue.

So, there's a big conceptual difference between the two. The keyword null represents an invalid reference. The class System.DbNull represents a nonexistent value in a database field. In general, we should try avoid using the same thing (in this case null) to represent two very different concepts (in this case an invalid reference versus a nonexistent value in a database field).

Keep in mind, this is why a lot of people advocate using the null object pattern in general, which is exactly what System.DbNull is an example of.

answered Dec 13, 2019 at 14:58

Nahue Gonzalez

3844 silver badges10 bronze badges

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

1 Comment

joebody's business Over a year ago

Great, thanks for your explanation! I've changed the function and it all works great!

public static object SolveBlanks(object number)         {             object answer = number.ToString();             if (answer.ToString() == "")             {                 answer = System.DBNull.Value;             }             return answer;         }

Nguyễn Văn Phong · Accepted Answer · 2019-12-13 15:11:45Z

0

You should study about DBNull

Let's try the following code:

public static object SolveBlanks(object number)
        {
            object answer = number.ToString();
            if (answer == "")
            {
                answer = DBNull.Value;
            }
            return answer;
        }

edited Dec 13, 2019 at 15:11

answered Dec 13, 2019 at 15:00

Nguyễn Văn Phong

14.2k19 gold badges47 silver badges65 bronze badges

Collectives™ on Stack Overflow

Inserting Null into a database with C#

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related