why wont this query work?

         <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
finnstoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JimV_ATLCommented:
Try this

SELECT distinct product
FROM qry_screen_output
WHERE ContactID = #Contactid#
AND Team = '#Team#'                    
Order by PRODUCT
0
DynAstYCommented:
Team is a string data type and does need single quotes around it.

Example:
Where Team = '#TEAM#'
0
finnstoneAuthor Commented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

DynAstYCommented:
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
DynAstYCommented:
Oh yea and the DISTICT is wrong. As JimV stated

SELECT distinct(PRODUCT) should be Select DISTINCT Product
0
TallerMikeCommented:
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
finnstoneAuthor Commented:
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
finnstoneAuthor Commented:
contactid is a number
0
finnstoneAuthor Commented:
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
finnstoneAuthor Commented:
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
DynAstYCommented:
Send me the DB at dynasty@hyperionx.com
0
finnstoneAuthor Commented:
Neither Where Contact or AND team work by themselves, there is something going on that is bigger i guess.
0
DynAstYCommented:
can you get the SQL to work if hard coded in dif page? start with the basics...
0
finnstoneAuthor Commented:
Neither Where Contact or AND team work by themselves, there is something going on that is bigger i guess.
0
finnstoneAuthor Commented:
yes hard coded worked
0
finnstoneAuthor Commented:
yes hard coded worked
0
DynAstYCommented:
whats the error you get?
0
DynAstYCommented:
can you just open mIRC and goto efnet #COLDFUSION would be easier
0
finnstoneAuthor Commented:
yes hard coded worked
0
TallerMikeCommented:
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
TallerMikeCommented:
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
dash420Commented:
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
hartCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
finnstoneAuthor Commented:
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
finnstoneAuthor Commented:
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
finnstoneAuthor Commented:
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
finnstoneAuthor Commented:
0
finnstoneAuthor Commented:
your comment helped me at least figure out how to find out that the problem was not coldfusion.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.