System.InvalidOperationException: Value must be set. Setting Null Parameters for SQLite

Question

I am using Microsoft.Data.Sqlite 2.1.0 on .NETStandard 2.0 and .NET Core 2.1.0 to interact with a local SQLite database. SQLitePCL is mentioned in the exception and is also a dependency.

I want to be able to set a parameter's value to NULL but when I do that, I get an exception that the parameter's "value must be set".

SqliteCommand cd = cn.CreateCommand();
cd.CommandText = sql;
cd.Parameters.AddWithValue("@param1", null); //This fails
cd.Parameters.AddWithValue("@param2", DBNull.Value); //This also fails
cd.Parameters.AddWithValue("@param3", ""); //This insert an empty string, not a NULL
cd.ExecuteNonQuery(); //The exception is thrown on this line

Full exception:

{System.InvalidOperationException: Value must be set.
  at Microsoft.Data.Sqlite.SqliteParameter.Bind (SQLitePCL.sqlite3_stmt stmt) [0x0004c] in <56cfa09aae23467e945f1a64a1f893bb>:0 
  at (wrapper remoting-invoke-with-check) Microsoft.Data.Sqlite.SqliteParameter.Bind(SQLitePCL.sqlite3_stmt)
  at Microsoft.Data.Sqlite.SqliteParameterCollection.Bind (SQLitePCL.sqlite3_stmt stmt) [0x00017] in <56cfa09aae23467e945f1a64a1f893bb>:0 
  at (wrapper remoting-invoke-with-check) Microsoft.Data.Sqlite.SqliteParameterCollection.Bind(SQLitePCL.sqlite3_stmt)
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x0025d] in <56cfa09aae23467e945f1a64a1f893bb>:0 
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader () [0x00000] in <56cfa09aae23467e945f1a64a1f893bb>:0 
  at MyApp.DbHelper.BuildDataSet (Microsoft.Data.Sqlite.SqliteCommand cd) [0x00019] in C:\...\MyApp\DbHelper.cs:55 }

According to the official documentation, the value "can be null."

I've tried creating a new SqliteParameter and setting the value to null there, thinking perhaps there's an issue with AddWithValue(), but that yields the same result.

How do I set a SqliteParameter's value to NULL?

Answer 1

I've had more experience with SQLite now and have settled on an answer through experience.

I'm unsure now of the exact situation that was causing me problems when I originally posted this question.

Contrary to the opening question, this does indeed work:

cd.Parameters.AddWithValue("@param2", DBNull.Value);

A straight-up null will throw the value must be set exception. If a parameter value is null, I am now detecting that and converting it to DBNull.Value in a wrapper class.

This solution has been very reliable for me for the the past month.