What is wrong with this syntax

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
rudodooAsked:
Who is Participating?
 
rob_lorentzConnect With a Mentor Commented:
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
 
usachrisk1983Commented:
Try:

AND up.employee_no in ( #preservesinglequotes(valuelist(getSubordinates.employee_no,", "))# )
0
 
rudodooAuthor Commented:
Ok, was I supposed to enter something other than "preservesinglequotes"
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
rudodooAuthor Commented:
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
 
usachrisk1983Commented:
No problem, can you paste your entire CFQUERY statement along with the exact error msg?
0
 
rudodooAuthor Commented:
<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
 
rob_lorentzCommented:
what data type is employee_no?
0
 
rob_lorentzCommented:
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
 
aseusaincCommented:
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
 
rudodooAuthor Commented:
What changes did you make?
0
 
rudodooAuthor Commented:
sorry didn't see the previous posting


Let me test and see if it works
0
 
rudodooAuthor Commented:
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
 
rudodooAuthor Commented:
Let me check some other issues with this and I'll get back with you
0
 
rudodooAuthor Commented:
Yes, I did some double checking and this error only occurs on certain users.  So far just 2 that we know of
0
 
aseusaincCommented:
Anything look different on those users if you look at the raw data in the db?
0
 
rudodooAuthor Commented:
Not as far as values but I wonder if it could be the attributes that are used
0
 
rudodooAuthor Commented:
No I don't think that is the problem either
0
 
aseusaincCommented:
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
 
rudodooAuthor Commented:
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
 
rob_lorentzCommented:
try the last query I posted.

getSubordinates query isnt returning any records.
0
 
rudodooAuthor Commented:
I believe it worked let me see if this is what they wanted
0
 
rudodooAuthor Commented:
We are testing it right now.  Would you mind explaining to me what you did so I can tell everyone else
0
 
aseusaincCommented:
rob is correct.

WHERE something=whatever AND up.employee_no IN ()

is no good.
0
 
aseusaincCommented:
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
All Courses

From novice to tech pro — start learning today.