11

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?

Samet ÖZTOPRAK
  • 3,112
  • 3
  • 32
  • 33
Eric
  • 654
  • 5
  • 16
  • did you try `null` instead of `DBNull.Value`? Not sure but might work. – sawbeanraz Aug 08 '18 at 16:19
  • You did not share what the 2 errors were when "this fails". If the column allows nulls, DBNull will work. Also, you should use `Add` rather than `AddWithValue` especially with SqlIte so you can control the datatype it perceives. – Ňɏssa Pøngjǣrdenlarp Aug 08 '18 at 16:54
  • @sawbeanraz, I tried `null`, read my question. @Plutonix, both `null` and `DBNull.Value` yield the same error which I listed above. I just tried `Add()` with an explicit data type and got the same result. The target field is nullable, but that's moot anyway as the SQL isn't executed, the problem is on the parameter binding. – Eric Aug 08 '18 at 18:22
  • Despite what the documentation says, I'd be tempted to avoid AddWithValue anyway: see [can we stop using AddWithValue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). I could've sworn I saw a question earlier which gave a hint why this might be happening for null but I can't find it. – Richardissimo Aug 08 '18 at 18:31
  • @Richardissimo, point taken, but tangential to my question; I get the same error with the `Add()` method. – Eric Aug 08 '18 at 19:01
  • @Eric Is it possible that you have defined the column to be NOT NULL which is causing the issue. – sawbeanraz Aug 09 '18 at 18:28

1 Answers1

18

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.

Eric
  • 654
  • 5
  • 16