I am having difficulties outputting a ListQualify in a query.
Here is the query
<cfset TodayDate = DateFormat(Now(), "mm/dd/yyyy")>
<!--- get the groupid of movement classes --->
<cfquery name="GroupOne" datasource="#dsn#">
SELECT Distinct(GroupID), EventName
FROM Calendar
WHERE EventType = 'Movement Class'
and CalendarDate > '#TodayDate#'
GROUP BY GroupID, Eventname
ORDER BY EventName ASC
</cfquery>
<!--- set GroupID to comma delimited list --->
<cfset MovementList = ValueList(GroupOne.GroupID,',')>
<!--- qualify the list with single quotes --->
<cfset MovementList = ListQualify(MovementList," ' ", ",","ALL")>
The output
<cfoutput>#MovementList#</cfoutput> gives the following
'6FA07A77-3048-2A59-E85C85CF523051C6','6FA24EAB-3048-2A59-E83D53893307A2BB'
BUT when i put the MovementList in the following query
<cfquery name="MovementClasses" datasource="#dsn#">
SELECT MIN(CalendarDate) as MinDate, MAX(CalendarDate) as
MaxDate, EventName, GroupID
FROM Calendar
WHERE GroupID IN (#MovementList#)
GROUP BY EventName, GroupID
</cfquery>
I get the following error. For some reason the list becomes qualified with
double quotes. Here is the error
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 4: Incorrect syntax near '6'.
25 : MaxDate, EventName, GroupID
26 : FROM Calendar
27 : WHERE GroupID IN (#MovementList#)
28 : GROUP BY EventName, GroupID
29 : </cfquery>
SQL SELECT MIN(CalendarDate) as MinDate, MAX(CalendarDate) as MaxDate,
EventName, GroupID FROM Calendar WHERE GroupID IN
(''6FA07A77-3048-2A59-E85C85CF523051C6'',''6FA24EAB-3048-2A59-E83D53893307A2BB'')
GROUP BY EventName, GroupID
How do I correct this?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218662
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=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
