problem using text list variable in query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JoyRose - 29 Sep 2005 13:50 GMT

:rose;

I create a list from form variables that I want to use in a query.

This is where it fails to delete the records (though the query produces no
error:

<cfset qualDelPhotoList = ListQualify(delPhotoList,"'",",","all") />
<cfquery name="qdelPhoto" datasource="#request.optomDS#">
Delete from optomSitePhotos
WHERE photoLocation IN ('#qualDelPhotoList#')
</cfquery>

The list is: photo1.jpg,photo2.gif or 'photo1.jpg,'photo2.jpg' (depending on
whether I use List Quality with the list). It doesn't seem to matter.

The single quotes around #qualDelPhotoList# in the query is required or the
query fails.

Any ideas how to make this work?
Thanks.

Here is a fuller picture of the code:

...<cfinclude template="qry_SitePhotos.cfm">
<cfif session.qSitePhotosRecordcount gte 1>
    <table width="100%">
    <tr>
    <cfif session.qSitePhotosRecordcount is 2>
        <td>These photos currently display with your facility's information. Click
on the appropriate button(s) if you wish to remove either or both photos.
    <cfelseif session.qSitePhotosRecordcount is 1>
        <td>This photo currently display with your facility's information. Click on
the appropriate button if you wish to remove the photo.
    </cfif>
    <cfset x = 1 />
    <cfloop query="qSitePhotos">
    <p><input type="checkbox" name="delete#x#"
value="#qSitePhotos.photoLocation#"><img src="img/#qSitePhotos.photoLocation#"
style="width:150px;height:150px"></p>
    <cfset x = x + 1>
    </cfloop>
    </td>
    </tr>
    </table>
</cfif>...

...
<cfif Len(form.delete1) or Len(form.delete2)>
    <cfset delPhotoList = "" />
    
    <cfif Len(form.delete1)>
        <cfset delPhotoList = listAppend(delPhotoList,"#form.delete1#") />

    </cfif>
    <cfif Len(form.delete2)>
        <cfset delPhotoList = listAppend(delPhotoList,"#form.delete2#") />

    </cfif>
    <cfset qualDelPhotoList = ListQualify(delPhotoList,"'",",","all") />


<cfquery name="qdelPhoto" datasource="#request.optomDS#">
Delete from optomSitePhotos
WHERE photoLocation IN ('#qualDelPhotoList#')
</cfquery>

</cfif>...

Reply to this Message

Dan Bracuk - 29 Sep 2005 14:17 GMT

Change this:
WHERE photoLocation IN ('#qualDelPhotoList#')

to this
WHERE photoLocation IN (#listqualify(qualDelPhotoList)#)
assuming the photolocation field is char

Reply to this Message

JoyRose - 29 Sep 2005 14:26 GMT

I got the error:
Parameter validation error for function LISTQUALIFY.

Reply to this Message

Dan Bracuk - 29 Sep 2005 14:38 GMT

Oops, I didn't notice that you used it to create your intial list.

In that case, just take the quotes out of
WHERE photoLocation IN ('#qualDelPhotoList#')

Reply to this Message

JoyRose - 29 Sep 2005 14:45 GMT

I get a db query execution error if I do that.

Reply to this Message

mxstu - 29 Sep 2005 18:52 GMT

If there is no other reason you are dynamically naming the checkboxes, it would
be easier to just give all the checkboxes the same name: "deletePhoto". Then
all selections will be passed to the action page as a comma delimited list
which you can use in your query. Note - Use the correct data type for
cfqueryparam. If you are using a string value, you might want to think about
using a numeric ID instead. Database operations using numeric ID's are
typically faster than those using strings.


<cfif listLen(form.deletePhoto) gt 0>
<cfquery name="qdelPhoto" datasource="#request.optomDS#">
DELETE FROM optomSitePhotos
WHERE photoLocation IN
(
<cfqueryparam value="#form.deletePhoto#" cfsqltype="cf_sql_varchar"
list="yes">
)
</cfquery>
</cfif>

Reply to this Message

JoyRose - 29 Sep 2005 19:57 GMT

Thank you!

This did it:
WHERE photoLocation IN (<cfqueryparam value = "#delPhotoList#"
cfsqltype="CF_SQL_VARCHAR" list="yes">)

I'll use the proper form list, but even with using the list I made from the
two form elements (without using "ListQualify'), this worked. Must be the
attribute: list="yes" with the cfqueryparam tag!

Reply to this Message

Webmaster Forum / ColdFusion / Advanced Techniques / September 2005

problem using text list variable in query