Delete Record In Access DB

[NoWizod]

I have been attempting to delete a record via the use of a form where selecting Delete will provide the correct RequestID to complete the operation, however I always get all IDs, so all records would be deleted. I am not sure where I'm going wrong, but I've been working on this one issue for a couple of hours and just need some fresh thoughts. Thanks!!


<cfloop query="getData">
<cfoutput>
<tr><td align="center">#Lastname#</td><td align="right">#DateFormat(ScheduleDateTime,"ddd, mmmm dd, yyyy")#</td><td>#TimeFormat(ScheduleDateTime,"hh:mm:sstt")#</td><td align="center">#PropertyID#</td><td>#ClientName#</td><td><input type="submit" value="Delete"><cfoutput>#RequestID#</cfoutput></td></tr>
<input type="hidden" name="RequestID" value="#RequestID#">
</cfoutput>
</cfloop>

 

[cfStarlight]

Either use separate forms:

<table border="1">
<cfoutput query="getData">
<form method="post" action="yourActionPage.cfm">
<tr><td align="center">#Lastname#</td>
	<td align="right">#DateFormat(ScheduleDateTime,"ddd, mmmm dd, yyyy")#</td>
	<td>#TimeFormat(ScheduleDateTime,"hh:mm:sstt")#</td>
	<td align="center">#PropertyID#</td>
	<td>#ClientName#</td><td>
	<input type="submit" value="Delete"><cfoutput>#RequestID#</cfoutput></td></tr>
	<input type="hidden" name="RequestID" value="#RequestID#">
</form>	
</cfoutput>
</table>

Or use javascript to store the record id and submit the form.

<script type="text/javascript">
function deleteRequest(idToBeDeleted) {
   var formObject = document.getElementById('theForm');
   var savedIDField  = document.getElementById('requestID');
   savedIDField.value = idToBeDeleted;
   formObject.submit();
}
</script>
<table border="1">
   <form id="theForm" method="post" action="yourActionPage.cfm">
   <cfoutput query="getData">
   <tr><td align="center">#Lastname#</td>
	   <td align="right">#DateFormat(ScheduleDateTime,"ddd, mmmm dd, yyyy")#</td>
	   <td>#TimeFormat(ScheduleDateTime,"hh:mm:sstt")#</td>
	   <td align="center">#PropertyID#</td>
	   <td>#ClientName#</td><td>
	   <input type="button" value="Delete" onClick="deleteRequest(#RequestID#);"></td>
   </tr>
   </cfoutput>
	<input type="hidden" id="requestID" name="requestID" value="">
</form>	
</table>

 

[Sarky78]

NoWizod,

I'd stick with the way that you have it right now, but add a checkbox element to the output, and then have a single submit button at the bottom. Something like this:

<form name="whatever" action="yourCFMPage.cfm" method="post">
<cfoutput>
<cfloop query="getData">
<tr><td align="center">#getData.Lastname#</td><td align="right">#DateFormat(getData.ScheduleDateTime,"ddd, mmmm dd, yyyy")#</td><td>#TimeFormat(getData.ScheduleDateTime,"hh:mm:sstt")#</td><td align="center">#getData.PropertyID#</td><td>#getData.ClientName#</td><td><input type="checkbox" name="RequestID" value="#getData.RequestID#" />#getData.RequestID#</td></tr>
</cfloop>
</cfoutput>
<input type="submit" value="Delete">
</form>

when the form is submitted you will get a single RequestID field submitted but this contain a list of values, you can then loop the list like this:

<cfloop list="#Form.RequestID#" index="i">
<cfquery datasource="">
  delete
  from yourtable
  where YourID = <cfqueryparam cfsqltype="the_type_of_your_field" value="#i#">
</cfquery>
</cfloop>

or use a single query like this:

<cfquery datasource="">
  delete
  from yourtable
  where YourID = <cfqueryparam cfsqltype="the_type_of_your_field" value="#Form.RequestID#" list="Yes">
</cfquery>

Take a look at the live docs (

http://livedocs.adobe.com/coldfusio...t=ColdFusion_Documentation&file=part_cfm.htm)

for what field type you need to use on the query param

Hope this helps!

Tony

 

[NoWizod]

Thanks for your help! I am attempting to teach myself from a Course Technology book (past the halfway point) and this is the first time I've been so stuck. I was trying to get it to match the Hands-On Project (requirements) they supplied at the end of the chapter, and thought at first glance it should be easy thinking that the information as it loops through could be set to the delete input, but no dice. I think the checkbox makes good sense and I'll probably go with that for now. But I'm sure I'll be revisiting it as I go through to see if I can get it to replicate what they show. Everything else works as the first test deleted all records in the database -- oops.
THANKS!

 

[cfStarlight]

NoWizod,

I misunderstood what you were trying to do. I saw the delete buttons on each row and thought you wanted to set up a form that would delete only a single id (not multiple ids).

Sarky78's suggestion is the way to go. The second query

or use a single query like this:

<cfquery datasource="">
  delete
  from yourtable
  where YourID = <cfqueryparam cfsqltype="the_type_of_your_field" value="#Form.RequestID#" list="Yes">
</cfquery>

just needs a slight modification. It should use where in (...) instead of the equals = operator. And some sort of empty list check if its appropriate.

<cfquery datasource="">
  delete
  from yourtable
  where YourID IN 
  ( 
   <cfqueryparam cfsqltype="the_type_of_your_field" 
       value="#Form.RequestID#" 
       list="Yes"> 
   )
</cfquery>