Solved

Results page...

Posted on 2006-12-01
10
349 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
Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

830 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