I dunno your table structure, perhaps in your Oracle DB the userid you are testing does not exist? Perhaps the groupid for your userid DNE? Although you should have the FK relationship set to not allow disjoint sets of groupids in each of your tables. You may also want to add ticks around the were clause value WHERE UserID = '#Session.ID#' just a good idea.
Know, are you sure your session.userid is populated? <cfdump var="#session#"> to see. Also, does your SQL work against your Oracle DB via SQL+ or Toad? Doug >-----Original Message----- >From: Ian Vaughan [mailto:[EMAIL PROTECTED] >Sent: Monday, July 14, 2003 9:52 AM >To: CF-Talk >Subject: Re: Code Errors with Coldfusion - Please Help ? > > >Douglas > >It seems as if the session.groupname is not getting populated ? > >So I am not sure if this query is not working as it should?? >Even though it >works as it should against an Access database. > ><cfquery name="UserGroups" datasource="intranetv8"> >SELECT * FROM usergroupmembership, groups WHERE UserID = #Session.ID# >AND >usergroupmembership.groupid = groups.groupid ></cfquery> > ><cfset session.groupname = QuotedValueList(UserGroups.groupname)> > > > >----- Original Message ----- >From: <[EMAIL PROTECTED]> >To: "CF-Talk" <[EMAIL PROTECTED]> >Sent: Monday, July 14, 2003 2:23 PM >Subject: RE: Code Errors with Coldfusion - Please Help ? > > >> 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/linksde >tail.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/linksde >tail.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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
