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]

Re: Delete Selection from CheckBoxList

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



--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.

"Will Lastname" <wh00ph@brinkst er.net> schreef in bericht
news:1129056516 .637744.174480@ g49g2000cwa.goo glegroups.com.. .[color=blue]
> 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?
>[/color]

[Peter Proost]

Re: Delete Selection from CheckBoxList

One more thing, are you working with parameters?

Greetz Peter

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.

[Will Lastname]

Re: Delete Selection from CheckBoxList

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.OleDbConn ection
objConn.Connect ionString =
System.Configur ation.Configura tionSettings.Ap pSettings("strC onnection")

For c = 0 To i

iid = clBox.SelectedV alue
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]

Re: Delete Selection from CheckBoxList

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.OleDbConn ection

Private Sub Form1_Load(ByVa l sender As Object, ByVal e As System.EventArg s)
Handles MyBase.Load
myCon.Connectio nString =
System.Configur ation.Configura tionSettings.Ap pSettings("strC onnection")
FillCheckedList
end sub

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

Private Sub btnDelete_Click (ByVal sender As System.Object, ByVal e As
System.EventArg s) 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.Co unt - 1)
'create all the needed parameternames
'and get all the id's from the selected items
For Each item In chkUsers.Checke dItems
strIn(countPara m) = CStr(item).Spli t("/"c)(0)
strParams &= "@p" & CStr(countParam ) & ","
countParam += 1
Next
strParams = strParams.Remov e(strParams.Len gth - 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("d elete from tblName where id in (" &
strParams & ")", myCon)
myCom = New OleDbCommand("u pdate 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.Parameter s.Add(New OleDbParameter( "@p" & CStr(i),
OleDbType.Numer ic))
myCom.Parameter s(i).Value = strIn(i)
Next

myCom.ExecuteNo nQuery()
myCon.Close()
vullen()
Catch ex As Exception
MsgBox(ex.ToStr ing)
End Try
End Sub

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.

[Will Lastname]

Re: Delete Selection from CheckBoxList

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]

Re: Delete Selection from CheckBoxList

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.CheckedIt ems.Count()

For c = 1 To i

iid = clBox.SelectedV alue

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.


---
"Our enemies are innovative and resourceful, and so are we. They never
stop thinking of ways to harm our country and our people, and neither do
we." President George W. Bush

*** Sent via Developersdex http://www.developersdex.com ***

[pproost@gmail.com]

Re: Delete Selection from CheckBoxList

Hi Will,

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

iid = clBox.SelectedV alue (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.Checke dItems
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]

Re: Delete Selection from CheckBoxList

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(countPara m) =
CStr(item).Spli t("/"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.OleDbConn ection
objConn.Connect ionString =
System.Configur ation.Configura tionSettings.Ap pSettings("strC onnection")

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

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

strParams = strParams.Remov e(strParams.Len gth - 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("d elete from tblName where id in (" &
strParams & ")", myCon)
objCommand = New OleDb.OleDbComm and("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.Para meters.Add(New OleDb.OleDbPara meter("@p" & CStr(i),
OleDb.OleDbType .Numeric))
objCommand.Para meters(i).Value = strIn(i)
Next

objCommand.Exec uteNonQuery()
objConn.Close()
'vullen()

Catch ex As Exception
MsgBox(ex.ToStr ing)
End Try

End Sub

[Peter Proost]

Re: Delete Selection from CheckBoxList

Hi Will,

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

Greetz Peter

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.

"Will Lastname" <wh00ph@brinkst er.net> schreef in bericht
news:1129315198 .077846.141040@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> 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(countPara m) =
> CStr(item).Spli t("/"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.OleDbConn ection
> objConn.Connect ionString =
> System.Configur ation.Configura tionSettings.Ap pSettings("strC onnection")
>
> 'If MsgBox("Are you sure that you want to delete the selected user?",
> ) Then
> ReDim strIn(clBox.Che ckedItems.Count - 1)
> 'create all the needed parameternames
> 'and get all the id's from the selected items
>
> For Each item In clBox.CheckedIt ems
> strIn(countPara m) = CStr(item).Spli t("/"c)(0) 'Additional
> information: Cast from type 'DataRowView' to type 'String' is not
> valid.
> strParams &= "@p" & CStr(countParam ) & ","
> countParam += 1
> Next
>
> strParams = strParams.Remov e(strParams.Len gth - 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("d elete from tblName where id in (" &
> strParams & ")", myCon)
> objCommand = New OleDb.OleDbComm and("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.Para meters.Add(New OleDb.OleDbPara meter("@p" & CStr(i),
> OleDb.OleDbType .Numeric))
> objCommand.Para meters(i).Value = strIn(i)
> Next
>
> objCommand.Exec uteNonQuery()
> objConn.Close()
> 'vullen()
>
> Catch ex As Exception
> MsgBox(ex.ToStr ing)
> End Try
>
> End Sub
>[/color]

[Will Chamberlain]

Re: Delete Selection from CheckBoxList

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

---
"Our enemies are innovative and resourceful, and so are we. They never
stop thinking of ways to harm our country and our people, and neither do
we." President George W. Bush

*** Sent via Developersdex http://www.developersdex.com ***

[Peter Proost]

Re: Delete Selection from CheckBoxList

You're welcome

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.


"Will Chamberlain" <will.chamberla in@devdex.com> schreef in bericht
news:uYFAn410FH A.3336@TK2MSFTN GP12.phx.gbl...[color=blue]
> Actually I got it to work Peter. Thanks again for all of your help!
>
> ---
> "Our enemies are innovative and resourceful, and so are we. They never
> stop thinking of ways to harm our country and our people, and neither do
> we." President George W. Bush
>
> *** Sent via Developersdex http://www.developersdex.com ***[/color]

[Brett Barrett]

Re: Delete Selection from CheckBoxList

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!

*** Sent via Developersdex http://www.developersdex.com ***