How To Delete Multiple Database Records From ListView with CheckBoxes??
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:
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 !!!
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
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
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
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 :
Code:
//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++ :p)......
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 !!
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
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
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:
Code:
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 :)
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
@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 .. :(
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
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.
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
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....
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
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.
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
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:
Code:
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...
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
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
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
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??
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
Quote:
Originally Posted by
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??
sorry bro if im confusing you i try to debug ur code right now and i end up here
Code:
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..
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
okay....thanks for ur effort bro...let me try this....give me lil time...i 'll let u know....
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
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.... :(
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
Quote:
Originally Posted by
vbfaint
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
Code:
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
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
Quote:
Originally Posted by
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:
Code:
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..
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
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
Re: How To Delete Multiple Database Records From ListView with CheckBoxes??
Quote:
Originally Posted by
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
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..
