Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Retrieving from database.

Hi, I have problem in trying to display a list of events.

I have 2 kinds of events, one is a loss event that display events more than $1000, and this one, displaying loss events less than $1000.

I have use back the same template of the existing more than $1000 event, hoping it can works the same. But i realised it can't work.

I have tired to insert tis:

<cfquery name="list" datasource="ORM">
select * from input
where '#form.actloss#' < 1000..
etc..
into one of the cfquery below but error occur.

The main thing is to retrieve the data of event
where '#form.actloss#' is got from the page where user key in the amount of losses (less than $1000).

Below is the original codes from the add event more than $1000.


<cfquery name="k" datasource="ORM">
select * from input where keyer = '#a#'
</cfquery>
<cfquery name="testor" datasource="ORM">
select * from input
</cfquery>
<cfquery name="approveds" datasource="ORM">
select * from input where status = 'Y'
</cfquery>
<cfquery name="list" datasource="ORM">
select * from input
where 1=1
<cfif not '#form.country#' is 0>and  country = '#form.country#'</cfif>
<cfif not '#form.sdept#' is 0>and deptsg = '#form.sdept#'</cfif>
<cfif not '#form.dept#' is 0>and dept = '#form.dept#'</cfif>
<cfif not #form.startyr# is 0>and datepart(yyyy, repdate) = #form.startyr#</cfif>
<cfif not #form.month# is 0> and datepart(m, repdate) =#form.month#</cfif>
<cfif session.userprivilege is 3>and status = 'Y'</cfif>
order by refid asc

</cfquery>


0
elise28
Asked:
elise28
  • 3
  • 3
  • 2
  • +4
1 Solution
 
anandkpCommented:
<cfquery name="list" datasource="ORM">
select * from input
where '#form.actloss#' < 1000..
etc..
into one of the cfquery below but error occur.

this shld have been as

select * from input where table_fieldname < 1000

u cannot have field names enclosed in singlequotes !!!

let me know

K'Rgds
Anand
0
 
HamdyHassanCommented:
Let me guess
the field name should be input.actloss

so it should be
<cfquery name="list" datasource="ORM">
select * from input
where actloss < 1000 >


Try to rewrite the question because I got confused about event meaning and what do you want to do?

0
 
elise28Author Commented:
I experienced the following error:

Error Diagnostic Information

An error occurred while evaluating the expression:

not '#form.country#' is 0

I wrote thise:

<cfquery name="list" datasource="ORM">
select * from input
where aloss < 1000
<cfif not '#form.country#' is 0>and  country = '#form.country#'</cfif>
<cfif not '#form.sdept#' is 0>and deptsg = '#form.sdept#'</cfif>
<cfif not '#form.dept#' is 0>and dept = '#form.dept#'</cfif>
<cfif not #form.startyr# is 0>and datepart(yyyy, repdate) = #form.startyr#</cfif>
<cfif not #form.month# is 0> and datepart(m, repdate) =#form.month#</cfif>
<cfif session.userprivilege is 3>and status = 'Y'</cfif>
order by refid asc

</cfquery>
0
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
anandkpCommented:
this statment is wrong

<cfif not '#form.country#' is 0>and  country = '#form.country#'</cfif>

write this as

<cfif not #form.country# is 0>and  country = '#form.country#'</cfif>

make changes in others respectively - by removing the singlequotes

let me know

K'Rgds
Anand
0
 
HamdyHassanCommented:
form.country I think is string field, not integer, double check your table defination

so it should be

<cfif #form.country# is not "" >and  country = '#form.country#'</cfif>



0
 
crosenblumCommented:
1. You should never use * in your queries, it's bad coding practice and bad performance. Always specificize exactly what fields with what data you need to bring back...

2. Instead of using dynamic fieldnames or tablenames, which are really ugly coding, hard to fix. Use a cfif and hardcode which table,field to use in which circumstances.

I know it takes more time, but it pays to stick to and stand by common sense standards...
0
 
SwiftCFCommented:
Try this instead elise:

<cfquery name="list" datasource="ORM">
select * from input
where aloss < 1000
<cfif len(form.country)> and country = '#form.country#'</cfif>
<cfif len(form.sdept)> and deptsg = '#form.sdept#'</cfif>
<cfif len(form.dept)> and dept = '#form.dept#'</cfif>
<cfif len(form.startyr)> and datepart(yyyy, repdate) = #form.startyr#</cfif>
<cfif len(form.month)> and datepart(m, repdate) = #form.month#</cfif>
<cfif session.userprivilege is 3>and status = 'Y'</cfif>
order by refid asc
</cfquery>

That will fix your query. As for your original problem, I hope that

select * from input
where actloss < 1000

was what you were looking for.

Also, you should use CFLock to lock your session variable accesses and writes.

Swift
0
 
DA_JEDCommented:
    <cfquery name="k" datasource="ORM">
          select * from input where keyer = '#a#'
     </cfquery>
     
     
     <cfquery name="testor" datasource="ORM">
          select * from input
     </cfquery>
     <cfquery name="approveds" datasource="ORM">
          select * from input where status = 'Y'
     </cfquery>

     <!---Ensure you use your brackets...--->
          <cfquery name="list" datasource="ORM">
               select * from input
               WHERE (1=1)
               <!--- Include the table field name that you are checking events for --->
                    and (Table_Field_Name < 1000) <!--- or and (Table_Field_Name < 1000)  whichever applies --->
          <cfif (Form.Country neq 0)      or (Len(Form.Counry)      neq 0)>and  (country = '#form.country#')</cfif>
          <cfif (Form.SDept neq 0)      or (Len(Form.SDept)      neq 0)>and      (deptsg = '#form.sdept#')</cfif>
          <cfif (Form.Dept neq 0)      or (Len(Form.Dept)           neq 0)>and      (dept = '#form.dept#')</cfif>
          <cfif (Form.StartYr neq 0)      or (Len(Form.StartYr)      neq 0)>and      (datepart(yyyy, repdate) = #form.startyr#)</cfif>
          <cfif (Form.Month neq 0)      or (Len(Form.Month)      neq 0)>and      (datepart(m, repdate) =#form.month#)</cfif>
          <cflock type="READONLY" scope="SESSION" timeout="15">
               <!--- I am assuming you misspelt 'SESSION'--->
               <cfif (Session.userprivilege eq 3)>and status = 'Y'</cfif>
          </cflock>
               order by refid asc
          </cfquery>
0
 
crosenblumCommented:
That's ugly coding.

Locking is mainly if the version of cf you are running doesn't have it built in. And if it session/application variables that are going to be used by multiple simultaneous users.

Select * is such a bad practice. I know it is faster, but just adds up to the things that prevent the application from performing well in the short term and long term.

as for other ways to handle form variables, i have another method.

Basically you have some local variables, that are application specific. I tend to use a strict naming convention...

so you have default variables

<cfparam name="local_firstname" default="">

Then in the form data handling...

<cfif isdefined("form.firstname")>
<cfset local_firstname = form.firstname>
</cfif>

then in your form element

<input type=text name=firstname value="#local_firstname#">

Doing it that way makes the code re-usable, that is if you want to allow editing of form data, such as addresses or whatever...

The form will either be in edit mode, and have values or will be null as the default values of null are displayed.
0
 
elise28Author Commented:
Thanks to all!
I have tired all valuables comments from what you all have suggested but I still face problems..

When i stick to the following, I experience no problem but it list out both the events (more & less than $1000).

<cfquery name="list" datasource="ORM">
select * from input  
where 1=1
<cfif not '#form.country#' is 0>and  country = '#form.country#'</cfif>
<cfif not '#form.sdept#' is 0>and deptsg = '#form.sdept#'</cfif>
<cfif not '#form.dept#' is 0>and dept = '#form.dept#'</cfif>
<cfif not #form.startyr# is 0>and datepart(yyyy, repdate) = #form.startyr#</cfif>
<cfif not #form.month# is 0> and datepart(m, repdate) =#form.month#</cfif>
<cfif session.userprivilege is 3>and status = 'Y'</cfif>
order by refid asc

</cfquery>

0
 
anandkpCommented:
in that case - just add the condition for the amount that u need to chk to be < 1000

so make ur query as :

<cfquery name="list" datasource="ORM">
select * from input  
where 1=1
And amt_fieldname < 1000
<cfif not '#form.country#' is 0>and  country = '#form.country#'</cfif>
<cfif not '#form.sdept#' is 0>and deptsg = '#form.sdept#'</cfif>
<cfif not '#form.dept#' is 0>and dept = '#form.dept#'</cfif>
<cfif not #form.startyr# is 0>and datepart(yyyy, repdate) = #form.startyr#</cfif>
<cfif not #form.month# is 0> and datepart(m, repdate) =#form.month#</cfif>
<cfif session.userprivilege is 3>and status = 'Y'</cfif>
order by refid asc

</cfquery>
0
 
elise28Author Commented:
thks anandkp.

0
 
mrichmonCommented:
No comment has been added lately, so it's time to clean up this question.
I will leave the following recommendation in the Cleanup topic area:

Accept anandkp

Please leave any comments here within the next four days.

mrichmon
EE Cleanup Volunteer
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now