Solved

simple query

Posted on 2004-09-09
16
149 Views
Last Modified: 2013-12-24
Hi ia m using query like this

<CFSET Sql="SELECT *l FROM  Employees where DepartmentID=#form.DeptID# and LastName like  '#form.LastName#%'  " >



<CFQUERY DATASOURCE="emp" NAME="employee">#Sql# </CFQUERY>

but its not working and shoing erro in CFSET
0
Comment
Question by:Basic
  • 7
  • 6
  • 2
  • +1
16 Comments
 

Author Comment

by:Basic
Comment Utility
<CFSET Sql="SELECT * FROM  Employees where DepartmentID=#form.DeptID# and LastName like  '#form.LastName#%'  " >



<CFQUERY DATASOURCE="emp" NAME="employee">#Sql# </CFQUERY>

but its not working and shoing erro in CFSET

0
 
LVL 21

Expert Comment

by:pinaldave
Comment Utility
Hi Basic,
1) what is wrong with this...
 
 <CFQUERY DATASOURCE="emp" NAME="employee">SELECT *l FROM  Employees where DepartmentID=#form.DeptID#
 and LastName like  '#form.LastName#%' </CFQUERY>
2) what do you mean by *1 that is not valid...
    if you want top 1 then you should say

SELECT TOP l FROM  Employees where DepartmentID=#form.DeptID#
 and LastName like  '#form.LastName#%'

Regards,
---Pinal
0
 

Author Comment

by:Basic
Comment Utility
please see my second post i wrote

<CFSET Sql="SELECT * FROM  Employees where DepartmentID=#form.DeptID# and LastName like  '#form.LastName#%'  " >





actaully i am using CFSET with in if condition thats why i cant use  <CFQUERY >

and <CFSET> does not taking single quote within the query for example   '#form.LastName#%'  

is returing as   ' '#form.LastName#%' '
0
 
LVL 21

Expert Comment

by:pinaldave
Comment Utility
use cfquery in if condition that is correct way to do it. do not use CFSET. if you are using CFSET then it will create this problem.
Regards,
---Pinal
0
 

Author Comment

by:Basic
Comment Utility
please give me sample code
0
 
LVL 21

Expert Comment

by:pinaldave
Comment Utility
<cfif yourcontion>
<cfquery>
select query
</cfquery>
<cfelse>
<cfquery>
select another query
</cfquery>
</cfif>


0
 
LVL 18

Expert Comment

by:Plucka
Comment Utility
Hi pinaldave,

<CFQUERY DATASOURCE="emp" NAME="employee">
    SELECT      *
    FROM        Employees
    where       DepartmentID=#form.DeptID#
    and         LastName like '#form.LastName#%'
</CFQUERY>

If you want paramaterised stuff you can do.

<CFQUERY DATASOURCE="emp" NAME="employee">
    SELECT      *
    FROM        Employees
    <cfif IsDefined("form.DeptId")>
        where       DepartmentID=#form.DeptID#
    </cfif>
    and         LastName like '#form.LastName#%'
</CFQUERY>

Regards
Plucka
0
 
LVL 18

Expert Comment

by:Plucka
Comment Utility
Hi Basic,

Opps

<CFQUERY DATASOURCE="emp" NAME="employee">
    SELECT      *
    FROM        Employees
    <cfif IsDefined("form.DeptId")>
        where       DepartmentID=#form.DeptID#
        and         LastName like '#form.LastName#%'
    </cfif>
</CFQUERY>

Regards
Plucka
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Accepted Solution

by:
anandkp earned 100 total points
Comment Utility
if ur using it like this :

<CFSET Sql="SELECT * FROM  Employees where DepartmentID=#form.DeptID# and LastName like  '#form.LastName#%'  ">
<CFQUERY DATASOURCE="emp" NAME="employee">#Sql# </CFQUERY>

u need to query it like this

<CFQUERY DATASOURCE="emp" NAME="employee">
 #PreserveSingleQuotes(Sql)#
</CFQUERY>

lemme know ...
0
 

Author Comment

by:Basic
Comment Utility
Can u again help in another small problem


if user enters single quoutes in texbox then still error is comming up
how to parse if the user enters single quoutes in texbox
0
 
LVL 21

Expert Comment

by:pinaldave
Comment Utility
supose uer entered varialbe is entereddata

then it should be like this...#PreserveSingleQuotes(entereddata)#
0
 
LVL 21

Expert Comment

by:pinaldave
Comment Utility
0
 

Author Comment

by:Basic
Comment Utility

the comment from anandkp  is working but

error is comming if the value of #form.LastName# contains single quoutes as user enters something like Ben's in textbox
0
 
LVL 21

Expert Comment

by:pinaldave
Comment Utility
#PreserveSingleQuotes(form.LastName)#
0
 

Author Comment

by:Basic
Comment Utility
i am using like this

#PreserveSingleQuotes(UCase(form.LastName))#

and i am geting error

Just in time compilation error

Invalid parser construct found on line 10 at position 236. ColdFusion was looking at the following text:

(
0
 

Author Comment

by:Basic
Comment Utility
thank u all
0

Featured Post

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Join & Write a Comment

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…
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now