ugh, using single ticks like that completely changed your SQL. What you should be
doing is checking if your first query returned any rows. If not, do not use the IN
clause.
something like this
SELECT *
FROM itlinks
WHERE linkarea='#URL.area#'
AND
(
<cfif UserGroups.recordcount GT 0>
security IN (#PreserveSingleQuotes(session.groupname)#)
<cfelse>
security = 'all'
</cfif>
)
ORDER BY linkid desc
Or heck better yet perhaps is do...this depends on your security model you built
<cfset session.groupname = QuotedValueList(UserGroups.groupname)>
<cfset session.groupname = ListAppend(session.groupname, 'all')>
Then your SQL coupld be
SELECT *
FROM itlinks
WHERE linkarea='#URL.area#'
AND
( security IN (#PreserveSingleQuotes(session.groupname)#) )
ORDER BY linkid desc
Doug
>-----Original Message-----
>From: Ian Vaughan [mailto:[EMAIL PROTECTED]
>Sent: Monday, July 14, 2003 8:37 AM
>To: CF-Talk
>Subject: Re: Code Errors with Coldfusion - Please Help ?
>
>
>I got rid of the error message by placing single quotes around
>it such as
>
>security IN '(#PreserveSingleQuotes(session.groupname)#)'
>
>However I turned on debugging and the query is still showing
>up with nothing
>in it
>
>Queries
>
>UserGroups (Records=0, Time=169ms)
>SQL =
>SELECT * FROM usergroupmembership, groups WHERE UserID = 75
>AND
>usergroupmembership.groupid = groups.groupid
>
>list (Records=3, Time=153ms)
>SQL =
>SELECT *
>FROM itlinks
>WHERE linkarea='Database Development/Support'
>AND
>(
>security = 'all'
>OR
>security IN '()'
>)
>ORDER BY linkid desc
>
>Where is in Access it works properly and It looks like
>
>Queries UserGroups (Records=5, Time=60ms)
>SQL =
>SELECT * FROM usergroupmembership, groups WHERE UserID = 1
>AND
>usergroupmembership.groupid = groups.groupid
>
>list (Records=2, Time=10ms)
>SQL =
>SELECT *
>FROM itlinks
>WHERE linkarea='Web Development/Support'
>AND
>(
>security IN ('Techsa','System Support','Technical Support','Finance &
>Corporate I.T.','ICT')
>OR
>security = 'all'
>)
>ORDER BY linkid desc
>
>
>
>----- Original Message -----
>From: "Tyagi, Badal" <[EMAIL PROTECTED]>
>To: "CF-Talk" <[EMAIL PROTECTED]>
>Sent: Monday, July 14, 2003 9:59 AM
>Subject: RE: Code Errors with Coldfusion - Please Help ?
>
>
>> Now this shows that your first query is running fine as it
>was earlier but
>> its record count is zero, therefore in variable "session.groupname"
>nothing
>> is stored and without checking its record count you are
>performing another
>> Query where you are using this variable which is throwing
>error. So first
>> check why your first Query(UserGroups) is returning zero recordcount.
>>
>> regards
>> badal
>>
>> -----Original Message-----
>> From: Ian Vaughan [mailto:[EMAIL PROTECTED]
>> Sent: Monday, July 14, 2003 2:22 PM
>> To: CF-Talk
>> Subject: Re: Code Errors with Coldfusion - Please Help ?
>>
>>
>> Just changed that line to what you suggested and am getting an Oracle
>error
>>
>> Queries
>>
>> UserGroups (Records=0, Time=26ms)
>> SQL =
>> SELECT * FROM usergroupmembership, groups WHERE UserID = 1
>> AND
>> usergroupmembership.groupid = groups.groupid
>>
>> Error Occurred While Processing Request
>> Error Diagnostic Information
>> Oracle Error Code = 936
>>
>> ORA-00936: missing expression
>>
>>
>>
>> SQL = "SELECT * FROM itlinks WHERE linkarea='Emerging
>Technologies'
>> AND ( security IN () OR security = 'all' ) ORDER BY linkid desc"
>>
>> Data Source = "INTRANETV8"
>>
>>
>> The error occurred while processing an element with a general
>> identifier of (CFQUERY), occupying document position (18:1)
>to (18:45) in
>> the template file
>>
>/usr/netscape/server4/docs/intranet/itintranet/itlinks/linksdetail.cfm.
>>
>>
>> Date/Time: Mon Jul 14 09:55:07 2003
>> Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)
>> Remote Address: 121.100.25.2
>> HTTP Referer:
>> http://intranet.neath-porttalbot.gov.uk/itintranet/itlinks/links.cfm
>> Query String: area=Emerging%20Technologies
>>
>>
>>
>>
>> Please inform the site administrator that this error has
>occurred (be sure
>> to include the contents of this page in your message to the
>administrator).
>>
>>
>>
>>
>---------------------------------------------------------------
>-----------
>--
>> ----
>>
>> ----- Original Message -----
>> From: "Tyagi, Badal" <[EMAIL PROTECTED]>
>> To: "CF-Talk" <[EMAIL PROTECTED]>
>> Sent: Monday, July 14, 2003 9:46 AM
>> Subject: RE: Code Errors with Coldfusion - Please Help ?
>>
>>
>> > I think you are using wrong Query name, this line should be...
>> >
>> > <cfset session.groupname = QuotedValueList(UserGroups.groupname)>
>> >
>> >
>> > regards
>> > badal Tyagi
>> > HCL Perot Systems
>> > Noida, India
>> >
>> >
>> > -----Original Message-----
>> > From: Ian Vaughan [mailto:[EMAIL PROTECTED]
>> > Sent: Monday, July 14, 2003 2:01 PM
>> > To: CF-Talk
>> > Subject: Code Errors with Coldfusion - Please Help ?
>> >
>> >
>> > Hi
>> >
>> > I would appreciarte it if someone on the list could try to
>help me solve
>> > this problem please, I am trying the following code
>against an Oracle
>> > database and it is returning the following error from this code ?
>> >
>> > <cfquery name="UserGroups" datasource="intranetv8">
>> > SELECT * FROM usergroupmembership, groups WHERE UserID =
>#Session.ID#
>> > AND
>> > usergroupmembership.groupid = groups.groupid
>> > </cfquery>
>> > <cfset session.groupname = QuotedValueList(Groups.groupname)>
>> >
>> > <CFQUERY name="list" datasource="intranetv8">
>> > SELECT *
>> > FROM itlinks
>> > WHERE linkarea='#URL.area#'
>> > AND
>> > (
>> > security IN (#PreserveSingleQuotes(session.groupname)#)
>> > OR
>> > security = 'all'
>> > )
>> > ORDER BY linkid desc
>> > </CFQUERY>
>> > Error Occurred While Processing Request
>> > Error Diagnostic Information
>> >
>> > An error occurred while evaluating the expression:
>> >
>> >
>> > session.groupname = QuotedValueList(groups.groupname)
>> >
>> >
>> >
>> > Error near line 14, column 7.
>>
>>
>---------------------------------------------------------------
>-----------
>> >
>> > Parameter 1 of function QuotedValueList which is now
>> > "groups.groupname" must be pointing to a valid query name
>> >
>> >
>> > The error occurred while processing an element with a general
>> > identifier of (CFSET), occupying document position (14:1)
>to (14:61) in
>> the
>> > template file
>> >
>/usr/netscape/server4/docs/intranet/itintranet/itlinks/linksdetail.cfm.
>> >
>> >
>> > Date/Time: Mon Jul 14 09:32:44 2003
>> > Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)
>> > Remote Address: 121.100.25.2
>> > HTTP Referer:
>> >
>http://intranet.neath-porttalbot.gov.uk/itintranet/itlinks/link
s.cfm
> > Query String: area=Emerging%20Technologies
> >
> >
> >
> > It looks as if it does not like these parts ?
> >
> > --------------
> > <cfset session.groupname = QuotedValueList(Groups.groupname)
> >
> > and
> >
> > security IN (#PreserveSingleQuotes(session.groupname)#)
> > ----------------------------------
> >
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
