Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Results page...

Posted on 2006-12-01
10
Medium Priority
?
361 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
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 1000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

824 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