Solved

simple query

Posted on 2004-09-09
16
170 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

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

Suggested Solutions

Title # Comments Views Activity
Why addon domain redirects to the original domain? 8 67
Connect to MS-SQL server from Linux/PHP 8 85
http to https 3 62
How can i point a subdomain to directory? 5 20
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
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…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

810 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