Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

What is wrong with this syntax

Posted on 2006-07-20
24
Medium Priority
?
437 Views
Last Modified: 2013-12-20
I get an error in Coldfusion that says incorrect syntax

Here is the code i am using

AND up.employee_no in ( #valuelist(getSubordinates.employee_no,", ")# )

Can anyone tell me what the error is
0
Comment
Question by:rudodoo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 5
  • 4
  • +1
24 Comments
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 17146901
Try:

AND up.employee_no in ( #preservesinglequotes(valuelist(getSubordinates.employee_no,", "))# )
0
 

Author Comment

by:rudodoo
ID: 17146944
Ok, was I supposed to enter something other than "preservesinglequotes"
0
 

Author Comment

by:rudodoo
ID: 17146992
because I stll received an error

this time its saying the preserve singel quotes is the error.

As you can see I am new in CFMX7 and I need some handholding
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 13

Expert Comment

by:usachrisk1983
ID: 17147013
No problem, can you paste your entire CFQUERY statement along with the exact error msg?
0
 

Author Comment

by:rudodoo
ID: 17147207
<cfquery name="qname" datasource="#DSN#">
      select       pg.projectgroup_id,
                  pg.mult_projects,
                  p.project_id,
                  p.date_created,
                  p.due_date,
                  r.rfq_id as RFQID,
                  r.work_order_num,
                  r.description,
                  r.site_locn,
                  r.status,
                  r.genpartno,
                  up.firstname,
                  up.lastname,
                  r.employee_no as engineer
      from T0023_ProjectGroup pg, T0016_Project p, T0003_RFQ r, t0001_user_profile up
      where pg.projectgroup_id = p.projectgroup_id
      and p.project_id = r.project_id
      and r.employee_no = up.employee_no
      and p.status < 10
      <cfif isdefined('form.RFQNumber') and form.RFQNumber NEQ ''>
            AND rfq_id = '#form.RFQNumber#'
      </cfif>
      <cfif isdefined('form.workOrderNumber') and form.workOrderNumber NEQ ''>
            AND work_order_num = '#form.workOrderNumber#'
      </cfif>      

      AND up.employee_no in ( #valuelist(getSubordinates.employee_no," ")# )
      
      order by pg.projectgroup_id,p.project_id,r.rfq_id
</cfquery>


Here is the error message

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 23: Incorrect syntax near ')'.  
 
 
Resources:
Enable Robust Exception Information to provide greater detail about the source of errors. In the Administrator, click Debugging & Logging > Debugging Settings, and select the Robust Exception Information option.
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.

 
Browser   Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)
Remote Address   10.33.38.31
Referrer   http://companyURL/login.cfm 
Date/Time   20-Jul-06 10:15 AM
 
0
 
LVL 10

Expert Comment

by:rob_lorentz
ID: 17147271
what data type is employee_no?
0
 
LVL 10

Expert Comment

by:rob_lorentz
ID: 17147299
is employee_no is integer this will work.

 <cfquery name="qname" datasource="#DSN#">
     select      pg.projectgroup_id,
               pg.mult_projects,
               p.project_id,
               p.date_created,
               p.due_date,
               r.rfq_id as RFQID,
               r.work_order_num,
               r.description,
               r.site_locn,
               r.status,
               r.genpartno,
               up.firstname,
               up.lastname,
               r.employee_no as engineer
     from T0023_ProjectGroup pg, T0016_Project p, T0003_RFQ r, t0001_user_profile up
     where pg.projectgroup_id = p.projectgroup_id
     and p.project_id = r.project_id
     and r.employee_no = up.employee_no
     and p.status < 10
     <cfif isdefined('form.RFQNumber') and form.RFQNumber NEQ ''>
          AND rfq_id = '#form.RFQNumber#'
     </cfif>
     <cfif isdefined('form.workOrderNumber') and form.workOrderNumber NEQ ''>
          AND work_order_num = '#form.workOrderNumber#'
     </cfif>    

     AND up.employee_no in ( #valuelist(getSubordinates.employee_no)# )
     
     order by pg.projectgroup_id,p.project_id,r.rfq_id
</cfquery>
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 17147303
If your employee_no column is a numeric type, use:

AND up.employee_no IN (#valuelist(getSubordinates.employee_no,",")#)


Or if it's a text type field use:

AND up.employee_no IN (#quotedvaluelist(getSubordinates.employee_no,",")#)

0
 

Author Comment

by:rudodoo
ID: 17147312
What changes did you make?
0
 

Author Comment

by:rudodoo
ID: 17147338
sorry didn't see the previous posting


Let me test and see if it works
0
 

Author Comment

by:rudodoo
ID: 17147457
I got an error using both types of code.  This problem occurs whenever someone enters their ID and Password and clicks submit.  Also, I believe this is only limited to certain users
0
 

Author Comment

by:rudodoo
ID: 17147533
Let me check some other issues with this and I'll get back with you
0
 

Author Comment

by:rudodoo
ID: 17147565
Yes, I did some double checking and this error only occurs on certain users.  So far just 2 that we know of
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 17147579
Anything look different on those users if you look at the raw data in the db?
0
 

Author Comment

by:rudodoo
ID: 17147722
Not as far as values but I wonder if it could be the attributes that are used
0
 

Author Comment

by:rudodoo
ID: 17147766
No I don't think that is the problem either
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 17147931
Please copy/paste the entire error.  Also, do you have debugging turned on?  On one of the failed tries, show us the SQL statement if it gets that far.  It should look something like:

Queries

get_data (Records=7, Time=0ms)
SQL =
SELECT      *
FROM      activations_users

test2 (Records=7, Time=0ms)
SQL =
SELECT      *
FROM      activations_users
WHERE      alias IN ('a','b','c','d',e','f','g')
0
 
LVL 10

Accepted Solution

by:
rob_lorentz earned 500 total points
ID: 17147956
i suspect that there are no records returned by the getSubordinates query. where field in () is not valid syntax.

try wrapping that part of your where clause with an if.

 <cfquery name="qname" datasource="#DSN#">
     select      pg.projectgroup_id,
               pg.mult_projects,
               p.project_id,
               p.date_created,
               p.due_date,
               r.rfq_id as RFQID,
               r.work_order_num,
               r.description,
               r.site_locn,
               r.status,
               r.genpartno,
               up.firstname,
               up.lastname,
               r.employee_no as engineer
     from T0023_ProjectGroup pg, T0016_Project p, T0003_RFQ r, t0001_user_profile up
     where pg.projectgroup_id = p.projectgroup_id
     and p.project_id = r.project_id
     and r.employee_no = up.employee_no
     and p.status < 10
     <cfif isdefined('form.RFQNumber') and form.RFQNumber NEQ ''>
          AND rfq_id = '#form.RFQNumber#'
     </cfif>
     <cfif isdefined('form.workOrderNumber') and form.workOrderNumber NEQ ''>
          AND work_order_num = '#form.workOrderNumber#'
     </cfif>    
     <cfif getSubordinates.recordCount>
         AND up.employee_no in ( #valuelist(getSubordinates.employee_no)# )
     </cfif>    
     order by pg.projectgroup_id,p.project_id,r.rfq_id
</cfquery>
0
 

Author Comment

by:rudodoo
ID: 17148443
Here

Exceptions

12:27:29.029 - Database Exception - in C:\Inetpub\wwwroot\app\RFQsummaryMgr.cfm : line 120
          Error Executing Database Query.
          


--------------------------------------------------------------------------------
SQL Queries

getOverdue (Datasource=RFQPRD, Time=31ms, Records=0) in C:\Inetpub\wwwroot\pincs\RFQsummaryMgr.cfm @ 12:27:29.029

      select p.project_id, p.PROJECT_DESCRIPTION, rfq_id, workemail
      from      t0016_project p, t0003_rfq r, T0001_USER_PROFILE u
      where      p.due_date < '20-Jul-06 12:27'
        and      p.project_id = r.project_id
        and       p.status = 1
        and       r.status = 2
        and      u.USER_ID = p.ENG_ID

getSubordinates (Datasource=RFQPRD, Time=0ms, Records=0) in C:\Inetpub\wwwroot\pincs\RFQsummaryMgr.cfm @ 12:27:29.029

      select employee_no
      from      t0001_user_profile
      where      supervisor = '0030310'

name (Datasource=RFQPRD, Time=0ms, Records=1) in C:\Inetpub\wwwroot\pincs\RFQsummaryMgr.cfm @ 12:27:29.029

      SELECT firstname, lastname
      FROM T0001_USER_PROFILE
      WHERE EMPLOYEE_NO = '30310'
0
 
LVL 10

Expert Comment

by:rob_lorentz
ID: 17148555
try the last query I posted.

getSubordinates query isnt returning any records.
0
 

Author Comment

by:rudodoo
ID: 17148573
I believe it worked let me see if this is what they wanted
0
 

Author Comment

by:rudodoo
ID: 17148593
We are testing it right now.  Would you mind explaining to me what you did so I can tell everyone else
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 17148598
rob is correct.

WHERE something=whatever AND up.employee_no IN ()

is no good.
0
 
LVL 7

Expert Comment

by:aseusainc
ID: 17148604
Rob's code:

<cfif getSubordinates.recordCount>
         AND up.employee_no in ( #valuelist(getSubordinates.employee_no)# )
</cfif>  

Basically this only adds the AND statement if the getSubordinates query returns 1 or more records.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question