Solved

Results page...

Posted on 2006-12-01
10
352 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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
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

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

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

Suggested Solutions

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…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
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 antispam), the admini…

710 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