Desk. Version 2020 R1.2.
Query: select name,area.areaid,said from area Left Join staffArea on area.areaid = staffarea.areaid And staffid = ‘key_goes_here’ order by name
This works fine in Valentina Studio. In Xojo it gets a syntax error.
What I’m trying to do is show a list of “areas.” A person can be assigned to one or more of them. The list would appear in a listbox with checkboxes in column 0. Users would be able to assign (or unassign) areas to a person by clicking the checkbox.
try changing it to areaid only
Both tables have an areaid column. Must include table name.
Again, this works in Valentina Studio.
Thanks for the suggestion.
It looks like there are smart quotes in your query string. This could cause a syntax error. If Valentina cleans them automagically for you that could cause the mismatch in behaviors.
Try this?
var tsSQL as String = "SELECT name, area.areaid, said FROM area LEFT JOIN staffArea ON area.areaid = staffarea.areaid AND staffid = ? ORDER BY name"
var toRS as RowSet = Database.SelectSQL(tsSQL, "TheStaffID")
Tim,
No smart quotes. I pasted your version just below my original. Looks the same. Gets same result.
Thanks for the suggestion.
-Bob
What is the exact error you get?
And what is the exact Xojo code?
And is the staffid an integer or a string?
I have found the problem!
I copied the work query (thanks, Tim) and the one that generates the syntax error into BBEdit.
In BBEdit there is a space between the " and the s in Select. It won’t paste here (or appear in Xojo). It’s probably some control character. That caused the problem. I deleted the beginning of my query and retyped it. Things are working as advertised.
Thanks to all for the suggestions.
Stay safe.
Just for reference - this used to happen to me a lot.
I would prototype SQL in Valentina Studio, then cut-and-paste the working phrase into Xojo code only to find it refusing to work. I used to kick myself going around in circles, then realising it was the same problem over and over again. They all needed to be pasted into a raw text editor first, or just re-typed direct into Xojo using the visual state of the paste as a template because of embedded hidden characaters.
This has reduced massively in later releases of VS (in fact I can’t remember the last time it happening to me now), so you might want to ensure that you are using the current release of Valentina as it may be something they spotted and took care of.
Craig,
Will do. I hope this helps others.
-Bob
