Solved

simple query

Posted on 2004-09-09
16
178 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
ID: 12022563
<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
ID: 12022594
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
ID: 12023326
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 21

Expert Comment

by:pinaldave
ID: 12023441
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
ID: 12023503
please give me sample code
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 12023520
<cfif yourcontion>
<cfquery>
select query
</cfquery>
<cfelse>
<cfquery>
select another query
</cfquery>
</cfif>


0
 
LVL 18

Expert Comment

by:Plucka
ID: 12023565
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
ID: 12023568
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
 
LVL 17

Accepted Solution

by:
anandkp earned 100 total points
ID: 12024802
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
ID: 12029358
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
ID: 12029448
supose uer entered varialbe is entereddata

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

Expert Comment

by:pinaldave
ID: 12029457
0
 

Author Comment

by:Basic
ID: 12029568

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
ID: 12029627
#PreserveSingleQuotes(form.LastName)#
0
 

Author Comment

by:Basic
ID: 12029705
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
ID: 12032370
thank u all
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
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 …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

840 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