Solved

Results page...

Posted on 2006-12-01
10
353 Views
Last Modified: 2013-12-24
I am trying to make a results page work... Anyone see anything wrong with it? because i keep getting the error "missing operator"

Please help.  It is definitely in this code.

the code will seem doubled because I have 2 input types for each specific query.  1 text and 1 drop downlist I created with cfselect.

<cfquery name="results" datasource="real">
SELECT *

FROM users

WHERE
      <cfif session.cfUsername NEQ "">
      users.username= #Session.cfUsername#</cfif>
      
      <cfif session.cfPassword NEQ "">
      AND users.password = #Session.cfPassword#</cfif>
      
      <cfif session.cfLevel NEQ "">
      AND users.level = #Val(Session.cfLevel)#</cfif>
      
      <cfif session.cfFname NEQ "">
      AND users.fname EQ #trim(Session.cfFname)#</cfif>
      
      <cfif session.cfLname NEQ "">
      AND users.lname = #Session.cfLname#</cfif>
      
      <cfif session.cfEmail NEQ "">
      AND users.email = #Session.cfEmail#</cfif>
      
      <cfif session.cfUserid NEQ "">
      AND users.userid = #Session.cfUserid#</cfif>
      
      <cfif session.textusername NEQ "">
      AND users.username LIKE '%#Session.textusername#'</cfif>
      
      <cfif session.textpassword NEQ "">
      AND users.password LIKE '%#Session.textpassword#%'</cfif>
      
      <cfif session.textlevel NEQ "">
      AND users.level = #Session.textlevel#</cfif>
      
      <cfif session.textfname NEQ "">
      AND users.fname LIKE '%#Session.textfname#'</cfif>
      
      <cfif session.textlname NEQ "">
      AND users.lname LIKE '%#Session.textlname#'</cfif>
      
      <cfif session.textemail NEQ "">
      AND users.email LIKE '%#Session.textemail#'</cfif>
      
      <cfif session.textuserid NEQ "">
      AND users.userid = #Session.textuserid#</cfif>
      </cfquery>
0
Comment
Question by:sjha81
[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
  • 7
  • 3
10 Comments
 
LVL 9

Expert Comment

by:73Spyder
ID: 18053111
You need a 1=1 as a "dummy" clause.  IF your first if fails then your SQL will not be corrent.

SELECT *

FROM users

WHERE   1=1
     <cfif session.cfUsername NEQ "">
     AND users.username= #Session.cfUsername#</cfif>
     
     <cfif session.cfPassword NEQ "">
     AND users.password = #Session.cfPassword#</cfif>
     
     <cfif session.cfLevel NEQ "">
     AND users.level = #Val(Session.cfLevel)#</cfif>
     
     <cfif session.cfFname NEQ "">
     AND users.fname EQ #trim(Session.cfFname)#</cfif>
     
     <cfif session.cfLname NEQ "">
     AND users.lname = #Session.cfLname#</cfif>
     
     <cfif session.cfEmail NEQ "">
     AND users.email = #Session.cfEmail#</cfif>
     
     <cfif session.cfUserid NEQ "">
     AND users.userid = #Session.cfUserid#</cfif>
     
     <cfif session.textusername NEQ "">
     AND users.username LIKE '%#Session.textusername#'</cfif>
     
     <cfif session.textpassword NEQ "">
     AND users.password LIKE '%#Session.textpassword#%'</cfif>
     
     <cfif session.textlevel NEQ "">
     AND users.level = #Session.textlevel#</cfif>
     
     <cfif session.textfname NEQ "">
     AND users.fname LIKE '%#Session.textfname#'</cfif>
     
     <cfif session.textlname NEQ "">
     AND users.lname LIKE '%#Session.textlname#'</cfif>
     
     <cfif session.textemail NEQ "">
     AND users.email LIKE '%#Session.textemail#'</cfif>
     
     <cfif session.textuserid NEQ "">
     AND users.userid = #Session.textuserid#</cfif>
     </cfquery>
0
 
LVL 9

Expert Comment

by:73Spyder
ID: 18053116
Also this line:


   AND users.fname EQ #trim(Session.cfFname)#</cfif>


should be this


   AND users.fname = #trim(Session.cfFname)#</cfif>
0
 

Author Comment

by:sjha81
ID: 18053160
thanks for the response but i figured it out...
however now i am having a problem on the validation side...

I have a feeling it is something extremely simple ... anyways

I have been testing this.. it works when its like:
=============================
<cfquery name="u1" datasource="real">
UPDATE users SET
      username = '#Session.editusername#',
      password = '#Session.editpassword#',
      fname = '#Session.editfname#',
      lname = '#Session.editlname#',
        email = '#Session.editemail#'
WHERE id = #FORM.accountid#
</cfquery>
=============================

As soon as I add a number field... it stops working. Please help!
=this doesnt work=
<cfquery name="u1" datasource="real">
UPDATE users SET
      username = '#Session.editusername#',
      password = '#Session.editpassword#',
      fname = '#Session.editfname#',
      lname = '#Session.editlname#',
        email = '#Session.editemail#',
          level = #Session.editlevel#             <-------------------is there something im missing?

 I took off the quotes because it is a number but doesnt seem to work either.  I also tried

level = #var(Session.editlevel)#

this doesnt work either

Im dying.






0
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

 
LVL 9

Expert Comment

by:73Spyder
ID: 18053192
I would use the cfqueryparam wherever I could



UPDATE users SET
     username = <cfqueryparam value="#Session.editusername#" cfsqltype="cf_sql_varchar">,
     password = <cfqueryparam value="#Session.editpassword#" cfsqltype="cf_sql_varchar">,
     fname = <cfqueryparam value="#Session.editfname#" cfsqltype="cf_sql_varchar">,
     lname = <cfqueryparam value="#Session.editlname#" cfsqltype="cf_sql_varchar">,
     email = <cfqueryparam value="#Session.editemail#" cfsqltype="cf_sql_varchar">,
     level = <cfqueryparam value="#Session.editlevel#" cfsqltype="cf_sql_integer">  

See:  http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm  for a complete list of types
0
 

Author Comment

by:sjha81
ID: 18053271
Thanks for the quick response, unfortunately it doesnt work for me.

I tried changing the field to decimal and used:

level = <cfqueryparam value="#Session.editlevel#" cfsqltype="cf_sql_decimal">

but i get this error message returned to me:

 Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented
 
The error occurred in C:\CFusionMX\wwwroot\final\secure\admin\accounteditprocess.cfm: line 44

42 :      email = <cfqueryparam value="#Session.editemail#" >,
43 :      level = <cfqueryparam value="#Session.editlevel#" cfsqltype="cf_sql_decimal">
44 : WHERE id = #FORM.accountid#
45 : </cfquery>


0
 
LVL 9

Expert Comment

by:73Spyder
ID: 18053292
2 questions

42 :      email = <cfqueryparam value="#Session.editemail#" >,
43 :      level = <cfqueryparam value="#Session.editlevel#" cfsqltype="cf_sql_decimal">
44 : WHERE id = #FORM.accountid#
45 : </cfquery>

On line 42 why did you remove the last part of the tag  email = <cfqueryparam value="#Session.editemail#" cfsqltype="cf_sql_varchar">

and try putting on on line 44 as well

WHERE id =  <cfqueryparam value="#form.accountid#" cfsqltype="cf_sql_varchar">
0
 
LVL 9

Expert Comment

by:73Spyder
ID: 18053294
I guess I had 2 comments, not questions.
0
 

Author Comment

by:sjha81
ID: 18053334
Error Executing Database Query.
Invalid data for CFSQLTYPE CF_SQL_DECIMAL.
 
The error occurred in C:\CFusionMX\wwwroot\final\secure\admin\accounteditprocess.cfm: line 44

42 :      email = <cfqueryparam value="#Session.editemail#" cfsqltype="cf_sql_varchar">,
43 :      level = <cfqueryparam value="#Session.editlevel#" cfsqltype="cf_sql_decimal">
44 : WHERE id =  <cfqueryparam value="#form.accountid#" cfsqltype="cf_sql_varchar">
45 : </cfquery>
46 : <cflocation url="accountedit.cfm">

Well at least the error changed. Based on this error, this might be conflicting:

<cfparam name="Session.editlevel" default="">
<cfif isDefined("FORM.level")>
      <cfset Session.editlevel = FORM.level>
      </cfif>

maybe its the default?
0
 
LVL 9

Accepted Solution

by:
73Spyder earned 500 total points
ID: 18053415
WHat the error means is that the value for session.edit level is not a valid cf_sql_decimal.

Try using another one from the list.
0
 
LVL 9

Expert Comment

by:73Spyder
ID: 18065401
I am glad that you resolved the issue.  Not sure if it warranted a C though
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
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…

630 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