Listbox Row delete error

General
1

I cannot figure out what is going wrong in this code. It is exactly the same as in other windows where I delete a listbox row, but in this window, I get the DB Error: near “5”: syntax error
What is wrong in this code? I am trying to get this fix for 2 days now.

Try
  //Delete selected idea from database, then reload the data to the listbox
  var index_selected as integer
  
  LocationID_Selected = ListBoxStoryLocations.CellValueAt(ListBoxStoryLocations.SelectedRowIndex,1)    
  
  if index_selected >-1 then
    app.db.ExecuteSQL("DELETE FROM Locations WHERE ID = " + LocationID_Selected)
  else
    MessageBox("Sorry, but there are NO Locations left to remove.")
  end if
  
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
  //If the TABLE does not exist, you will get the **DB Error: near "5": syntax error**

End Try

//Refresh the listbox
RefreshLocationsListbox

End Select
2

Is LocationID_Selected an integer? If so, you need to add .ToString to it.

If not, I suggest you make a temporary variable like this:

  if index_selected >-1 then
Var sql as string = "DELETE FROM Locations WHERE ID = " + LocationID_Selected
    app.db.ExecuteSQL(sql)
  else
    MessageBox("Sorry, but there are NO Locations left to remove.")
  end if

and then use the debugger to see what is in sql.

3

You say:

then in your code you have this comment:

are you sure the Locations table exists?
are you sure LocationID_Selected is getting a valid ID?
are you using the correct db?
can you create a sample project/db to share so others can test?

Not related to your problem but why you do this?

4

Looks like there is nothing in sql:

Screenshot 2023-09-08 at 15.50.42
Screenshot 2023-09-08 at 15.50.42703×242 37.9 KB

5

Did you change your code and used Tim’s code?

6

Yes, the table exists, the Add Location code works correctly and shows up in the table.

You are right, this below code looks weird. I added that to see if there is a row selected, but that’s not the way to do this. I will change it. Thanks.

var index_selected as integer //this will be 0
...
if index_selected >-1 then
7

Where were you stopped at that point? On the ExecuteSQL? Also, as @AlbertoD hinted at, are you confusing index_selected and LocationID_Selected ?

8

It stopped at this point, I shows the wrong screenshot:
Screenshot 2023-09-08 at 15.55.25

9

Yes, I did. The weird thing is that it always has 1 as value, no matter which row I try to delete.

10

Then it looks like every row in that listbox has the string ‘1’ in column 1.

1 Like
11

It looks like we need a sample project/db to give you more help.

12

I figured it out. The variable LocationID_Selected was empty all the time and it looks like it’s because it was declared in both the Property of the window and in a Module. I didn’t know actually it was there. So I assume it was resetting the value all the time back to 0.

13

Not so much resetting, as permanently using the wrong one.