Solved

What is wrong with this syntax

Posted on 2006-07-20
24
427 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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 integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now