Getting a list view entry into a variable to remove from database

Question

What i am trying to to is get the selected list view item into a variable. More importantly a specific entry in the selected so that i can remove it from the database.

My code:

For Each i As ListViewItem In lstViewMembers.SelectedItems
        lstViewMembers.Items.Remove(i)
        Dim Remove As String
        Remove = lstViewMembers.Items(0)

        sql = "DELETE FROM tblMembers WHERE Name = '" & Remove & "'"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Remove")
    Next

Name is the 2nd column in the list view, how would i remove that selected person from the database using the name?

Thanks,

Andy

MANY problems: 1) setting yourself up for SQL injection by saying "where name = 'variable'" (see http://www.w3schools.com/sql/sql_injection.asp) 2) using old join syntax (see http://www.w3schools.com/sql/sql_join_inner.asp) 3) attempting to delete list members from active list (confuses it - http://stackoverflow.com/questions/29464800/cant-remove-items-from-list-with-remove/29465099#29465099) 4) I don't THINK a dataAdapter "fills" anything on a delete statement. For an answer about deleting with a join, see http://stackoverflow.com/questions/439750/t-sql-selecting-rows-to-delete-via-joins. — CindyH, Apr 08 '15 at 19:11
1st - doesn't matter. 2nd there are relationships in which i have now remove the other table so that doesn't matter. 3rd - ok... 4) that kinda helps ^You haven't really helped me at all — Andy, Apr 09 '15 at 11:23
+ none of the above helps because i need to delete the selected. — Andy, Apr 09 '15 at 11:25

CindyH · Answer 1 · 2015-04-09T14:09:30.567

I'm assuming that con is an active and open connection, not just a connection string.

In order to 1) make minimal changes, 2) delete it from the database, and 3) leave the deleted items in the VB list, here's what I'd do. I'm quite unfamiliar with DataAdapters - I know there's a way to delete with them but I haven't used it.

Dim Remove As String
For Each itemToDelete As ListViewItem In lstViewMembers.SelectedItems
    Remove = itemToDelete.Text 

    sql = "DELETE FROM tblMembers WHERE Name = '" & Remove & "'"

    SqlCommand cmd = New SqlCommand (sql, con)
    cmd.ExecuteNonQuery()
Next

Make sure that you close and remove the connection afterwards - connections are expensive. For a way to do that, see https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

In order to reduce the sql-injection risk, look here for the use of parameters https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters%28v=vs.110%29.aspx

The main issue is with this line Remove = `lstViewMembers.Items(i)` it states "Overload resolution failed because no accessible 'Item' can be called with these arguments" (quoted) — Andy, Apr 09 '15 at 13:32
Updated the answer - it was attempting to use "i" as a list, not a specific item. — CindyH, Apr 09 '15 at 14:10

Getting a list view entry into a variable to remove from database

1 Answers1