Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

why wont this query work?

Posted on 2003-03-14
28
Medium Priority
?
158 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
  • 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

578 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