Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Results page...

Posted on 2006-12-01
10
Medium Priority
?
358 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
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…

721 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