Filling a popup menu with rows error

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 )
end If

Remove the point behind AddRow

1 Like
PopStoryID.AddRow rs.Column("Story_Title").StringValue

Thanks, that part was indeed an error. However now I am getting another one:

Screenshot 2022-11-13 at 15.07.26

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:

Screenshot 2022-11-14 at 07.51.27

rs = app.DB.SelectSQL("SELECT * FROM CHARACTERS INNER JOIN STORIES on CHARACTERS.fkstory = 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 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 and and take note of the parts about passing values as parameters.

You guys beat me to it.

1 Like
...  PopStoryID.SelectedRowValue.ToString

Even with titles without a bracket, I get this error:

Screenshot 2022-11-14 at 12.03.12

Placing quotes around the code didn’t solve it. I agree though that I should use the 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.

Screenshot 2022-11-14 at 12.08.40

store the 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.rowtag(listbox.LastAddedRowIndex) = rowset(rowid)

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("").IntegerValue // DesktopPopupmenu is zero-based
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 :slight_smile:

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.