SQLite Insufficient parameters supplied to the command

Question

By adding a parameter to the query, the error raised on the DB.Fill(DS) line. The parameter @id is exactly equal to the parameter of the query. I've search a lot but all solution is about typo of parameter name.

SQLiteDataAdapter DB;      
DataSet DS = new DataSet();
DataTable DT = new DataTable();

SetConnection();
sqlCmd = sqlCon.CreateCommand();
string CommandText = "select * from Words where id = @id"; //query;
await sqlCon.OpenAsync();

sqlCmd.Parameters.AddWithValue("@id", 1);
DB = new SQLiteDataAdapter(CommandText, sqlCon);

DS.Reset();
await Task.Run(() => { DB.Fill(DS); });
DT = DS.Tables[0];
sqlCon.Close();
return DT;

UPDATE = Solution As @steve16351 said, the problem is with CommandText. Just add the query to sqlCmd and send it as SQLiteDataAdapter parameter.

sqlCmd = sqlCon.CreateCommand();
sqlCmd.CommandText("select * from Words where id = @id"); //query; <<==  
await sqlCon.OpenAsync();
sqlCmd.Parameters.AddWithValue("@id", 1);
DB = new SQLiteDataAdapter(sqlCmd); <<==

steve16351 · Accepted Answer · 2018-07-08 09:12:41Z

3

You're not using the SQL command that you've added the parameter to, you're passing the SQLiteDataAdapter the command text directly. Use the overload that takes the command object:

sqlCmd.CommandText = "select * from Words where id = @id"; //query;
sqlCmd.Parameters.AddWithValue("@id", 1);
DB = new SQLiteDataAdapter(sqlCmd);

And here is a more complete example:

using (var conn = new SQLiteConnection(@"Data Source=c:\temp\mydbthatdoesnotexistyet.db;Version=3;"))
{
    conn.Open();

    // create the words table
    var create = conn.CreateCommand();
    create.CommandText = "CREATE TABLE Words (Id INT, Word TEXT)";
    create.ExecuteNonQuery();

    // insert data to the table
    var populate = conn.CreateCommand();
    populate.CommandText = "INSERT INTO Words (Id, Word) VALUES (@Id, @Word)";
    populate.Parameters.AddWithValue("@Id", 1);
    populate.Parameters.AddWithValue("@Word", "abc");
    populate.ExecuteNonQuery();

    // select the data back from the table
    var select = conn.CreateCommand();
    select.CommandText = "SELECT Word FROM Words WHERE Id = @Id";
    select.Parameters.AddWithValue("@Id", 1);

    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(select);
    DataSet data = new DataSet();
    dataAdapter.Fill(data);
    DataTable table = data.Tables[0];
}

edited Jul 8, 2018 at 9:12

answered Jul 8, 2018 at 8:56

steve16351

5,8572 gold badges21 silver badges33 bronze badges

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

2 Comments

Behzad Over a year ago

this error raised: System.NullReferenceException: 'Object reference not set to an instance of an object.' even I have added this line: sqlCmd = new SQLiteCommand();

steve16351 Over a year ago

I've added a complete, working minimal example, perhaps that will help you see where the problem is. Alternatively, add your new code to your question.

Gustav · Accepted Answer · 2018-07-08 08:41:22Z

0

Like is used for text comparison. You just need a match or not, thus:

string CommandText = "select * from Words where id = @id";

answered Jul 8, 2018 at 8:41

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

1 Comment

Behzad Over a year ago

Thanks for answering but it's not the answer. It raises the error again.

Ali Tooshmalani · Accepted Answer · 2018-07-08 08:56:30Z

0

Like :

   string CommandText = "select * from Words where id = $id"; //query;

after :

   sqlCmd.Parameters.AddWithValue("$id", 1);

answered Jul 8, 2018 at 8:56

Ali Tooshmalani

2602 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQLite Insufficient parameters supplied to the command

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related