Solved

simple query

Posted on 2004-09-09
16
185 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
[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
  • 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
Save the day with this special offer from ATEN!

Save 30% on the CV211 using promo code EXPERTS30 now through April 30th. The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

730 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