I just encountered a tricky and subtle syntax error occurring in SQLITE, and it's solution. I'm absolutely certain others will have been driven mad by this at some point, so I'm explaining it here to prevent others throwing things against the wall in frustration or - even worse - giving up on a project.
Many SQL statements have literals in them and you can use either single or double quotes as per the syntax. The inverted comma / apostrophe key and the quote mark key are each a single character, but in many applications other than a simple notepad they will often be converted to a directional version - either an 'opening' quote or a 'closing' quote for example, that is angled slightly one way or the other. These directionalised quote marks / inverted commas are NOT valid literal identifiers in SQLITE, but they can be hard to spot in text that is a small font size.
In my case I decided to move the direct SQL commands I'd put in a series of text strings within App Inventor to CSV files I could import and run so I would be more easily able to edit them during development. So I used LibreOffice Calc to create the CSV files and first copied and pasted the individual SQL commands I'd written directly within the app for use with Simple Sqlite into LibreOffice Calc, then exported the CSV files and uploaded them to App Inventor. Everything worked swimmingly.
Then when I decided to rearrange the field order I accidentally deleted a quote mark on one of the lines within a cell in LibreOffice Calc and had to re-enter the quote mark. Unfortunately, LibreOffice Calc decided to make it a directionalised quote mark instead of keeping it a standard one. When I'd uploaded the updated CSV file to App Inventor, SQLITE didn't understand that line, but confusingly came up with a syntax error pointing to a comma instead.
A very useful tool for testing SQLITE commands is SQLITEONLINE.COM which enables you to test a series commands on your own personal database by creating files and then running other commands on those files. On this website, text recognised as reserved words comes up as orange capitals and literals should come up as green text so the content is not interpreted. This was where I eventually realised why other almost identical commands in the same file were working, but one particular command wasn't, and that's when I spotted the directionalised quote mark. I am now certain I had the same problem when I used single quotes but I solved it by accident on that occasion.
If the text-editing part of an app you're using to create SQL commands insists on directionalising inverted commas and quote marks, either use a different app or copy and paste a non-directional one that already exists.
