Delete multiple selected rows from database?

Hi,
I was trying to help someone else, and then I realised that I am also having trouble succeeding in this quest :slight_smile:

If a listbox has multiple selections enabled - how do I delete all the selected listbox rows from the actual database?

This is the code so far - which successfully removes 1 selected row.
Not sure how to loop through and delete ALL the selected entries though???

[code]// ENSURE A LISTBOX ROW IS SELECTED
If Window1.addressbox.ListIndex <> -1 Then

// DATABASE CONNECTION IS SUCCESSFUL, SO EXECUTE THE SQL TO DELETE THE SELECTED ENTRIES
If AddressDB.connect Then
  
  Dim ps As SQLitePreparedStatement = AddressDB.Prepare("DELETE FROM addressbook WHERE name = ? AND email = ?")
  ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
  ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
  ps.Bind(0, Window1.Addressbox.cell(Window1.Addressbox.listindex,0))
  ps.Bind(1, Window1.Addressbox.cell(Window1.Addressbox.listindex,1))
  ps.SQLExecute
  
  // DATABASE WRITE ERROR, SO DISPLAY THE ERROR MSGBOX
  If AddressDB.Error Then
    MsgBox("Error: " + Str(AddressDB.ErrorCode) + " - " + AddressDB.ErrorMessage)
    Return
    
  Else
    // POPULATE THE LISTBOX WITH THE NEW VALUES
    Populate()
    
    // EMPTY THE TEXT FIELDS
    NameField.text = ""
    emailfield.text = ""
    supplierinfofield.text = ""
    
  End If
  
Else
  // ERROR CONNECTING TO DATABASE, SO DISPLAY THE ERROR MSGBOX
  MsgBox("Could not connect to the database - entries not deleted!")
  
End If

End If[/code]

I also have this code (which obviously only deletes the selected listbox rows from the listbox and NOT from the database):

For i as Integer = addressbox.listcount-1 downto 0 If addressbox.selected(i) then addressbox.removeRow(i) End if Next

The trouble I am having is combining both code segments.

Can anyone help?
Thank you all in advance.

First I’d suggest, that you store the actual id of each database record in a rowtag object of the listbox.

Then you create a delete method with an id parameter. From your For-downto loop you call the delete method for each selected row and pass in its stored ID (and not the name and email, which might delete duplicates).

When the delete was successful, then you return true and in your loop you remove the selected row.

Thanks Oliver - I will look into that.

this is one spot where a prepared statement doesn’t help you much :stuck_out_tongue:

it’s much simpler to do something like

 DELETE FROM addressbook WHERE id in (   ... a list of id's here ... )

one hit to the db

now you could create that list in many ways

    • create a temp table in the db ( see http://sqlite.org/lang_createtable.html and use create temporary table)
    • insert each id into that table (using a prepared statement)
    • do DELETE FROM addressbook WHERE id in ( select id from temporary table )
  1. just concatenate together a string - which has its own downsides as far as sql injection - and use that as the list of ids to remove

if you have a lot of rows to delete I’d suspect either of these would be quicker than looping over each row in the list & doing a delete on each one

If your id’s are stored as Integers, and assigned to the RowTag of the ListBox, then you do something like:

dim ids() as String

for rowIndex as Integer = 0 to MyListBox.ListCount - 1
  ids.Append Str(MyListBox.RowTag(rowIndex))
next

db.SQLExecute("DELETE FROM table WHERE id IN (" + Join(ids, ",") + ")")
if db.Error then
  // handle the error
end if

then I think you’re safe from SQL injection, i.e. the data did not come from a user and the data is a type that can not cause a SQL injection error, an Integer.

When you use "… id in " then it may be shorter and faster, but you have less information about a failure. A delete statement may fail on one record but not on all (for instance if one has implemented some business-rules with triggers).

So if you delete those records one by one, then you have more control and more information about each deleted record.

In general I would make use of the rowtag. Create a class with properties like the id of the database record (and maybe more). And then use this id to delete the record, not any other information.

For instance, when I fill a listbox, I add my own rowtag like this:

[code] Dim lb As Listbox = w.lbScreenFields

Dim sql As String = “SELECT GUID,ScreenFieldName,Caption FROM screen_fields ORDER BY ScreenFieldName”

Dim rs As RecordSet
Dim t As imLocalizerRecord // define my rowtag class, to store infos on database record

rs = imDatabase.SQLSelect(sql)

If (imDatabase.Error = True) Then
im.logError("[" + CurrentMethodName + "] ERROR : " + imDatabase.ErrorMessage + ", SQL : " + sql)
Else
While rs <> Nil And Not rs.EOF

  t = New imLocalizerRecord  // Instantiate my rowtag class
  t.Caption         = rs.Field("Caption").StringValue
  t.GUID            = rs.Field("GUID").StringValue
  t.ScreenFieldName = rs.Field("ScreenFieldName").StringValue
  
  lb.AddRow(t.ScreenFieldName)
  lb.RowTag(lb.LastIndex) = t
  
  rs.MoveNext
Wend

End If[/code]

Good point! You have to make a business decision here, “What should fail?” Should the entire operation fail if the user clicks “Delete For This Email” or should only 2 of the 20 records fail? That should be decided on the situation at hand. If you want one or the other, it can be achieved in a few ways including using transactions.

Jeremy,
I am trying your code first of all, here is my completed WORKING code:

[code]Dim ids() As String

For i as Integer = addressbox.listcount-1 downto 0
If addressbox.selected(i) then
ids.Append Str(addressbox.RowTag(i))
End if
Next

// DATABASE CONNECTION IS SUCCESSFUL, SO EXECUTE THE SQL TO DELETE THE SELECTED ENTRIES
If AddressDB.connect Then

AddressDB.SQLExecute("DELETE FROM addressbook WHERE NRef IN (" + Join(ids, ",") + ")")

// DATABASE WRITE ERROR, SO DISPLAY THE ERROR MSGBOX
If AddressDB.Error Then
  MsgBox("Error: " + Str(AddressDB.ErrorCode) + " - " + AddressDB.ErrorMessage)
  Return
  
Else
  // POPULATE THE LISTBOX WITH THE NEW VALUES
  Populate()
  
  // EMPTY THE TEXT FIELDS
  NameField.text = ""
  emailfield.text = ""
  supplierinfofield.text = ""
  
End If

Else
// ERROR CONNECTING TO DATABASE, SO DISPLAY THE ERROR MSGBOX
MsgBox(“Could not connect to the database - entries not deleted!”)

End If[/code]

The question I have is:
What will happen if there is an error deleting any of the rows?

Will the app stop at the first error, then display the MsgBox, then END all other attempts to delete the remaining selected rows?
Or - will it display the MsgBox for the first error, but then still continue on and delete any other remaining rows which need to be deleted?

Thank you.

Since you issued 1 single statement, that 1 single statement will either succeed as a whole or fail as a whole. It’s a good practice to use transactions, which can further make this clear.

As a note, maybe instead of repopulating the list box you should store the row indexes that were added to the delete statement. Then, in the “Else” if the Db.Error if statement, simply remove those indexes. I am assuming what will be left would be the same as your call to Populate(). If you do this, be sure to loop through the row indexes backwards using something like

  for rowIndex as Integer = deleteRowNumbers.UBound downto 0
    TheListBox.RemoveRow deleteRowNumbers(rowIndex)
  next

This will save database calls. While SQLite isn’t expensive, it’s more than needs to be done and if you ever port this to another database, possibly one with remote connectivity, you’ll not want to do extra database work when possible.

Another note, why connect to the database here? I’m assuming you’ve already connected and populated the listbox. If your database is application wide, why not just create a App.Db variable to hold a connection to the database. If it is window centric, then a Window.Db? Then connect when your app or window opens, and disconnect when your app or window closes.

Any update about this?

what kind of an UPDATE were you expecting to see… this is an asked and answered question. all the information you need is included in the above postings

It’s already 5 years ago, so the above postings still work until now? That’s great :smiley: I just thought there is an updated answers for 2019. I’ll just follow the instructions above then ask again for help if needed.

Thank you Sir Dave