Delete Selection from CheckBoxList

[Will Lastname]

I have a windows application that displays users from access database
in a checkboxlist. What I am attempting to do is select a user or users
from the checkboxlist, loop through, and delete their corresponding
information. I have setup a textbox to print the SQL string that is
generated and it seems to be correct.

UPDATE tblName SET EmployeeName = Null WHERE (ID IN(n1,n2));

If I select 3 users with id's 44, 67, 78 and click 'remove' button,
only the user with id 44 is removed even though all 3 users are
reflected in the SQL string.

Any suggestions?

 

[Peter Proost]

If you realy want to delete the info you should do:

delete from tblName WHERE ID IN(n1,n2,n3)

but if you show some code it's easier to see if something is wrong

Greetz Peter

 

[Peter Proost]

One more thing, are you working with parameters?

Greetz Peter

 

[Will Lastname]

Thanks for the quick reply Peter. Here is a code sample:

Dim strSQL As String = "UPDATE tblSoftware Set EmployeeName = Null
WHERE (ID IN("

Dim objConn As New OleDb.OleDbConnection
objConn.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings("strConnection")

For c = 0 To i

iid = clBox.SelectedValue
Dim strSQLBuilder As String = ""

If i > c Then

strSQLBuilder += strSQLBuilder & iid & ","

ElseIf i = c Then

strSQLBuilder += iid & "));"

End If

strSQL = strSQL & strSQLBuilder

Next


I love that signature by the way!

 

[Peter Proost]

Hi,

I couldn't realy test your code because I don't know what values you c and i
are. But maybe you can try this code, it works for me and uses parameters
and a oledbcommand which is safer then concatenating a sqlstring. Normaly
you could just copy paste the code if your checkedlistbox is called chkUsers
and if you have a button called btnDelete.

Hth

Greetz Peter

Private myCon As New OleDb.OleDbConnection

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles MyBase.Load
myCon.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings("strConnection")
FillCheckedList
end sub

Private Sub FillCheckedList
myCon.Open()
Dim myCom As New OleDbCommand("select * from tblName", myCon)
Dim myRead As OleDbDataReader
chkUsers.Items.Clear()
myRead = myCom.ExecuteReader
Do While myRead.Read
chkUsers.Items.Add(myRead.Item("id") & "/" &
myRead.Item("EmployeeName"), False)
Loop
myCon.Close()
End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnDelete.Click
Dim strIn() As String
Dim item As Object
Dim countParam As Integer = 0
Dim strParams As String
Dim myCom As OleDbCommand
If MsgBox("Are you sure that you want to delete the selected user?")
Then
ReDim strIn(chkUsers.CheckedItems.Count - 1)
'create all the needed parameternames
'and get all the id's from the selected items
For Each item In chkUsers.CheckedItems
strIn(countParam) = CStr(item).Split("/"c)(0)
strParams &= "@p" & CStr(countParam) & ","
countParam += 1
Next
strParams = strParams.Remove(strParams.Length - 1, 1)

End If
'some basic errorhandling
Try
myCon.Open()
'If you realy want to delete the user you can use this)
'myCom = New OleDbCommand("delete from tblName where id in (" &
strParams & ")", myCon)
myCom = New OleDbCommand("update tblName set EmployeeName =
null where id in (" & _ strParams & ")", myCon)
'add the parameters and their values to the OleDbCommand
For i As Integer = 0 To countParam - 1
myCom.Parameters.Add(New OleDbParameter("@p" & CStr(i),
OleDbType.Numeric))
myCom.Parameters(i).Value = strIn(i)
Next

myCom.ExecuteNonQuery()
myCon.Close()
vullen()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub

 

[Will Lastname]

Damn Peter! This is what I call lending a helping hand. I will check
this out and get back to you. Thanks a ton!

 

[Will Chamberlain]

Ok, before I implement this code I found another interesting bit about
my code. If I select a user that happens to have SelectedValue of 110,
the SQL string reflects this. If a SelectedValue of 108 is selected,
ditto. If I select both 108 and 110 then the SQL string reflects that 2
users were selected, but instead of WHERE (ID IN (108, 110)) it shows
(ID IN(108, 108). Here is some code:

---
Dim c as integer
i = clBox.CheckedItems.Count()

For c = 1 To i

iid = clBox.SelectedValue

Dim strSQLBuilder As String = ""

If i > c Then

strSQLBuilder += strSQLBuilder & iid & ","

ElseIf i = c Then

strSQLBuilder += iid & "));"

End If

strSQL = strSQL & strSQLBuilder

Next
---

I am new to checkboxlists so I don't know how to efficiently loop
through. What is the checkboxlist equivalent of movenext? Thanks in
advance.

 

[pproost]

Hi Will,

in your code you asign the same value to iid with every iteration:

iid = clBox.SelectedValue (this just returns the first selected value I
think.

The best way to loop trough all the selected items is by looping
through the checkedlistboxs checkeditems property, this returns all the
checked items.
For example:

Dim item As Object
For Each item In chkUsers.CheckedItems
msgbox(item)
Next

I would also advice to always use parameters because they protect you
from stuff like sql insertion attacks

Hth

Greetz Peter

 

[Will Lastname]

Thanks for all your help Peter. You are proving to be very helpful. I
almost have your advice implemented but am getting an error:

Error: Additional information: Cast from type 'DataRowView' to type
'String' is not valid.

This is where the error originates: strIn(countParam) =
CStr(item).Split("/"c)(0)

Subroutine code:

Dim strIn() As String
Dim item As Object
Dim countParam As Integer = 0
Dim strParams As String
Dim objCommand

Dim objConn As New OleDb.OleDbConnection
objConn.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings("strConnection")

'If MsgBox("Are you sure that you want to delete the selected user?",
) Then
ReDim strIn(clBox.CheckedItems.Count - 1)
'create all the needed parameternames
'and get all the id's from the selected items

For Each item In clBox.CheckedItems
strIn(countParam) = CStr(item).Split("/"c)(0) 'Additional
information: Cast from type 'DataRowView' to type 'String' is not
valid.
strParams &= "@p" & CStr(countParam) & ","
countParam += 1
Next

strParams = strParams.Remove(strParams.Length - 1, 1)

'End If
'some basic errorhandling

Try
objConn.Open()
'If you realy want to delete the user you can use this)
'myCom = New OleDbCommand("delete from tblName where id in (" &
strParams & ")", myCon)
objCommand = New OleDb.OleDbCommand("UPDATE tblSoftware SET
EmployeeName = Null WHERE ID in (" & strParams & ")", objConn)
'add the parameters and their values to the OleDbCommand
For i As Integer = 0 To countParam - 1
objCommand.Parameters.Add(New OleDb.OleDbParameter("@p" & CStr(i),
OleDb.OleDbType.Numeric))
objCommand.Parameters(i).Value = strIn(i)
Next

objCommand.ExecuteNonQuery()
objConn.Close()
'vullen()

Catch ex As Exception
MsgBox(ex.ToString)
End Try

End Sub

 

[Peter Proost]

Hi Will,

sorry for the late reply, but I had a weekend of. With which code do you
fill your checkedlistbox?

Greetz Peter

 

[Will Chamberlain]

Actually I got it to work Peter. Thanks again for all of your help!

 

[Peter Proost]

You're welcome

 

[Will Chamberlain]

Actually Peter I could use one last piece of advice. I am getting an
error:
"Cannot modify the Items collection when the DataSource property is set"

The line that generates the error is:
clBox.Items.Clear() - When I remove this the next line to error (same
error as above) is:
clBox.Items.Add(myRead.Item("id") & "/" & myRead.Item("EmployeeName"),
False)

Here is the complete sub:

Panel1.Visible = True

Dim strSoftwareTitle As String = comboSoftwareTitles.Text()

Dim objConn As New OleDb.OleDbConnection
objConn.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings("strConnection")

Dim strSQL As String = "SELECT ID, EmployeeName FROM tblSoftware WHERE "
& _
"(SoftwareTitle = '" & strSoftwareTitle & "') AND " & _
"EmployeeName Is Not Null) ORDER BY EmployeeName ASC;"

Dim objCommand As New OleDb.OleDbCommand(strSQL, objConn)
Dim myRead As OleDb.OleDbDataReader

objConn.Open()

clBox.Items.Clear()
myRead = objCommand.ExecuteReader()

Do While myRead.Read
clBox.Items.Add(myRead.Item("id") & "/" &
myRead.Item("EmployeeName"), False)
Loop

objConn.Close()

 

[Peter Proost]

Do you somewhere use clBox.DataSource =

Because this piece of code (as far as I can see the same as yours) works
just fine:

myCon.Open()
Dim myCom As New OleDbCommand("select * from tblName where softwaretitle =
'SR' and employeename is not null order by employeename asc;", myCon)
Dim myRead As OleDbDataReader
clBox.Items.Clear()
myRead = myCom.ExecuteReader
Do While myRead.Read
clBox.Items.Add(myRead.Item("id") & "/" & myRead.Item("EmployeeName"),
False)
Loop
myCon.Close()

Maybe you've set the datasource property in the property's window when
expirementing?

Greetz Peter

 

[Will Chamberlain]

You are right. I had a reference to another subroutine that set the
datasource. <napoleon dynamite voice>Idiot!</napoleon dynamite voice>

 

[Peter Proost]

Believe me, these things happen when programming

Greetz Peter

 

[Brett Barrett]

I am running into the "Cast from type 'DataRowView' to type 'short' is
not valid" error also. You mentioned that you figured it out - would you
mind letting me know what you did to resolve the problem?

Thanks!