Deleting a database row in ListBox

Getting Started
1

Hi, I am new to this and have been going through the XoJo beginners programming guide. I am at the part about creating the AddressBook database/Interface. The guide shows how to add rows to the listbox but not delete a row. I figured out how to RemoveRowAt with a selected row in the list box and a delete button pressed event. But this does not remove the record. Please help. I did try reverse engineering the “INSERT INTO” but no luck.
Thank you in advance for any guidence.

2

You need to issue an SQL command to tell the database to delete the record. Like: “Delete from mytable where id=7”. You can use db.executeSQL to issue the command.

1 Like
3

Link ?

I figured out how to RemoveRowAt with a selected row in the list box and a delete button pressed event. But this does not remove the record.

Of course, you do not asked the database to remove, only the displayer (ListBox).

In short, you mixed ListBox (it is a Displayer) and the Database (SQLite ?).

As already adviced, you need to remove the Record from the Database (not the Row from the ListBox), then, clear the ListBox and populate it again (the deleted Record will not appear).

4

Hi Maximilian. Thank you for your response. I am working through the XoJo programming guide found here. Xojo: Learn Xojo Programming
I created the addressbook application and can successfully add records to the database, showing in the listbox. I want to l earn how to delete an entry. I tried the following in a delete button pressed event after selecting the line item I want to delete.

If AddressBox.SelectionRowIndex <> -1 Then
Var row As Integer = AddressBox.SelectedRowIndex
AddressBox.RemoveRowAt(row)
End If

This does work as expected and I now realize that I have to take an additional step to remove the row from the database I just don’t know the correct syntax or how to incorporate it in the above Delete button pressed event.

5

Hi Emile. Thank you for your response. I am working through the XoJo programming guide found here. Xojo: Learn Xojo Programming
I created the addressbook application and can successfully add records to the database, showing in the listbox. I want to l earn how to delete an entry. I tried the following in a delete button pressed event after selecting the line item I want to delete.

If AddressBox.SelectionRowIndex <> -1 Then
Var row As Integer = AddressBox.SelectedRowIndex
AddressBox.RemoveRowAt(row)
End If

This does work as expected and I now realize that I have to take an additional step to remove the row from the database I just don’t know the correct syntax or how to incorporate it in the above Delete button pressed event.

6

As this OP’s question illustrates, that the example links listbox and database is a very bad idea, in that it makes a newbie imagine that these two things have something to do with each other, whereas in fact they have nothing to do with each other. So, not unnaturally, a newbie might think that deleting a row from the lisrbox also deletes it from the database, which is not the case.

I’ve not looked at database examples recently, ut a sensible set of examples would show how to reat a table, add a row to it update and delete the row

7

Apologies for typos but I’m on a train.

8

to create and use a delete sql statement you need to store the table id in the list view row tag or cell tag.

https://documentation.xojo.com/api/databases/sqlitedatabase.html

2 Likes
9

Or any other value or string that uniquely identifies the row in teh database that this row in the listbox corresponds to - assuming you are in fact using a listbox to present the database data to the user. You don’t, of course, have to use a listbox at all.

10

You have page 215 an explanation of what a Record is.

Look at the ID description as this is what you need to use to modify or delete a specific Record.
This is the paragraph before (and after) the image of a key.

An example of how to delete a Record from the Database appears at page 219:

DELETE FROM people WHERE id = 2

You have a definition of SQL Delete there (with more syntax)*:
SQLDelete

  • Notably deleting a Record using First Name and Family Name (but, remember, two people can havee the same, and this is why the Record ID is important: it is unique to each Record).
11

btw
few databases support returning id in the insert statement at the end so you get direct the id.
means the select sql method return a row set with a single row.

12

The OP talk about:

And in that case, this is SQLite (check Introduction to Programming With Xojo, page 213, yellow rectangle).

Is it relevant ?

13

In fact, the case is easier than it looked at the title.

Here’s the code to put in a button:

// 
// Delete the selected entry
// 

Var sql As String

If AddressBox.SelectedRowIndex = 0 Then Return

sql = "DELETE FROM addressbook WHERE name='" + AddressBox.CellTextAt(AddressBox.SelectedRowIndex, 0) + "';"

Try
  MyDatabase.ExecuteSQL(sql)
  
  NameField.Text = ""
  EmailField.Text = ""
  NameField.SetFocus
  Populate
Catch error As DatabaseException
  MessageBox(error.Message)
End Try

BTW: I do not used ID.

Because there is a space in the Name Field, I had to add a single quote before and after the passed NameField (sql code).

To do that, I simply duplicate the Add button, remove the top lines (keep the Try … End Try lines).

I added a test for a selected line in the AddressBox (ListBox) to avoid troubles.

Now, you have room to add a Modify button.

14

Hi Emile. Thank you so much. I will give that a try! :grinning: