How do I delete multiple records using checkboxes?

Results 1 to 2 of 2

Thread: How do I delete multiple records using checkboxes?

  1. #1
    Mike M. Guest

    Default How do I delete multiple records using checkboxes?

    Here&#039s the scenario:<BR><BR>The HR department already has a web page that shows all the company&#039s current job openings. But now it wants a web-based tool that will allow one of its staff to delete all job vacancies from the jobs database as they are filled. The goal is to create a dynamic situation in which a person using the tool can delete former job vacancies from the database AND from the web page at the same time.<BR><BR>Here&#039s what I&#039d like to do:<BR><BR>I&#039d like to create an ASP script that will loop through the jobs database and create a web page that shows all job vacancies AND place a checkbox beside them. Then, the HR staffer will check off all the job vacancies he or she wants to delete at that moment, then click a Submit button to submit the changes. Then, another ASP script will delete all the checked job vacancies from the database AND update the web site as well. (The company&#039s job listings web page on the WWW is also an ASP page) <BR><BR>How do I do this?<BR><BR>Please, keep it simple. <BR><BR>Thanks.

  2. #2
    Join Date
    Dec 1969

    Default EASY to delete multiple records using checkboxes

    The key to this is having a UNIQUE PRIMARY KEY in the Vacancies table in the DB.<BR><BR>So let me *assume* that the structure of the Vacancies table looks *something* like this:<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;Table: Vacancies<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;JobID Text PRIMARY KEY<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;JobName Text<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;MaxSalary Double<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs p;&nbsp;JobDesc Text<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;... other fields ...<BR><BR>***************<BR><BR>So, then, your first page does something like this:<BR><BR>**** FILE: ShowVacancies.asp ****<BR><BR>&LT;FORM Action="RemoveVacancies.asp" Method="Post"&#062;<BR>&#060;%<BR>... [I assume you can make the connection to DB] ...<BR>Set RS = conn.Execute("SELECT * FROM Vacancies ORDER BY JobID")<BR>Do While Not RS.EOF<BR>%&#062;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&LT;I NPUT TYPE=Checkbox Name=ToDelete Value="&#060;% = RS("JobID") %&#062;"&#062;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&#060; % = RS("JobName") %&#062;<BR>&nbsp;&nbsp;&nbsp;&nbsp;... etc.<BR>&nbsp;&nbsp;&nbsp;&nbsp;&#060;%<BR>&nbsp;& nbsp;&nbsp;&nbsp;RS.MoveNext<BR>Loop<BR>...<BR><BR >********************************<BR><BR>Then your second page:<BR><BR>**** FILE: RemoveVacancies.asp ******<BR><BR>&#060;%<BR>removals = Request.Form("ToDelete")<BR>If Len(removals) &#062; 0 Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;removals = Replace( removals, ", ", "&#039,&#039" ) <BR>&nbsp;&nbsp;&nbsp;&nbsp;query = "DELETE FROM Vacancies WHERE JobId In(&#039" & removals & "&#039)"<BR>&nbsp;&nbsp;&nbsp;&nbsp;... [ again, you can make connection to DB ] ...<BR>&nbsp;&nbsp;&nbsp;&nbsp;conn.Execute(query) <BR>End If<BR><BR>****************<BR><BR>And that&#039s it!<BR><BR>Why does it work?<BR><BR>(1) We gave *all* the removal checkboxes the SAME NAME ("ToDelete").<BR><BR>(2) So, in the second page, when we get the value of the field with that name, what we get is a LIST of ALL the checked boxes, separated by commas (with a space after each comma--don&#039t ask why, it just happens).<BR><BR>Example: On the first page, the user checks the boxes for jobs with JobID&#039s ENG-01 and ADM-07 and QA-37<BR><BR>On the second page, Request.Form("ToDelete") will get the string<BR>&nbsp;&nbsp;&nbsp;&nbsp;"ADM-07, ENG-01, QA-37"<BR><BR>(3) We then use a tricky replace. We replace each comma-space sequence with apostrophe-comma-apostrophe. So that string that *was*<BR>&nbsp;&nbsp;&nbsp;&nbsp;"ADM-07, ENG-01, QA-37"<BR>becomes<BR>&nbsp;&nbsp;&nbsp;&nbsp;"ADM-07&#039,&#039ENG-01&#039,&#039QA-37"<BR><BR>(4) Finally, we tack on the rest of the query and end up with <BR>&nbsp;&nbsp;&nbsp;&nbsp;"DELETE FROM Vacancies WHERE JobID IN (&#039ADM-07&#039,&#039ENG-01&#039,&#039QA-37&#039)"<BR><BR>Ta-da. Nothing more to it. <BR><BR>If you need help understanding the (very minor) SQL involved, check out the web page<BR>&nbsp;&nbsp;&nbsp;&nbsp;<BR>a wonderful one-web-page summary of the SQL language.<BR><BR>

Posting Permissions

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