Delete row of info via a checkbox, need help with SQL

[ajcwdc]

Sorry, but I'm brand new at this! I'm trying to let the user delete a row of information. I'm wondering if I'm associating the ID field with the DeleteId checkbox correctly. Do I need to index the table, or am I writing the SQL wrong? I do not get a syntax error, it simply doesn't delete. Any help would appreciated!
Thanks!


<!---part of a form that displays query results with a checkbox at the end of table data--->

<form name=&quot;delete&quot; action=&quot;delete.cfm&quot; method=&quot;post&quot;>
<cfoutput query=&quot;show_prop&quot;>
<tr>
<td nowrap>#type_of_furniture#</td>
<td width=&quot;20%&quot; align=center>#property_number#</td>
<td align=center>#date_received#</td>
<td align=center>#serial_number#</td>
<td width=&quot;50%&quot; align=center>#description#</td>
<td>
<input type=&quot;checkbox&quot; name=&quot;DeleteID&quot; value=&quot;#id#&quot;>
</td>
</tr>
</cfoutput>

</table>

<input type=&quot;submit&quot; Value=&quot;Delete Record&quot;>
</form>





<!--- delete.cfm --->

<CFIF IsDefined(&quot;form.delete&quot>
<CFQUERY datasource=&quot;surplus&quot; name=&quot;show_prop&quot;>
DELETE FROM Surplus_Property
WHERE ID = '#form.DeleteID#'
</CFQUERY>
</CFIF>

 

[Guest_imported]

You seem to have a error in delete.cfm. You test &quot;<CFIF IsDefined(&quot;form.delete&quot>&quot;, which is incorrect. There is no form variable called &quot;delete&quot;. You should be testing for &quot;form.DeleteID&quot;. Change that and it should work.

However, putting your delete functionallity in a separate file is not the best practice. Doing it your way (in a separate file), you should at least use a more unique filename, such as &quot;deleteByID.cfm&quot;. This will allow you to have other &quot;delete&quot; templates that do other things that will not be confused with each other. The better way, though, is to put the delete functionallity into the same file as the form as, illustrated below. Also, I recommend that you eliminate the use of &quot;IsDefined()&quot; whenever possible. Use of IsDefined() makes complex code much harder to understand. Now, here is how you might implement my suggestions:

<!--- main template: deleteSurplusProperty.cfm --->

<cfparam name=&quot;operation&quot; default=&quot;showForm&quot;>
<!--- 
Although not needed in this program, you could <cfparam> deleteID giving it a default value of &quot;&quot;.  Then you would check for its value rather than checking for IsDefined() 
--->

<!--- 
Do database operations first if we want to redisplay the form so that the user can delete another piece of property.  Otherwise, you MAY put the database operations at the end.
--->
<cfif operation EQ &quot;delete&quot;>
  <CFQUERY datasource=&quot;surplus&quot; name=&quot;delete_prop&quot;>
     DELETE FROM Surplus_Property
     WHERE ID = '#form.DeleteID#'  
  </CFQUERY>
  <cfoutput>
  <h3The property ###property_number# has been deleted</h3>
  </cfoutput>
</cfif>

<!--- Display the form to the user --->
<!--- 
if you don't want to re-display the form after deleting, put the follow code in the &quot;else&quot; block of the above &quot;if&quot; statement. 
--->
<cfquery name=&quot;all_property&quot; datasource=&quot;surplus&quot;>
  select type_of_furniture, property_number, date_received, serial_number, description, id
  from surplus_propery
</cfquery>

<form name=&quot;delete&quot; action=&quot;deleteSurplusProperty.cfm&quot; method=&quot;post&quot;>
   <cfoutput query=&quot;all_property&quot;>
    <tr>
    <td nowrap>#type_of_furniture#</td>
    <td width=&quot;20%&quot; align=center>#property_number#</td>
    <td align=center>#date_received#</td>
    <td align=center>#serial_number#</td>
    <td width=&quot;50%&quot; align=center>#description#</td>
    <td>
    <input type=&quot;checkbox&quot; name=&quot;DeleteID&quot; value=&quot;#id#&quot;>
    </td>
    </tr>
    </cfoutput>    
    
    </table>
    
    <input type=&quot;submit&quot; Value=&quot;Delete Record&quot;>
    <input type=&quot;hidden&quot; name=&quot;operation&quot; value=&quot;delete&quot;
    <input type=&quot;hidden&quot; name=&quot;property_number&quot; value=&quot;#property_number#&quot;
</form>

 

[ajcwdc]

Thank you very much! You're code certainly is a lot more thorough. I tried putting all the code onto on page as you suggested. However, there is still a problem with my query expression. I apologize for my lack of knowledge in this area.

I'm still confused what value is passed on. If this is a checkbox doesn't is pass a &quot;0&quot; if not checked, and a &quot;1&quot; if checked. How does the ID (primary key) stay with that data?
<input type=&quot;checkbox&quot; name=&quot;DeleteID&quot; value=&quot;#id#&quot;>

I'm gettting this error:

ODBC Error Code = 22005 (Error in assignment)


[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

 

[vipm]

If checkbox is not filled up, it doesn't get the value &quot;0&quot; but is &quot;undefined.&quot;

As for the data type mismatch error, the field &quot;id&quot; most probably is of type integer. Thus you shouldn't put quotes around it--this is only for strings. Try...

DELETE FROM Surplus_Property
WHERE ID = #form.DeleteID#

Hope this helps.

 

[ajcwdc]

It worked! I can't thank you guys enough! The quotes were hanging it up! Thanks again!