Is week1dFXscore TEXT instead of INTEGER?
Yes Ralph, they’re all text
sqlStatement = “UPDATE userScores SET week1dFXscore = ‘3’ WHERE teamName = ‘Chalk Talk’” causes the same error
I, once, put the UPDATE string in the Clipboard, then paste it in a text Editor, then enlarge the text to a size of 24 to be sure I correctly be able to watch what the text is. Sometimes, ith my old eyes, I do not see correctly the text and skip an error ( instead of " the other day in this forum).
Or put the UPDATE string in a TextArea/TextField, then copy/paste elsewhere to enlarge its size
What I also get, but no error was reported, was nothing ! The cure was to invert the order of appearance of the text (but in a complex SELECT string). It was as simple as that tens of minutes lost for this simple error of mine.
Do 100+ must be inside the ’ string ? or WHERE 100+ 'Years of Killing Dreams' ?
(or another variation ?)
Make sure those are straight quote characters, not curly ones.
Why not just use a PreparedStatement?
UPDATE userScores SET week1dFXscore = ?, week1dPHscore = ?, week1eHBscore = ? WHERE teamName = ?I am sure it will help finding the issue and protects you from various kinds of issues and possible threats.
Strange. I tried this code in Valentina Studio:
UPDATE userScores
SET
week1dFXscore = '3'
WHERE teamName = '100+ Years of Killing Dreams'
and it worked for either team name. So the WHERE clause doesn’t seem to be the culprit. Must be something else going on.
I was just going to suggest this as well. I’ve been burned by this many times until I changed my Mac default to use straight quotes.
Ralph: you were using curly quotes on purposed ???
Never noticed that the Mac was set that way.
Tadpole has code to automatically turn “smart quotes” off within the app to circumvent that problem
Apology accepted, but I have no idea where you got that idea from, any assumptions that we made were based on the lack of information in the original post.
Could you answer the following?
- What version of Xojo are you using and on what platform?
- If this is a web project, are you seeing the error during development or in production or both?
- If its a web project and you’re only seeing it in production, what platform are you deployed on?
Could you add the following code after the problem SQLExecute and paste the responses back here?
system.DebugLog("error=>" + YourBDVariable.ErrorMessage + "<")
system.DebugLog("sql=>" + sqlStatement + "<")
system.DebugLog("encode=>" + encodehex(sqlStatement) + "<")
SQLite is very forgiving, I’ve only been able to replicate the near “WHERE”: syntax error message when there is a missing value after an = that happens to be just before a WHERE, which doesn’t seem the case from what you’ve posted so far so I’m as stumped as everyone else here.
Normally when SQLite says “error near XYZ” the error is “usually” (but not 100% of the time) to the LEFT of the indicated “word”
Try to erase the entire SQL statement and retype it letter by letter into the code editor. Don’t paste it from some other source. This way you can make sure you don’t have any invisible characters in your statement (which is what Greg was hinting at).
THANKS JULIAN
1.What version of Xojo are you using and on what platform? 2017r3 (2018r1 is broken).
2.If this is a web project, are you seeing the error during development or in production or both? I’m just doing development at this point. I see it during debugging/testing.
3.If its a web project and you’re only seeing it in production, what platform are you deployed on? I’m using Windows 10, IE 11. Chrome is the same.
Please note that all the columns are “text”
What can I do with the hex version of the statement?
My statement is code generated and I’ve never had an issue with the quotes.
Tried that Max.
I’ve used the teamName “Chalk Art” in place of “100+ Years of Killing Dreams” … got the same error.
John, have you tried the query in a database editor as mentioned earlier? I’ve ran into some query issues over the years where I could only find the problem with trial and error in a database editor.
If all the columns are TEXT then shouldnt all the values be in quotes?
Merv . . . what do you mean by a database editor? The only one I know of is the window in Xojo. It doesn’t seem to do anything anymore.
Like Navicat or Dave’s Tadpole that was mentioned before.
It just tells us the makeup of the string so we can see if there are any odd characters inside that shouldn’t be there.
John could you post the data as text and not screenshots?
Please don’t take this the wrong way as some users don’t know the feature exists, if you click this icon at the bottom of the ide it will show you information that has been system.debuglog in text format that you can copy/paste without using alerts from the website:
Oh and could you also post the lines of code that generate the sqlstatement string all the way down to the sqlexecute in code blocks?
Thanks.
P.S. Don’t be afraid to ask a question on something you are unsure about, no one here judges, we’re just trying to get to the bottom of your problem so you can get back to your project 
Here’s the image from my last post, it seems my ipad broke it =\
