Deleting multiple records

[Looch]

Here's what I have... A table with 3 columns. Headings are (Name, Prayer
Request, and Delete). A loop that goes through a Record Set and prints out the
Name, Prayer Request and then a Checkbox in the 'Delete' column. This part
works fine. What I want to do is to allow the user to select the checkbox for
each row that they want to delete, and then when they click the 'delete' button
at the bottom of the form, they will be redirected to the same page except that
the checked rows will no longer be there because they have been deleted from
the database. Attached is the code for the form that I have. I tried adding a
'Delete Record' Server Behavior, but was unsure how that would work, when each
checkbox was not specifically related to any specific row. The editing part
seems to work fine, it is just the delete portion of this page. Can anybody
help a brother out? These forums have been great and I have always appreciated
the help I have been able to get. If you can provide any solutions to clear up
this problem, I would be very grateful. Thanks, in advance!

<form action="<cfoutput>#CurrentPage#</cfoutput>" method="POST"
name="editPrayer">
<table width="98%" border="1" align="center" cellpadding="3"
cellspacing="0">
<tr>
<td><div align="left"><strong>Name</strong></div></td>
<td><div align="left"><strong>Prayer Request</strong></div></td>
<td><div align="center"><strong>Delete</strong></div></td>
</tr>
<cfoutput query="rs_prayerList">
<tr>
<td><div align="left"><a
href="editPrayer.cfm?PrayerID=#rs_prayerList.Praye rID#">#rs_prayerList.Name#</a>
</div></td>
<td><div align="left">#rs_prayerList.PrayerRequest#</div></td>
<td><div align="center"><input type="checkbox" /></div></td>
</tr>
</cfoutput>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td align="right"><input type="submit" name="Delete" value="Delete"
/></td>
</tr>
</table>
</form>

[vkunirs]

Hi Looch

You can to this Like:

Here with in the cfoutput tag for the checkbox you need to give the name and
value attributes.
here for all the checkboxes the name will be same and the value will be the
id of that record.

after selecting the checkboxes which you want by submitting the page then
redirect to the delete.cfm page.

in that page write the code to delete the records.
here use the cfloop and give the form.checkbox name. cf loop will treat
this as a list and pass it into the loop

then you can delete the records one by one for the id's

after deleting the records redirect to the same page. then there you will see
the only undeleted records.

do liek this:




<cfoutput query="rs_prayerList">
<tr>
<td><div align="left"><a
href="editPrayer.cfm?PrayerID=#rs_prayerList.Praye rID#">#rs_prayerList.Name#</a>
</div></td>
<td><div align="left">#rs_prayerList.PrayerRequest#</div></td>
<td><div align="center"><input type="checkbox" name="del" value="id"
/></div></td>
</tr>
</cfoutput>

[Looch]

Thanks for the help, vkunirs!! That worked perfectly. The only thing that I
might like to change is for it to have a pop-up dialog box confirm their
deletion. You know, a little dialog box that says something like 'Are you sure
you want to delete these prayer requests?'. That way, in case they
accidentally clicked a check box, it will not automatically delete those
records. Does that make sense? If you (or anybody else) knows how to do this,
that would be great. Thanks!! :D

[eastinq]

Yeah, just add the onSubmit event to your form tag.

<form onSubmit="return window.confirm('Are you sure?')" ...

[Looch]

Thanks for all of your help, guys. It is much appreciated!!

[Looch]

OK...so I thought that I had it, but when I tried to delete more than one
entry, the thing crashed on me. I think that I am a little unsure about how
and where the cfloop structure is supposed to go. If you could help me out
with that, I would appreciate it. Here is the error I am getting... Error
Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC
Socket][Microsoft] [ODBC Microsoft Access Driver] Too few parameters. Expected
1. The error occurred in
C:\Inetpub\wwwroot\FarragutChurch\deletePrayer.cfm : line 16 14 : <cfloop
index='listElement' list='FORM.del'> 15 : <cfquery
datasource='farragutChurch'> 16 : DELETE FROM tbl_prayerList WHERE
PrayerID=<cfoutput>#listElement#</cfoutput> 17 : </cfquery> 18 :
<cflocation url='editPrayerList.cfm'>
--------------------------------------------------------------------------------
SQL DELETE FROM tbl_prayerList WHERE PrayerID=FORM.del DATASOURCE
farragutChurch VENDORERRORCODE -3010 SQLSTATE 07002 Here are my
variables that are showing in the debubbing information... Form Fields:
DEL=15,14 DELETE=Delete FIELDNAMES=DEL,DELETE Here is what I have, but it
does not work...

<cfif IsDefined("FORM.del") AND #FORM.del# NEQ "">
<cfloop index="listElement" list="FORM.del">
<cfquery datasource="farragutChurch">
DELETE FROM tbl_prayerList WHERE
PrayerID=<cfoutput>#listElement#</cfoutput>
</cfquery>
<cflocation url="editPrayerList.cfm">
</cfloop>
</cfif>

[Looch]

I figured this one out...It was so simple, I just overlooked it. Here is what
I had... <cfif IsDefined('FORM.del') AND #FORM.del# NEQ ''> <cfloop
index='listElement' list='FORM.del'> <cfquery
datasource='farragutChurch'> DELETE FROM tbl_prayerList WHERE
prayerID=#listElement# </cfquery> <cflocation
url='editPrayerList.cfm'> </cfloop> </cfif> And here was the
fix...(notice the pound signs surrounding the FORM.del in the cfloop tag. The
cflocation tag was also moved outside of the cfloop tags. This took care of my
problem deleting multiple records. <cfif IsDefined('FORM.del') AND #FORM.del#
NEQ ''> <cfloop index='listElement' list='#FORM.del#'> <cfquery
datasource='farragutChurch'> DELETE FROM tbl_prayerList WHERE
prayerID=#listElement# </cfquery> </cfloop> <cflocation
url='editPrayerList.cfm'> </cfif>

[Looch]

Thanks for all of your help guys!!

[magnusthyvold]

You can also achieve the deletion of multiple records without using a loop.
Instead you use the IN operator in SQL. The IN operator will make a comparison
against a list in the WHERE clause. The form will return the ID numbers as a
list just as in the other examples. <cfquery datasource='farragutChurch'>
DELETE FROM tbl_prayerList WHERE PrayerID IN '#DEL#' </cfquery> This will
delete each record from tbl_prayerlist wehere the PrayerID matches an element
in the list. It does this with only one call to the database instead of a call
for each element in the list. The list should be in single quotes and you
don't need the cfoutput tags inside the cfquery tag. It is generally more
efficent to let the database do this kind of work than ColdFusion. Magnus

[alagukannan]

Hi,
ur method doesn't work for me.I am not able to retreive the multiple id
values.If i do it in the way u said,i get a variable undefined error in the
next page(delete page).
I have attached the codes below can anyone check it out and reply me.


Database content shown here.....................................

<cfif condition>
<form action="TeacherSite_Deleteedit1.cfm" method="post" name="delform">
<input type="hidden" name="FNAME" value="#checklogin.FirstName#">
<input type="hidden" name="LNAME" value="#checklogin.LastName#">
<input type="hidden" name="name" value="#q5.TeacherName#">
<input type="hidden" name="block" value="#q5.Competency_block#">
<input type="hidden" name="title" value="#q5.Competency_title#">
<input type="hidden" name="site" value="#q5.Site#">
<td><input type="checkbox" name="chk" value="#q5.id#"></td>
<!-----chk used for storing id value in checkbox --------------->
</cfif>
</cfoutput>
<tr></tr>
<tr>
<td colspan="5"> <div align="center">
<input type="submit" class= "button" name="Delete" value="Delete"
onClick="javascript:createCookie('ppkcookie1',wind ow.location.href,-1);">
</div></td>
</tr>
</form>

Delete page....
<cfif Isdefined(#delform.chk#)
<CFQUERY name="q1" datasource="Intranet">
SELECT id,Site, TeacherName, Competency_block,Competency_title FROM CR_submit
where id in(#delform.chk#)
</cfquery>
<cfoutput query="q1">
id:#q1.id#;
<br>
name:#q1.teachername#
</cfoutput>
</cfif>

[OldCFer]

You are referencing your form incorrectly:

<cfif Isdefined(#delform.chk#)
<CFQUERY name="q1" datasource="Intranet">
SELECT id,Site, TeacherName, Competency_block,Competency_title FROM CR_submit
where id in(#delform.chk#)
</cfquery>

should be:

<cfif Isdefined("Form.chk")
<CFQUERY name="q1" datasource="Intranet">
SELECT id,Site, TeacherName, Competency_block,Competency_title FROM CR_submit
where id in(#form.chk#)
</cfquery>

[alagukannan]

Thank u
guys..it worked..
I had a small glitch which i fixed it..
thx for the help and ur time
thx
AL

[stcoff]

I am trying to set up this page so that I can delete more than one record at a
time. This is a site I took over and have very minimal knowledge with cold
fusion. Please Help.

Code is attached

Stcoff



<cfif isdefined("url.send")>

<CFQUERY NAME="deleteleader" DATASOURCE="#ds#">
DELETE from track_leaders
WHERE perf_id = #form.perf_id#
</CFQUERY>

<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td><b>Administration Panel </b>- <a href="admin_panel.cfm">Return to
admin
panel</a></td>
</tr>
<tr>
<td><b>Back to Delete Leader </b>- <a
href="fuse_delete_leaders.cfm">Delete a
Performance</a></td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
<tr>
<td>(Leader) has been deleted.<BR>
</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
</table>
<cfelse>
<!-- Get Current Leaders -->

<cfquery datasource="#ds#" name="leaders">
SELECT track_leaders.event_id, track_leaders.name AS playername,
track_ievent.ievent_name, track_leaders.perf_id, track_leaders.result,
track_teams.school, track_ievent.ievent_name, track_ievent.category
FROM (track_leaders INNER JOIN track_teams ON track_leaders.teamid =
track_teams.teamid) INNER JOIN track_ievent ON track_leaders.event_id =
track_ievent.event_id
ORDER BY track_leaders.event_id, track_leaders.name, track_leaders.result;
</cfquery>

<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td><b>Administration Panel </b>- <a href="admin_panel.cfm">Return to
admin
panel</a></td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
<tr>
<td>
<p>Click the checkbox next to the performance on the form below you wish
to
delete from the Database. </p>
<p>&nbsp;</p>
</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
<form name="form1" method="post" action="fuse_delete_leaders.cfm?send=">
<tr>
<td><b>Leaders: </b></td>
</tr>
<CFIF leaders.RecordCount IS 0>
<tr>
<td>
There are no active Performances to display at this time.
</td>
</tr>
<tr>
<td>&nbsp;</td>
</tr>
<CFELSE>
<CFOUTPUT QUERY="leaders">
<tr>
<td>
<INPUT TYPE="checkbox" NAME="perf_id" VALUE="#perf_id#"
ONCLICK="form.submit();">
<table width="720" border="1" cellspacing="2" cellpadding="2">
<tr>
<td width="150" nowrap> #playername# </td>
<td width="70" nowrap>#result# </td>
<td width="200" nowrap><b>#ievent_name# </b></td>
<td width="200" nowrap>#school#</td>
</tr></table>
</td>
</tr>

<tr>
<td>&nbsp;</td>
</tr>
</CFOUTPUT>
</CFIF>
<tr>
<td>&nbsp;</td>
</tr>
</form>
</table>
</cfif>

[The ScareCrow]

The user checks the checkbox of the record(s) they want to delete.

The name of this checkbox is perf_id

So if more than one is checked then when the form is submitted, this form
field will be a list of the id's to be deleted.

So the query will be
<CFQUERY NAME="deleteleader" DATASOURCE="#ds#">
DELETE from track_leaders
WHERE perf_id IN (#form.perf_id#)
</CFQUERY>


Ken

[stcoff]

Thanks, it is working now.

steve