I am trying to place the values of a sqlite table column in a popup menu, but keep getting a syntax error. What am I doing wrong here?
var rs as Rowset
rs = app.db.SelectSQL("SELECT * FROM Stories ORDER BY Story_Title") // Get all the data from the Stories table.
if rs <> Nil then
For i As Integer = 1 To rs.RowCount
PopStoryID.AddRow.(rs.Column("Story_Title").StringValue )
rs.MoveToNextRow
Next
rs.Close
end If
Remove the point behind AddRow
PopStoryID.AddRow rs.Column("Story_Title").StringValue
Thanks, that part was indeed an error. However now I am getting another one:
probably not related to the period. I will study this more in depth later.
Thanks, I got this error fixed. The next step is now to ONLY see characters of a story in the Character listbox when that Story title is choosen from a drop down menu. For this I am comparing the SelectedRowValue of the drop down menu with contain the Story titles with the Story Title column in the database, but I am getting a syntax error. It does work when I type the story title in the code directly, so I know this works, but when I try to get the title value directly from the drop down value, I see this error:
rs = app.DB.SelectSQL("SELECT * FROM CHARACTERS INNER JOIN STORIES on CHARACTERS.fkstory = STORIES.id where STORIES.Story_Title = " + PopStoryID.SelectedRowValue )
Have a look at the SQL that is causing the error. After “Wars” you have a bracket “(”. You need to put quotes around PopStoryID.SelectedRowValue.
You probably shouldn’t use string values. Save a RowID or identifier and use that instead.
like Beatrix said: don’t use string values to compare in an sql query.
use integer unique ID
what happens if two movies have the same name ? this happens …
You’re not enclosing the STORIES.Story_Title in single quotes, hence the syntax error. A more conventional approach would be to use STORIES.id for your lookup. (What if you have more than one story with the same title?) You could store the ID in the rowtag of the popupmenu.
Either way you should be using parameters for your SQL. It will take care of the single quotes for you, as well as escaping single quotes in the string, and will also help prevent SQL injection attacks.
Check out https://documentation.xojo.com/api/databases/database.html#database-selectsql and https://documentation.xojo.com/api/databases/database.html#database-executesql and take note of the parts about passing values as parameters.
You guys beat me to it.
... PopStoryID.SelectedRowValue.ToString
Even with titles without a bracket, I get this error:
Placing quotes around the code didn’t solve it. I agree though that I should use the story.id value from the db and not the story title to compare with the drop down menu, but I don’t understand how to do this. When I place the story id in the drop down menu (so no title to see), the correct story characters are showing in the listbox, but then it’s not possible to know which stories are available in the drop down menu.
store the story.id in the rowtag of the popup menu
Have a look at the SQL in the debugger and then you will see the problem with the SQL. Your code for the listbox should look like this:
loop over the rows in the rowset
listbox.add(rowset(story))
listbox.rowtag(listbox.LastAddedRowIndex) = rowset(rowid)
next
To populate your popupmenu:
var rs as Rowset
rs = app.db.SelectSQL("SELECT * FROM Stories ORDER BY Story_Title") // Get all the data from the Stories table.
if rs <> Nil then
For i As Integer = 1 To rs.RowCount
PopStoryID.AddRow ( rs.Column("Story_Title").StringValue )
PopStoryID.RowTagAt ( i - 1 ) = rs.Column("STORIES.id").IntegerValue // DesktopPopupmenu is zero-based
rs.MoveToNextRow
Next
rs.Close
end If
To get the rowtag:
Var myTag as Integer
...
myTag = PopStoryID.RowTagAt ( PopStoryID.SelectedRowIndex )
Thanks so much, Thomas. I am still trying to get my head around how to use row tags, but I believe I start to understand the idea now. You connect it to a row simply by adding “.RowTageAt” to the listbox and the value is added by whatever is place after the “=” sign?
Not that difficult now I see the structure 
Using your code makes the compiled app crash, but I need to play around with the code some more to see what is happening. Thanks!
The rowtag takes a Variant so if you need to store multiple values in the rowtag, make a class with those properties and put an instance of the class in the rowtag.
You can think of the RowTag as a hidden column on the listbox. It can hold a Variant, so it can hold data of just about any data type. There are CellTags, CellTagAt(Row, Column), as well if you needed to hold further non-displayed information.