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.
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.
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")
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.
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.