Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question 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.

  • #2
    Senior Coder A1ien51's Avatar
    Join Date
    Jun 2002
    Location
    Between DC and Baltimore In a Cave
    Posts
    2,717
    Thanks
    1
    Thanked 94 Times in 88 Posts
    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
    Tech Author [Ajax In Action, JavaScript: Visual Blueprint]

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #4
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So how can I create array on the code above? Please help me.

  • #5
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    deleting multiple ids

  • #6
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I asked somethig like that a while ago (one of my first post I think ) Here is the link:

    deleting multiple ids

    <edit>
    crossed posts....
    </edit>

  • #7
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Originally posted by Squall Leonhart
    So how can I create array on the code above? Please help me.
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #8
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #9
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 ?

  • #10
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Code:
    <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.
    Code:
    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

  • #11
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Code:
    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>
    Last edited by raf; 11-20-2003 at 07:38 PM.

  • #12
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.
    Last edited by M@rco; 11-20-2003 at 02:18 AM.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #13
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for reply.
    So i changed this part
    Code:
    strSQL = "DELETE FROM tblRequest WHERE id= " & Request.Form("myselect")
    to this
    Code:
    strSQL = "DELETE FROM tblRequest WHERE id= In " & Request.Form("myselect")
    But still doesn't work

  • #14
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Look at raf's answer again carefully!
    Last edited by M@rco; 11-20-2003 at 07:23 PM.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #15
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    strSQL = "DELETE FROM tblRequest WHERE id= In " & Request.Form("myselect")

    needs to be

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


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •