I cannot find the mentioned Syntax Error here, probably I have a blind spot? The database exception is:
db.ExecuteSQL(“INSERT INTO Characters (Character_Information) VALUES ('” + txtCharacterInfo.text + "’ WHERE Character_Name = " + TextFieldCharacterName.text + “’ )”)
single quotes are missing after “WHERE Character_Name =”
db.ExecuteSQL(“INSERT INTO Characters (Character_Information) VALUES ('” + txtCharacterInfo.text + "’ WHERE Character_Name = '" + TextFieldCharacterName.text + “' )”)
André:
this code is SQL injection - Wikipedia prone.
Thanks for letting me know, but I am at a stage that I am still trying to get this kind of code to work, still very complicated for me.
Likely not a worrisome issue for a stand alone desktop app. Perhaps you could suggest an alternative since Andre is struggling with the basic syntax?
Yes, it’s a desktop app for a single user.
Still getting the syntax error, starting myself blind on those little quotes…
db.ExecuteSQL(“INSERT INTO Characters (Character_Information) VALUES ('” + txtCharacterInfo.text + "’ WHERE Character_Name = " + TextFieldCharacterName.text + “’ )”)
https://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-prepare
Explanations and how to code there.
Also, when you get the needed answer, you can mark the entry had a solution, so people know and eventually stop answering.
Did you code the part you copy the shared data base file to a location it will be used ?
For the current thread. Use a command line string will greatly help you:
SQL_Cmd = "INSERT INTO Characters(…"
Then, BEFORE db.ExecuteSQL:
System.DebugLog SQL_Cmd
And you will get/see the SQL Command: it will be easier to understand where the single quote is missing as already adviced.
Try this:
const kSQL = "INSERT INTO Characters (Character_Information) VALUES (?) WHERE Character_Name = ?"
db.ExecuteSQL(kSQL, txtCharacterInfo.Text, TextFieldCharacterName.Text)
The SQL with ?s in it is called a prepared statement, and this prevents you from suffering effects of SQL injection or having to sanitize the user input yourself.
Also, you can make code on the forum readable as code by using the </> button above the post editor or surrounding the code in three backticks ```
The position of the single quotes is incorrect.
db.ExecuteSQL("INSERT INTO Characters ('Character_Information') VALUES ('" + txtCharacterInfo.text + "') WHERE Character_Name = '" + TextFieldCharacterName.text + "'")
Edit: But @Tim_Parnell has the better solution using a prepared statement.
Thanks all, I did read the chapters on the preferred statements to avoid problems, but didn’t practise it yet. I first want to understand the basic use of sqlite commands.
Using a prepared statement is basic use of SQLite commands and might even solve your problem here. If it were a whole extra thing to learn like they used to be in API 1.0, I might not have suggested it. API 2.0 makes all of your queries prepared, so you should take advantage of that.
For example, if your characters name just happened to be McLovin' then everyone’s suggestions here will fail. Prepared statements would handle the value correctly for you.
that part was wrong, carefully look at open and close brackets
I edited it like 3 times then saw Tim’s post and just stopped since it was the better solution.
somehow the reply was to andré but this forum software tag you Tom.
I tried the above solutions, but kept on getting the same syntax error. However after tearing my eyes out and randomly making changes I got this to work:
db.ExecuteSQL("INSERT INTO Characters (Character_Information) VALUES ('" + txtCharacterInfo.text + "’ WHERE Character_Name = " + TextFieldCharacterName.text + "')")
Getting some coffee now 
You also have a parenthesis in the wrong place. That last one that’s inside the quotes needs to be just after the values like this:
db.ExecuteSQL(“INSERT INTO Characters (Character_Information) VALUES ('” + txtCharacterInfo.text + "’) WHERE Character_Name = '" + TextFieldCharacterName.text + “’”)
But more importantly… INSERT statements don’t normally have WHERE clauses on them. An UPDATE would, but when you are adding brand new rows to a table there’s no need for a WHERE statement.
Yes, I am aware of that, but I am trying to get text field info placed in the same record as a new character I added before that. Right now, that info is placed at a new last record row. So by using the WHERE command I am trying to get that info with the correct Character name. Not working still though... Yes, it’s a mess…
My advice about using a SQL_Cmd string and reporting it using System.DebugLog will aboid loosing your vision because you will be able to read exactly what the SQLite Plugin receive.
Please, follow the advice, then judge if it was pertinent.
If the advice is too dark, ask for clarification.
First, that must be in a transaction (chaining interdependent instructions isolated).
Second, all records must have a ROWID (another concept you must learn)
Third, to reference and get the last ROWID inserted, use the function last_insert_rowid() in that transaction
Fourth, to change a previous inserted record, use UPDATE and not INSERT.