Sqlite "Update" C# Syntax Error
Solution 1
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();
}
}
Solution 2
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.
user1248067
Updated on July 09, 2022Comments
-
user1248067 almost 2 years
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();
-
sapbucket about 7 yearsI'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