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?

try [`dbnull`](https://learn.microsoft.com/en-us/dotnet/api/system.dbnull?view=netframework-4.8) ! — TaW, Dec 13 '19 at 14:54
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. — , Dec 13 '19 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, Dec 13 '19 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, Dec 13 '19 at 15:07

score 3 · Accepted Answer · answered Dec 13 '19 at 14:58

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.

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; }` — joebody's business, Dec 13 '19 at 15:02

Nguyễn Văn Phong · Answer 2 · 2019-12-13T15:11:45.727

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 '19 at 15:11

answered Dec 13 '19 at 15:00

Nguyễn Văn Phong

13,506
17
39
56

Inserting Null into a database with C#

2 Answers2