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
Solved

What is wrong with this syntax

Posted on 2006-07-20
24
430 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
  • 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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
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 125 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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Reverse Proxy Server 6 91
Column Spacing 3 92
Systems talking to each other 5 141
How to find the first line of javascript code-behind of a web page button. 7 87
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

839 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