?
Solved

why wont this query work?

Posted on 2003-03-14
28
Medium Priority
?
150 Views
Last Modified: 2013-12-24
         <CFSET Contactid = ListFirst(GetAuthUser())>
          <CFSET Team = ListRest(GetAuthUser())>
          <CFQUERY NAME="get_products" DATASOURCE="test_db" DBTYPE="ODBC">
               SELECT distinct(product) FROM qry_screen_output
               WHERE ContactID = #Contactid#
               AND Team = #Team#                    
               Order by PRODUCT
          </CFQUERY>

Why would this not work, cfset contactid  = 123 and team = HYPE

when i remove the WHERE and AND clause this works...anyone have an idea? my db is sound too, qry_screen_output is a table and ContactID and Team are column names
0
Comment
Question by:finnstone
[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
  • 15
  • 7
  • 3
  • +3
28 Comments
 
LVL 5

Expert Comment

by:JimV_ATL
ID: 8138648
Try this

SELECT distinct product
FROM qry_screen_output
WHERE ContactID = #Contactid#
AND Team = '#Team#'                    
Order by PRODUCT
0
 

Expert Comment

by:DynAstY
ID: 8138752
Team is a string data type and does need single quotes around it.

Example:
Where Team = '#TEAM#'
0
 

Author Comment

by:finnstone
ID: 8138805
that doesnt work, already tried it. .. my email is tob_baker@yahoo.com , email me asking for the database maybe i am doing something really dumb.

here is my code anyhow

<CFCOMPONENT>
     
     <CFFUNCTION access="remote" name="list_products" returntype="string" hint="Getting the list of products">

            <!--- for now i am harcoding contactid and team to 122 and hp, you can pass these as arguments  --->
          <CFSET Contactid = ListFirst(GetAuthUser())>
          <CFSET Team = ListRest(GetAuthUser())>
          <CFQUERY NAME="get_products" DATASOURCE="test_db" DBTYPE="ODBC">
               SELECT distinct(PRODUCT) FROM qry_screen_output
               WHERE ContactID = #Contactid#
               AND Team = '#Team#'                
               Order by PRODUCT
          </CFQUERY>
          <CFSET products = Valuelist(get_products.product)>
          <CFRETURN products>
     </CFFUNCTION>
     
     <CFFUNCTION access="remote" name="list_colors" returntype="string" hint="Getting the list of colors">
          <CFARGUMENT name="selProduct" type="string" required="true">    
 
          <CFSET selProduct = ListQualify(selProduct,"'",",")>
           
            <CFSET Contactid = ListFirst(GetAuthUser())>
          <CFSET Team = ListRest(GetAuthUser())>
           
          <CFQUERY NAME="get_colors" DATASOURCE="test_db" DBTYPE="ODBC">
               SELECT distinct(COLOR) FROM qry_screen_output
               WHERE ContactID = #Contactid#
               AND Team = '#Team#'
               AND PRODUCT in (#PreserveSingleQuotes(selProduct)#)
               Order by COLOR
          </CFQUERY>
          <CFSET colors = Valuelist(get_colors.color)>
          <CFRETURN colors>
     </CFFUNCTION>    
     
     <CFFUNCTION access="remote" name="str_sales" returntype="string" hint="Getting the list of colors">
            <CFARGUMENT name="selProduct" type="string" required="true">    
          <CFARGUMENT name="selColor" type="string" required="true">
           
          <CFSET selProduct = ListQualify(selProduct,"'",",")>
          <CFSET selColor = ListQualify(selColor,"'",",")>
           
            <CFSET Contactid = ListFirst(GetAuthUser())>
          <CFSET Team = ListRest(GetAuthUser())>
           
          <CFQUERY NAME="get_sales" DATASOURCE="test_db" DBTYPE="ODBC">
               SELECT sum(SALES) as sumsales FROM qry_screen_output
               WHERE ContactID = #Contactid#
               AND Team = '#Team#'
               AND PRODUCT in (#preservesinglequotes(selProduct)#)
               AND COLOR in (#preservesinglequotes(selColor)#)
          </CFQUERY>
          <CFSET sales = get_sales.sumsales>          
          <CFRETURN sales>
     </CFFUNCTION>    
     
</CFCOMPONENT>
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Expert Comment

by:DynAstY
ID: 8138871
You said "when i remove the WHERE and AND clause this works" is the ContactID also a string try single quotes on that also.
0
 

Expert Comment

by:DynAstY
ID: 8138881
Oh yea and the DISTICT is wrong. As JimV stated

SELECT distinct(PRODUCT) should be Select DISTINCT Product
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8138909
Try removing just one of the 2 WHERE clauses. I'm guessing you just don't have any data setup for that criteria. Open the DB and see if you can find a row (By hand) that has a contactid  of 123 and a team equal to 'HYPE'.

Also try the following:

          <CFQUERY NAME="get_products" DATASOURCE="test_db" DBTYPE="ODBC">
              SELECT distinct(product) FROM qry_screen_output
              WHERE ContactID = #Contactid#
              AND LTRIM(RTRIM(Team)) = '#Team#                   '
              Order by PRODUCT
         </CFQUERY>
0
 

Author Comment

by:finnstone
ID: 8138938
here is where i defined contacid and team vaiarbles of the cfc in the CFLOGIN cfm

    <!--- Find record with this Username/Password --->
    <!--- If no rows returned, password not valid --->
      <!--- userroleID stuff superflous, im not going to use it like contactid --->
    <CFQUERY NAME="GetUser" DATASOURCE="#Datasource#">
      SELECT ContactID, Team, UserRoleName
      FROM Login_table LEFT OUTER JOIN UserRoles
        ON Login_table.UserRoleID = UserRoles.UserRoleID
      WHERE LoginID    = '#Form.UserLogin#'
        AND Password = '#Form.UserPassword#'
    </CFQUERY>
   
    <!--- If the username and password are correct... --->
    <CFIF GetUser.RecordCount EQ 1>
      <!--- Tell ColdFusion to consider the user "logged in" --->
      <!--- For the NAME attribute, we will provide the user's --->
      <!--- ContactID number and first name, separated by commas --->
      <!--- Later, we can access the NAME value via GetAuthUser() --->
      <CFLOGINUSER
        NAME="#GetUser.ContactID#,#GetUser.Team#"
        PASSWORD="#FORM.UserPassword#"
        ROLES="#GetUser.UserRoleName#">      
0
 

Author Comment

by:finnstone
ID: 8138947
contactid is a number
0
 

Author Comment

by:finnstone
ID: 8138964
Try removing just one of the 2 WHERE clauses. I'm guessing you just don't have any data setup for that criteria. Open the DB and see if you can find a row (By hand) that has a contactid  of 123 and a team equal to 'HYPE'.


WHAT DO YOU MEAN BY THE ABOVE
0
 

Author Comment

by:finnstone
ID: 8138973
Try removing just one of the 2 WHERE clauses. I'm guessing you just don't have any data setup for that criteria. Open the DB and see if you can find a row (By hand) that has a contactid  of 123 and a team equal to 'HYPE'.


WHAT DO YOU MEAN BY THE ABOVE
0
 

Expert Comment

by:DynAstY
ID: 8138980
Send me the DB at dynasty@hyperionx.com
0
 

Author Comment

by:finnstone
ID: 8138999
Neither Where Contact or AND team work by themselves, there is something going on that is bigger i guess.
0
 

Expert Comment

by:DynAstY
ID: 8139026
can you get the SQL to work if hard coded in dif page? start with the basics...
0
 

Author Comment

by:finnstone
ID: 8139033
Neither Where Contact or AND team work by themselves, there is something going on that is bigger i guess.
0
 

Author Comment

by:finnstone
ID: 8139037
yes hard coded worked
0
 

Author Comment

by:finnstone
ID: 8139062
yes hard coded worked
0
 

Expert Comment

by:DynAstY
ID: 8139071
whats the error you get?
0
 

Expert Comment

by:DynAstY
ID: 8139074
can you just open mIRC and goto efnet #COLDFUSION would be easier
0
 

Author Comment

by:finnstone
ID: 8139134
yes hard coded worked
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8139353
PLEASE, for the sake of my inbox... DO NOT REFRESH THE QUESTION. There is a link at the top of the page labeled " Reload This Question". Use it!
0
 
LVL 8

Expert Comment

by:TallerMike
ID: 8139369
Well, if this query isn't returning any results:

<CFQUERY NAME="get_products" DATASOURCE="test_db">
  SELECT distinct(product) FROM qry_screen_output
  WHERE ContactID = #Contactid#
    AND Team = '#Team#'
  ORDER BY PRODUCT
</CFQUERY>

Then try this:

<CFQUERY NAME="get_products" DATASOURCE="test_db">
  SELECT distinct(product) FROM qry_screen_output
  WHERE ContactID = #Contactid#
  ORDER BY PRODUCT
</CFQUERY>
<cfoutput>#get_products.RecordCount#</cfoutput>

And this:

<CFQUERY NAME="get_products" DATASOURCE="test_db">
  SELECT distinct(product) FROM qry_screen_output
  WHERE Team = '#Team#'
  ORDER BY PRODUCT
</CFQUERY>
<cfoutput>#get_products.RecordCount#</cfoutput>

BOTH of these queries will need to return results in order for you to get results from the query with the 2 WHERE clauses. A WHERE clause is any statment appearing within the WHERE attribute of the SQL statment. This includes the lines that begin with AND, they are still WHERE clauses.
0
 
LVL 6

Expert Comment

by:dash420
ID: 8141223
put the debug on and see what is the problem?. i mean run the debug query directly on database.

<CFQUERY NAME="get_products" DATASOURCE="test_db" debug>
 SELECT distinct(product) FROM qry_screen_output
 WHERE ContactID = #Contactid#
   AND Team = '#Team#'
 ORDER BY PRODUCT
</CFQUERY>

0
 
LVL 11

Accepted Solution

by:
hart earned 200 total points
ID: 8141444
rob check the query by putting the query in a cfm file and execute it.

then tell us what the error is, then i can comment on it.

u know who i am :-)
0
 

Author Comment

by:finnstone
ID: 8143031
dash/hart/anyone else...

i ran this query (i hardcoded the contactid and team..they do not work as hardcodings)

<CFQUERY NAME="get_products" DATASOURCE="test_db" debug>
SELECT distinct(product) FROM qry_screen_output
WHERE ContactID = "122"
  AND Team = "HPENT"
ORDER BY PRODUCT
</CFQUERY>


And got this result

Error Executing Database Query.  
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.  
 
The Error Occurred in C:\CFusionMX\wwwroot\Tech Team Tool Test\TMP3wip8bsu2m.cfm: line 6
 
4 :   AND Team = "HPENT"
5 : ORDER BY PRODUCT
6 : </CFQUERY>

 

--------------------------------------------------------------------------------
 
SQL    SELECT distinct(product) FROM qry_screen_output WHERE ContactID = "122" AND Team = "HPENT" ORDER BY PRODUCT  
DATASOURCE   test_db
VENDORERRORCODE   -3010
SQLSTATE   07001
0
 

Author Comment

by:finnstone
ID: 8143114
i ran this query too .. nothing come out on the web page when i ran it.


          <CFSET ContactID = "122">
          <CFSET Team = "HPENT">

<CFQUERY NAME="get_products" DATASOURCE="test_db" debug>
SELECT DISTINCT PRODUCT FROM qry_screen_output
WHERE ContactID = #ContactID#
  AND Team = '#Team#'
ORDER BY PRODUCT
</CFQUERY>
0
 

Author Comment

by:finnstone
ID: 8143205
OK the last 2 posts worked and so did this query, i did not have a CFOUTPUT ...so now i will go back to my full application and try and see the problem...i guess this means the problem lies in FLASH actionscript. stay tune, ill be asking more questions when i recognize what i need to ask about

working query below

          <CFSET ContactID = ListFirst(GetAuthUser())>
          <CFSET Team = ListRest(GetAuthUser())>

<CFQUERY NAME="get_products" DATASOURCE="test_db">
SELECT DISTINCT PRODUCT FROM qry_screen_output
WHERE ContactID = #ContactID#
  AND Team = '#Team#'
ORDER BY PRODUCT
</CFQUERY>

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<CFOUTPUT query= "get_products">
<b>#PRODUCT#</b>
</cfoutput>
</body>
</html>
0
 

Author Comment

by:finnstone
ID: 8143401
0
 

Author Comment

by:finnstone
ID: 8143404
your comment helped me at least figure out how to find out that the problem was not coldfusion.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

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…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month13 days, 4 hours left to enroll

777 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