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? 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?
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.
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!
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.
Damn Peter! This is what I call lending a helping hand. I will check
this out and get back to you. Thanks a ton!
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 ***
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
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
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
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 ***
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 ***
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 *** This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |