473,322 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Delete Selection from CheckBoxList

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?

Nov 21 '05 #1
12 1828
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" <wh****@brinkster.net> schreef in bericht
news:11**********************@g49g2000cwa.googlegr oups.com...
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?

Nov 21 '05 #2
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.
Nov 21 '05 #3
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.AppSett ings("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!

Nov 21 '05 #4
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.AppSett ings("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

--
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.
Nov 21 '05 #5
Damn Peter! This is what I call lending a helping hand. I will check
this out and get back to you. Thanks a ton!

Nov 21 '05 #6
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.
---
"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 ***
Nov 21 '05 #7
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

Nov 21 '05 #8
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.AppSett ings("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

Nov 21 '05 #9
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" <wh****@brinkster.net> schreef in bericht
news:11**********************@g14g2000cwa.googlegr oups.com...
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.AppSett ings("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

Nov 21 '05 #10
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 ***
Nov 21 '05 #11
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" <wi**************@devdex.com> schreef in bericht
news:uY**************@TK2MSFTNGP12.phx.gbl...
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 ***

Nov 21 '05 #12
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 ***
Apr 20 '06 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jensen bredal | last post by:
Hello, Can someone guide me in how i may process a multiple selection on a databound checkboxlist? The checkboxlist is bound to a table of product category. The product category id is an FK in...
4
by: dm_dal | last post by:
Is there a know issue surrounding the CheckBoxList control and it's viewstate? When my control is created, it's ListItems are checked as needed, but on a postback, they loose their Selected...
4
by: Shaul Feldman | last post by:
Hello, I have something really awkward at work - fighting with CheckBoxList... How can I define CSS for ListItem in CheckBoxList programmatically. I add CheckBoxList's Items on the fly, something...
0
by: Saket Mundra | last post by:
I am designing a web application to offer services to customers. In form1 I have listed all the services available using CheckBoxlist control. I have a different webform for customization of each...
5
by: Patrick.O.Ige | last post by:
I'm binding a CheckBoxlist below in the ItemDataBound(the CheckBoxList is in a Datalist) By doing "li.Selected = True" i can see all the checkBoxes are selected. But what i want is to be able...
4
by: Patrick.O.Ige | last post by:
I have a CheckBoxList in a DataList and i'm trying to get item Selected after doing a postBack. I have set my CheckBoxlist AutoPostBack="True" Any ideas what 'm doing wrong? It seems not to...
2
by: Joanne | last post by:
Hi, I have an "interesting" problem with UserControls in a datagrid and I'm desperate for your help as I work alone and have no-one else to ask. It is quite complicated but I'll try to keep it...
1
by: imranabdulaziz | last post by:
Dear All, I am using asp.net2.0, C#, sql2005 using Visual studio 2005 Let me explain the scenario. I am making search interface where 18 field(all are master field) are there . which I populate...
0
by: dudeja.rajat | last post by:
On Sat, Aug 30, 2008 at 2:32 PM, Fredrik Lundh <fredrik@pythonware.comwrote: Fredrik, Thanks so much. That worked. Following this, I can now see that my combo2 has no previous elements and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.