A nice way to debug dynamically built queries is like this:
<cfset qualDelPhotoList = ListQualify(delPhotoList,"'",",","all") />
<cfoutput><pre>
<!--- <cfquery name="qdelPhoto" datasource="#request.optomDS#"> --->
Delete from optomSitePhotos
WHERE photoLocation IN ('#qualDelPhotoList#')
<!--- </cfquery> --->
</pre></cfoutput>
<cfabort>
When you run that, you'll see the query as it is passed to the DB. (Or look
in the debug if it's there).
Copy the generated query into Query Analyser and debug in there.
Could you show us the generated SQL string?
Ade
-----Original Message-----
From: Joy Holman [mailto:[EMAIL PROTECTED]
Sent: 29 September 2005 13:55
To: CF-Talk
Subject: problem using a text list variable in a query statement
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"><!---see if there are any
photos in the database to display.--->
<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>...
......<!---delete existing photos--->
<cfif Len(form.delete1) or Len(form.delete2)>
<cfset delPhotoList = "" />
<!---try to combine values in list to send to sql query(above)--->
<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>...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219597
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
