?
Solved

Retrieving from database.

Posted on 2003-02-27
14
Medium Priority
?
200 Views
Last Modified: 2013-12-24
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
Comment
Question by:elise28
[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
  • 3
  • 3
  • 2
  • +4
14 Comments
 
LVL 17

Expert Comment

by:anandkp
ID: 8032670
<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
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8033230
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
 

Author Comment

by:elise28
ID: 8038390
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 17

Expert Comment

by:anandkp
ID: 8039422
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
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8041389
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
 
LVL 4

Expert Comment

by:crosenblum
ID: 8044038
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
 

Expert Comment

by:SwiftCF
ID: 8045012
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
 
LVL 1

Expert Comment

by:DA_JED
ID: 8048420
    <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
 
LVL 4

Expert Comment

by:crosenblum
ID: 8050231
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
 

Author Comment

by:elise28
ID: 8054671
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
 
LVL 17

Accepted Solution

by:
anandkp earned 200 total points
ID: 8055000
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
 

Author Comment

by:elise28
ID: 8055024
thks anandkp.

0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10931182
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

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

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
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…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

801 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