How To Delete Multiple Database Records From ListView with CheckBoxes??

[vbfaint]

Hello,

I have a listview where i have shown records directly from mysql database....
Now i have a problem that i can't delete multiple records but one record from the listview with checkboxes...

My Code:

                    'Dim i As Long
                    'For i = lvDisplayIn.Items.Count To 1 Step -1
                    For i = lvDisplayIn.Items.Count - 1 To 0 Step -1
                        If lvDisplayIn.Items(i).Checked = True Then
                            'Database query to delete records
                        End If
                        If lvDisplayIn.Items.Count = 0 Then Exit For
                    Next i

You can see i have comment out some codes in my code... these codes are from my searching about how to delete
multiple records....but it didn't work....

So when i check multiple checkboxes it only deletes one record from the last///..... why?

Please help me with the circumstances.....
Thanks In Advance !!!

[robie]

hi vbfaint, i goggling your problem and i see these http://www.java2s.com/Tutorial/VB/02...eckBoxcell.htm

also these

http://www.morkalork.com/mork/articl...w#.U4f7EnJdURo

hope it give you some idea,

cant paste the whole code because it will be too long, hope it helps to your problem

[vbfaint]

hi vbfaint, i goggling your problem and i see these http://www.java2s.com/Tutorial/VB/02...eckBoxcell.htm

also these

http://www.morkalork.com/mork/articl...w#.U4f7EnJdURo

hope it give you some idea,

cant paste the whole code because it will be too long, hope it helps to your problem

Okay.... Thanks for ur effort to help me @robie bro.....
The first link isn't helpful at all....
but the second link i can find a code for removing records :

//Remove from listview with checkboxes  
  
        private void button1_Click(object sender, EventArgs e)  
  
        {  
  
            foreach (ListViewItem lvi in listView1.Items)  
  
            {  
  
                if (lvi.Checked == true)  
  
                    listView1.Items.Remove(lvi);  
  
            }  
  
        }

I can try this code//../..but hold on let me know what is going on with the code??
Let me understand the code what is doing this code and what is not doing....
As u can look up my codes there different formatted code than this code(i meant foreach and for loop)+(i know what is vb.net and c++ )......

So please help me with code of understanding that i can use according to my project....
Any further help requires my expectations...
Thanks in advance !!

[robie]

                    'Dim i As Long
                    'For i = lvDisplayIn.Items.Count To 1 Step -1
                    For i = lvDisplayIn.Items.Count - 1 To 0 Step -1
                        If lvDisplayIn.Items(i).Checked = True Then
                            'Database query to delete records
                        End If
                        If lvDisplayIn.Items.Count = 0 Then Exit For
                    Next i

these code is the one that u searched? just confirming bro.. if not please post your original code..


these code is quite similar in what you post:

originaly posted by gracehskuo:

Dim conDataConnection As ADODB.Connection
Dim X As Long
Dim s As String


    Set conDataConnection = New Connection
    conDataConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\overtime.mdb;Persist Security Info=False"

    For X = ListView1.ListItems.Count To 1 Step -1
        If ListView1.ListItems(X).Checked = True Then
        conDataConnection.Execute "DELETE FROM Overtime where EmployeeName = '" & ListView1.ListItems(X).SubItems(2) & "' and OvertimeDate = '" & ListView1.ListItems(X).SubItems(7) & "'"
          
            ListView1.ListItems.Remove X
        End If
    Next

    conDataConnection.Close
    Set conDataConnection = Nothing

i think these is much easier to understand and debug it is much similar to the one above but it delete the last one right? comment back after you try these code bro, hope it will resolved your problem

[vbfaint]

@robie bro.... i can't understand exactly what direction u r sending to me....

I have posted my original code which i am working with.... but u can see in my code that 2 lines is commented out... i meant that 2 lines
i found when i searched in google....
and i tried with those lines according to my intend but it didn't work at all....

and about gracehskuo's problem, i guess that was a sql problem....

and take a look on my code carefully i used lvDisplayIn.Items.Count - 1 To 0 Step -1 >> Items
but in gracehskuo's code it > ListView1.ListItems.Count To 1 Step -1 >> ListItems
when i put ListItems it gets error as the same old error...

And Yes it deletes from the last record but only one record per each execution of the code...
I need multiple deletion procedure to solve my problem ..

[kaliman79912]

Hi vbfaint, sorry I dont have time right now to go through all the posts and someone may have suggested this. When deleting multiple records you have to take into account that after you have deleted the first one, the number and indexes of the rest will change. Take this into consideration before you attempt to delete the second one.

[vbfaint]

Yes i got index problem....
but bro just take a look on my code and give me a code solution that i will understand easily ....
Please help me on this bro...before u go....

[kaliman79912]

OK my friend. I read the problem and I have a few questions.

The first commented line is just the declaration of the integer variable. If you use Option Strict (Which you should), this line should not be commented or i should be typed on the loop (For i as Integer =...)

The second commented line is just a variation of the one you left without comment, obviously it only changes the range of the values of "i" but otherwise is the same. Can't tell which one is best if we dont see the mentioned database query to delete records, which is your third commented line.

The logic so far is OK, the key there would be what you are doing to delete the records, so, show us that please.

[vbfaint]

Okay...when i put > i as integer ...it occurs an error :
'For' loop control variable cannot be of type 'String' because the type does not support the required operators.

My whole code is:

                    Dim i As String
                    For i = lvDisplayIn.Items.Count To 1 Step -1
                        'For i = lvDisplayIn.Items.Count - 1 To 0 Step -1
                        If lvDisplayIn.Items(i).Checked = True Then
                            Try
                                ds = New DataSet
                                da = New MySqlDataAdapter("DELETE FROM inprod WHERE prodName='" & lvDisplayIn.Items(i).SubItems(1).Text & "'", conn)
                                da.Fill(ds, "inprod")
                                MsgBox("Selected Products are Deleted Successfully !!", MsgBoxStyle.Information, "Information")
                                My.Settings.delQuannew = Val(lvDisplayIn.Items(i).SubItems(2).Text)
                                My.Settings.Save()
                                My.Settings.Reload()


                                ds = New DataSet
                                da = New MySqlDataAdapter("SELECT quantity AS OQ FROM stock WHERE prodName='" & lvDisplayIn.Items(i).SubItems(1).Text & "'", conn)
                                da.Fill(ds, "stock")
                                If ds.Tables("stock").Rows.Count > 0 Then
                                    My.Settings.delQuanold = Val(ds.Tables("stock").Rows(0).Item("OQ").ToString)
                                    My.Settings.delQuan = ((Val(My.Settings.delQuanold)) - (Val(My.Settings.delQuannew)))
                                    My.Settings.Save()
                                    My.Settings.Reload()
                                End If

                                ds = New DataSet
                                da = New MySqlDataAdapter("UPDATE stock SET quantity='" & Val(My.Settings.delQuan) & "' WHERE prodName='" & lvDisplayIn.Items(i).SubItems(1).Text & "'", conn)
                                da.Fill(ds, "stock")
                                MsgBox("Successfully Updated Total Stock !!", MsgBoxStyle.Information, "Information")
                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            Call showDataIn()
                            Call showDataTot()
                        End If
                        'If lvDisplayIn.Items.Count = 0 Then Exit For
                    Next i

I throw out comments and its now buggy,..... so this is whole code for u brother...
Help me out...

[kaliman79912]

Of course, i sould be an Integer, not a String.

Lets do this in stages, I have a few suggestions on your deletion code.

1) do not initialize the dataset and dataadapter on each iteration, do that before the loop and then just change the parameters
2) On the topic, use parameters and not string concatenation.
3) I do not believe da.Fill is the correct way of executing a DELETE FROM query
4) Do not use "Call"

lets deal with those and we go from there

[vbfaint]

Okay,....first of all i need 1) & 2) examples .... i wanna be sure what exactly u r trying to say to me....
do that before the > u mean should i do my deletion procedure in a sub before??

[robie]

Okay,....first of all i need 1) & 2) examples .... i wanna be sure what exactly u r trying to say to me....
do that before the > u mean should i do my deletion procedure in a sub before??

sorry bro if im confusing you i try to debug ur code right now and i end up here

   With ListView1
            
            For i = ListView1.Items.Count - 1 To 0 Step -1
                If ListView1.Items(i).Checked = True Then
                    'Database query to delete records
                    MsgBox(.Items(i).ToString)

                    .Items(i).Remove()
                End If

            Next i

        End With

it delete all the selected checkbox only try it with your SQL query u know what i mean..

[vbfaint]

okay....thanks for ur effort bro...let me try this....give me lil time...i 'll let u know....

[vbfaint]

Bro I tried with your code...but it didn't work.... it also doing the same as the old... delete only one record from the last....

[robie]

okay....thanks for ur effort bro...let me try this....give me lil time...i 'll let u know....

ur code works well, i think the problem is in your SQL query.. unfortunately my "try catch" version of calling connection is different than yours..

if i will debug your mine would like these

    With ListView1

            For i = ListView1.Items.Count - 1 To 0 Step -1
                If ListView1.Items(i).Checked = True Then
                    'Database query to delete records
                    MsgBox(.Items(i).ToString)

                    .Items(i).Remove()

Dim sqlstr As String = ""
sqlstr = "DELETE FROM TABLE WHERE prodName='" & lvDisplayIn.Items(i).SubItems(1).Text & "'"


Dim dt As New DataTable
                    Try

                        Dim sqlcn As New SqlConnection(conn)
                        Dim sqlcmd As New SqlCommand

                        sqlcmd.CommandText = sqlstr
                        sqlcmd.Connection = sqlcn
                        sqlcmd.CommandTimeout = 0

                        If sqlcn.State = ConnectionState.Closed Then sqlcn.Open()

                        sqlcmd.ExecuteNonQuery()


                        sqlcn.Close()
                        sqlcn.Dispose()
                        sqlcmd.Dispose()
                    Catch ex As Exception
                        MsgBox(ex.ToString)

                    End Try
                End If

            Next i

        End With

note:im not sure in the sqltstr if it is right..

edit: i forgot to change lvDisplayIn to Listview1

[robie]

Okay...when i put > i as integer ...it occurs an error :
'For' loop control variable cannot be of type 'String' because the type does not support the required operators.

My whole code is:

                    Dim i As String
                    For i = lvDisplayIn.Items.Count To 1 Step -1
                        'For i = lvDisplayIn.Items.Count - 1 To 0 Step -1
                        If lvDisplayIn.Items(i).Checked = True Then
                            Try
                                ds = New DataSet
                                da = New MySqlDataAdapter("DELETE FROM inprod WHERE prodName='" & lvDisplayIn.Items(i).SubItems(1).Text & "'", conn)
                                da.Fill(ds, "inprod")
                                MsgBox("Selected Products are Deleted Successfully !!", MsgBoxStyle.Information, "Information")
                                My.Settings.delQuannew = Val(lvDisplayIn.Items(i).SubItems(2).Text)
                                My.Settings.Save()
                                My.Settings.Reload()


                                ds = New DataSet
                                da = New MySqlDataAdapter("SELECT quantity AS OQ FROM stock WHERE prodName='" & lvDisplayIn.Items(i).SubItems(1).Text & "'", conn)
                                da.Fill(ds, "stock")
                                If ds.Tables("stock").Rows.Count > 0 Then
                                    My.Settings.delQuanold = Val(ds.Tables("stock").Rows(0).Item("OQ").ToString)
                                    My.Settings.delQuan = ((Val(My.Settings.delQuanold)) - (Val(My.Settings.delQuannew)))
                                    My.Settings.Save()
                                    My.Settings.Reload()
                                End If

                                ds = New DataSet
                                da = New MySqlDataAdapter("UPDATE stock SET quantity='" & Val(My.Settings.delQuan) & "' WHERE prodName='" & lvDisplayIn.Items(i).SubItems(1).Text & "'", conn)
                                da.Fill(ds, "stock")
                                MsgBox("Successfully Updated Total Stock !!", MsgBoxStyle.Information, "Information")
                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            Call showDataIn()
                            Call showDataTot()
                        End If
                        'If lvDisplayIn.Items.Count = 0 Then Exit For
                    Next i

I throw out comments and its now buggy,..... so this is whole code for u brother...
Help me out...

focus first at delete bro i see many query here, try one at a time..

[kaliman79912]

putting the deletion procedure in a Sub could be better, it makes things clearer. But it is not mandatory.

On issue 1)

ds = New DataSet

that should be at the begining of the whole Sub, just once.
also the dataAdapter. but I am not sure if you should be using MySQLCommand instead of all that.

2) If you build the command using concatenation you have a risk of SQL injection. (its a tech term).

I have to go to a meeting now, but if you do not have it resolved when I come back, I will dig deeper

[robie]

putting the deletion procedure in a Sub could be better, it makes things clearer. But it is not mandatory.

On issue 1)

ds = New DataSet

that should be at the begining of the whole Sub, just once.
also the dataAdapter. but I am not sure if you should be using MySQLCommand instead of all that.

2) If you build the command using concatenation you have a risk of SQL injection. (its a tech term).

I have to go to a meeting now, but if you do not have it resolved when I come back, I will dig deeper

your right sir it much cleaner if we module the delete process or sub im just put in there to make him see the process..
thanks sir. please help if these not resolved after that..