Out of Bounds Error?

Databases
1

Hi there.

I am getting an out of bounds exception, when I run this sqlite command:

deleteTheRecordString = "Delete From ToDoTasks where Task like '"+ToDoWindow.TaskListBox.CellTextAt ( ToDoWindow.TaskListBox.SelectedRowIndex,1) +”'"

My command appears to be ok; I run a similar command in my SQL DB manager, and it works fine, so I don’t think the - format- (SQLite command proper) is correct.

Any ideas?

Regards

2

In some cases can be -1

You must assure proper values.

3

Hi Rick.

Yes I saw that when I did a debug.
But if I select a row in a listbox, should not the selected row index be the number of the row I selected?
It does not appear to be…

But I have an idea of something to try.
Will advise after I try it.

Stay tuned.

4

Please take a look at SQL prepared statements - you’re just about to run headlong into quoting frustrations and potential security vulnerabilities.

1 Like
5

Before any row is selected the SelectedRowIndex is -1 and your code may get executed prematurely, i.e. before any selection was made. I’ve learned to code defensively and never assume that SelectedRowIndex will be greater than or equal to 0, but explicitly test whether it really is.

6

see also
RequiresSelection flag
constant NoSelection

https://documentation.xojo.com/api/user_interface/desktop/desktoplistbox.html

example of using a parameter in query

rowsFound = db.SelectSQL(“SELECT * FROM Customer WHERE PostalCode=?”, PostalCode.Text)

https://documentation.xojo.com/api/databases/mysqlcommunityserver.html#mysqlcommunityserver-selectsql

7

No, it’s one less. First row has index 0.

8

One could say that 0 is the number of the first row …

But that’s not the issue here. Any code that directly passes the result of SelectedRowIndex to CellTextAt (or indeed any of the …At methods) is suspect as it assumes that this result is the number of a selected row – when in fact it could also be -1, as an indication of no row being selected.

9

Well indeed. As it is if clicking in the scrollbar or beyond the last actual row of the listbox.

10

Ok.
It’s official.
I’m getting old.

This line:

TaskListBox.RemoveRowAt(TaskListBox.SelectedRowIndex)

Was in the wrong place in my delete events.
So the Row was getting deleted too soon - so of course I came up with a -1 (Out of Bounds)

Sorry for being a chimp…

But I am going to have to learn Prepared Statements…

Thank everyone for their time….

:smiley:

11

This is an easy one :slight_smile:
Take for example your current Code:

deleteTheRecordString = "Delete From ToDoTasks where Task like '"+ToDoWindow.TaskListBox.CellTextAt ( ToDoWindow.TaskListBox.SelectedRowIndex,1) +”'"

would become:

// db is your Database Connection
db.ExecuteSQL("Delete From ToDoTasks where Task like ?", ToDoWindow.TaskListBox.CellTextAt (ToDoWindow.TaskListBox.SelectedRowIndex,1) )

See the 2nd Example for the ExecuteSQL Statement Command here: Database — Xojo documentation

1 Like
12

Actually you aren’t. WIth the API2 database methods, there’s essentially nothing to learn.

1 Like
13

Caution: this can hold -1 (If No Selection)

15

This was already mentioned by @Rick_Araujo in the first reply in this thread, and my post is exclusively about switching to PreparedStatements… :pensive_face:

And simply pointing it out without suggesting a solution isn’t very helpful. :neutral_face:

Whatever. Just to make you happy:

If ToDoWindow.TaskListBox.SelectedRowIndex <> DesktopListBox.NoSelection Then
  db.ExecuteSQL("Delete From ToDoTasks where Task like ?", ToDoWindow.TaskListBox.CellTextAt (ToDoWindow.TaskListBox.SelectedRowIndex,1) )
End If

And please don’t mention that it’s better to assign a method to the window instead of directly accessing a window object, or that even with SQLite you should pay attention to the encoding in case the data comes from an external source, and so on. That’s not what this Thread is about… :wink:

3 Likes