Hi,
I was trying to help someone else, and then I realised that I am also having trouble succeeding in this quest
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.
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.
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
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?
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.
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 I just thought there is an updated answers for 2019. I’ll just follow the instructions above then ask again for help if needed.