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>


elise28Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.