Solved

simple query

Posted on 2004-09-09
16
161 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
 
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
Save on storage to protect fatherhood memories

You're the dad who has everything. This Father's Day, make sure your family memories are protected. My Passport Ultra has automatic backup and password protection to keep your cherished photos and videos safe. With up to 3TB, you have plenty of room to hold the adventures ahead.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

19 Experts available now in Live!

Get 1:1 Help Now