Deleting multiple records from listbox

Hi, guys again

I have trouble on how to delete multiple records from listbox.
I looked around many sites but couldn't get the clear answer.
Please take a look at this code
This code managed to populate the listbox.



code:--------------------------------------------------------------------------------
<form name="validform" method="post" action="updateRequest.asp?state=delete" onclick="whichchoice()" onSubmit="return dateDiff(this);">
<table ALIGN=CENTER border="0" cellpadding="3" cellspacing="1" width="100%">
<tr>
<th colspan="6" height="25"><b>Choose the items you want to delete</b></th>
</tr>
<tr>
<td width=1%> </td>
<td align=center><span class="gen"><b>Description</b></span></td>
<td width=2%> </td>

</tr>

<tr>
<td width=1%></td>
<td align=center><select name="myselect" style="width:300px;" size="6" multiple="multiple">
<% Do while not rsEdit.eof %>
<option value="<%=rsEdit.fields("ID")%>"><%=rsEdit.fields.item("description").value%></option>
<% rsEdit.Movenext
Loop
%>

</select></td>
<td width=2%> </td>

</tr>
<tr><td><br></td></tr>
<tr>
<th colspan="13" height="25"><input type="submit" name="Delete" value="DELETE"></th>
</tr>
</table>
</form>
--------------------------------------------------------------------------------


But when I click delete button and move to following page


code:--------------------------------------------------------------------------------
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsUpdate
Dim strSQL 'Holds the SQL query for the database

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("tech_re.mdb")
Set rsUpdate = Server.CreateObject("ADODB.Recordset")
Select Case Request.Querystring("state")
Case "delete"
strSQL = "DELETE FROM tblRequest WHERE id= " & Request.Form("myselect")
adoCon.Execute(strSQL)
adoCon.Close
Set adoCon = Nothing
Response.Redirect "it_request.asp"
End Select
%>
--------------------------------------------------------------------------------


It seems this one works only when I delete one record. But when I chose two or more records, it generate errors.
Have you guys ever encountered problem like that?
Please help me.

You are going to have to loop through the number of items selected since you can only delete 1 record at a time.

There are are some other ways too.


Eric

To add to Alien51's comments, Request.Form("myselect") will be an array when you have multiple selections so you'll need to loop through each entry in that array. Of course it won't be an array when the user selects only one item so you'll have to gaurd against that possibility as well.

So how can I create array on the code above? Please help me.:confused:

Originally posted by A1ien51
You are going to have to loop through the number of items selected since you can only delete 1 record at a time.
There are are some other ways too.
Eric
You can delete all the selected records at once. You just need to store the primary key value of the records as he name or value of the checkboxes when you compose the form, and then, in your ASP page, you build a collection with all these PK values and run one select.

http://www.codingforums.com/showthread.php?s=&threadid=25883&highlight=checkbox

I asked somethig like that a while ago (one of my first post I think :p) Here is the link:

http://www.codingforums.com/showthread.php?s=&threadid=25883&highlight=checkbox

<edit>
crossed posts.... :)
</edit>

Originally posted by Squall Leonhart
So how can I create array on the code above? Please help me.:confused:

Raf's got a good answer for you within the thread both he and Bouchel posted. It's not the exact solution but the principle is there and should be easy enough to adjust.

So how can I store the selected value to array?
Do I have to use session variable?
It's hard to understand the link you gave me.

Originally posted by Squall Leonhart
So how can I store the selected value to array?
If you give all checkboxes the same name, buth the primary key value of the record as value, then you will automatically get a sort of array.
If you would name them all checkdelete
(--> name="checkdelete" value="PKvalue from recordset")
and you would submit the form to a page, then
request.form("checkdelete")
will look like 125, 12, 14, 478

Just try it. You could then even just add this value in as the condition for the delete.
But i never do it like that cause i find it a bit dodgy. I'm also not sure that all browsers will post it like that
Originally posted by Squall Leonhart

Do I have to use session variable?

No. You don't need sessionvariables. It's even impossible to do this efficient using sessionvariables, cause the form is posted straight to the processing page.
Originally posted by Squall Leonhart
It's hard to understand the link you gave me.
What exactly don't you understand ?

If you give all checkboxes the same name, buth the primary key value of the record as value, then you will automatically get a sort of array.


This means I already have array.

<td align=center><select name="myselect" style="width:300px;" size="6" multiple="multiple">
<% Do while not rsEdit.eof %>
<option value="<%=rsEdit.fields("ID")%>"><%=rsEdit.fields.item("description").value%></option>
<% rsEdit.Movenext
Loop
%>

</select></td>

This mean Request.Form("myselect") will ve array in array?

What exactly don't you understand ?

What I didn't understand was delete part.
There was no mention about how to delete records in array on the link you gave me.

So I would like to know how the records in array can be deleted.

strSQL = "DELETE FROM tblRequest WHERE id= " & Request.Form("myselect")
adoCon.Execute(strSQL)
adoCon.Close
Set adoCon = Nothing
Response.Redirect "it_request.asp"


I wonder how I can set up array in deleting process.
Thanks

I misread your innitial post. I thought yu used checkboxes but i see from your code it's a listbox.

Just
response.write("myselect")
and look at the output. I don't know of hand , but i think it will look like "45, 145, 78" --> so it's not an array, but its a commadelimited string. And that's exactly what you need to include in the In-clause of your deletestatement.

So your code will look like

strSQL = "DELETE FROM tblRequest WHERE id In (" & Request.Form("myselect") & ")"
adoCon.Execute(strSQL)
adoCon.Close
Set adoCon = Nothing


<edit>forgot the () of the In clause</edit>

Originally posted by Roy Sinclair
To add to Alien51's comments, Request.Form("myselect") will be an array when you have multiple selections so you'll need to loop through each entry in that array. Of course it won't be an array when the user selects only one item so you'll have to gaurd against that possibility as well. Actually, Request.Form("fieldname") returns an object which exposes:

- a .Count property indicating the number of submitted values corresponding to the specified fieldname

- something that behaves mostly like an array and contains either no items (when .Count = 0) or items in positions 0 to .Count-1

- a default property which is a string of the value(s) delimited with ", ".



Anyway, raf's solution posted just above this post will pretty much do the job, but remember to validate those form values first (see other posts on "SQL script injection"), and bear in mind that using this approach with non-numeric primary keys will require modifications.

Thanks for reply.
So i changed this part

strSQL = "DELETE FROM tblRequest WHERE id= " & Request.Form("myselect")

to this

strSQL = "DELETE FROM tblRequest WHERE id= In " & Request.Form("myselect")


But still doesn't work:confused:

Look at raf's answer again carefully! ;)

strSQL = "DELETE FROM tblRequest WHERE id= In " & Request.Form("myselect")

needs to be

strSQL = "DELETE FROM tblRequest WHERE id In (" & Request.Form("myselect") & ")"

Thank you very much.
It works now.:thumbsup:
Yeah~~~~~(Happy)

Glad you got it running :)