Sqlite "Update" C# Syntax Error

Question

Hi following Code gives a Syntax Error.I don't know how to fix the Problem.

The Error

{"SQLite error\r\nnear \"Mytext\": syntax error"}

My Code

string dataSource = "Database.s3db";
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + dataSource;
connection.Open();
SQLiteCommand command = new SQLiteCommand(connection);
command.CommandText = ("update Example set Info ='" + textBox2.Text + ", Text ='"+textBox3.Text + "where ID ='" + textBox1.Text +"'");
command.ExecuteNonQuery();

To avoid this kind of errors (and SQL injections), you should use query parameters, not create the SQL command string dynamically. — svick
– svick, Commented Mar 4, 2012 at 15:37
@user1248067: Please don't use the accepted answer as-is. You should really, really use parameterized SQL. — Jon Skeet
– Jon Skeet, Commented Mar 4, 2012 at 15:47

Jon Skeet · Accepted Answer · 2017-04-06 15:11:34Z

30

Others have suggested alternative ways of constructing the SQL, but you shouldn't be including the values in the SQL at all. You should be using a parameterized query, which avoids SQL injection attacks amongst other things.

It's not immediately clear to me which driver you're using, but assuming it's the Devart.com one, the documentation for SQLiteCommand.Parameters gives a good example of how to do this. In your case, the code would become something like:

string dataSource = "Database.s3db";
using (SQLiteConnection connection = new SQLiteConnection())
{
    connection.ConnectionString = "Data Source=" + dataSource;
    connection.Open();
    using (SQLiteCommand command = new SQLiteCommand(connection))
    {
        command.CommandText =
            "update Example set Info = :info, Text = :text where ID=:id";
        command.Parameters.Add("info", DbType.String).Value = textBox2.Text; 
        command.Parameters.Add("text", DbType.String).Value = textBox3.Text; 
        command.Parameters.Add("id", DbType.String).Value = textBox1.Text; 
        command.ExecuteNonQuery();
    }
}

edited Apr 6, 2017 at 15:11

answered Mar 4, 2012 at 15:42

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

1 Comment

sapbucket Over a year ago

I'm not sure if it is a version issue; but I had to use "DbType.String" in place of "SqLiteType.Text". I'm using SQLite version 1.0.99

David McEleney · Accepted Answer · 2020-10-25 10:16:22Z

1

So, use the above answer as parameterised SQL is best practice.

But, to answer your question on syntax - there's two issues:

command.CommandText = ("update Example set Info ='" + textBox2.Text + ", Text ='"+textBox3.Text + "where ID ='" + textBox1.Text +"'");

Here, you're not closing the single quote after you've written the set Info ='" + textBox2.Text + ", Text

It should be: set Info ='" + textBox2.Text + "', Text

^^ with a closing ' after the double quote.

You've made same mistake with textBox3.

Also, Text ='"+textBox3.Text + "where

There's no space before the where keyword.

Tip: when having errors like this, output SQL to console & inspect string constructed. But parameterised is best approach.

answered Oct 25, 2020 at 10:16

David McEleney

3,9052 gold badges29 silver badges35 bronze badges

Collectives™ on Stack Overflow

Sqlite "Update" C# Syntax Error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related