Adding parameters in SQLite with C#

Question

Im just learning SQLite and I can't get my parameters to compile into the command properly. When I execute the following code:

this.command.CommandText = "INSERT INTO [StringData] VALUE (?,?)";

this.data = new SQLiteParameter();
this.byteIndex = new SQLiteParameter();

this.command.Parameters.Add(this.data);
this.command.Parameters.Add(this.byteIndex);

this.data.Value = data.Data;
this.byteIndex.Value = data.ByteIndex;

this.command.ExecuteNonQuery();

I get a SQLite Exception. Upon inspecting the CommandText I discover that whatever I'm doing is not correctly adding the parameters: INSERT INTO [StringData] VALUE (?,?)

Any ideas what I'm missing?

Thanks

Can you post the exception you are getting? – JP Alioto Apr 30 '09 at 21:46 — JP Alioto, Apr 30 '09 at 21:46

score 91 · Answer 1 · answered Apr 30 '09 at 21:48

91

Try a different approach, naming your fields in the query and naming the parameters in the query:

this.command.CommandText = "INSERT INTO StringData (field1, field2) VALUES(@param1, @param2)";
this.command.CommandType = CommandType.Text;
this.command.Parameters.Add(new SQLiteParameter("@param1", data.Data));
this.command.Parameters.Add(new SQLiteParameter("@param2", data.ByteIndex));
...

answered Apr 30 '09 at 21:48

Björn

29,019
9
65
81

11

Now, you can do this: `this.command.Parameters.AddWithValue("@param1", data.Data);` `this.command.Parameters.AddWithValue("@param2", data.ByteIndex);` – skst Apr 20 '17 at 00:33
@skst Please don't use .AddWithValue. See http://www.dbdelta.com/addwithvalue-is-evil/ and https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ and another one: https://dba.stackexchange.com/questions/195937/addwithvalue-performance-and-plan-cache-implications – Mary Aug 22 '19 at 14:26
1

@VinGarcia .CommandType helps the database determine whether this is a Text command or the name of a stored procedure. It save the db from having to parse the command. – Mary Aug 22 '19 at 14:28
1

@Mary, you make a valid point, but to be fair, SQLite has only a small subset of the types SQL Server provides, so there's much less chance of a mismatch. SQLite's implementation of `AddWithValue()` only has to decide between _NULL_, _REAL_, _INTEGER_, _TEXT_, and _BLOB_. I suppose it might pick _INTEGER_ when it should pick _REAL_, but that would probably be discovered pretty quickly. Still, yes, it's probably better to use `Add()` and specify the type. As for specifying the `CommandType`, _Text_ is the default type, so there's no need to set it. (There are no stored procedures in SQLite.) – skst Aug 24 '19 at 01:05
@skst Re: CommandType I was just trying to answer the VinGarcia's question in comments. Your comment about the default as .Text completes the answer. Thanks. Good point about limited types in Sqlite but I think I will stick to .Add. It is just what I am used to. :-) – Mary Aug 24 '19 at 01:27
Agreed. I might go back and change my `AddWithValue()` calls to `Add()` since I do prefer strong types. :-) I saw that you were answering VinGarcia's question, but I thought it'd be useful to point out that it's not required because there's no choice other than _Text_ in SQLite (at least for now). – skst Aug 24 '19 at 01:32
@Mary "don't use AddWithValue" as a blanket advice should be avoided.. "Avoid using AddWithValue if your DB is SQL Server" is better; [MySQL actively advocate and promote its use](https://mysqlconnector.net/overview/using-addwithvalue/) for example – Caius Jard Aug 18 '20 at 08:36
@CaiusJard I see that your link is specifically aimed at MySqlConnector. I have been using MySql.Data.MySqlClient. I asked whether avoiding the use of .Add() applies to just MySqlConnector or both providers in several places but never received an answer. I am hoping you can shed some light on this and provide me with a reference. Thank you. – Mary Feb 07 '21 at 23:43

score 30 · Accepted Answer · answered Apr 30 '09 at 21:47

30

Try VALUES instead of VALUE.

answered Apr 30 '09 at 21:47

score 0 · Answer 3 · answered Sep 14 '20 at 21:09

var connectionstring = "Data Source=" + Environment.CurrentDirectory + "\\game.db";
using (var connection = new SqliteConnection(connectionString))
{
    var insertCmd = connection.CreateCommand();
    insertCmd.CommandText = "INSERT INTO Results(result, win) VALUES (@prizes, @win)";
    insertCmd.Parameters.AddWithValue("prizes", prizes);
    insertCmd.Parameters.AddWithValue("win", winAmount);
    insertCmd.ExecuteNonQuery();
}

Adding parameters in SQLite with C#

3 Answers3

Linked