freelanceprogrammers.org Forum Index » Cold Fusion
replacing strings for sql query unsuccessfully..
Joined: 05 Apr 2005
Posts: 37
replacing strings for sql query unsuccessfully..
I am taking in a multiple-valued string and need to match each
variable against a column of records in a database (MS Access).
The string may contain values like ASV-10 or ASV 10 or all other
sorts of variations. What I am doing is stripping out the special
characters, replacing them with text that should take each phrase on
either side of the special character and treat them as search
strings in the WHERE clause of the sql statement with AND between
them, and that way building the query WHERE string ad hoc. If the
last character in a list element is a special character, I am
stripping it off without adding any text.
I`ve been trying variations for hours and need a fresh set of eyes..
Any help is greatly appreciated. B/R -- Jen
Here is a sample of what I am doing -- I`ve used the - special
character for this example:
my sample querystring is form.searchstring=NI-,NATC
<cfquery name="rpt" datasource="#ds#">
SELECT *
from table1
WHERE
<cfloop from="1" to="#listlen(temp)#" index="countme">
<cfset newvalue=#trim(ListGetAt(form.searchstring,countme))#>
<cfif countme is 1>
<cfset tnewvalue = #Replace(newvalue,"_","#repFct#", "ALL")#>
<cfif #Find(tnewvalue,"%%",1)# is not 0>
<cfif #Find(newvalue,"_",1)# is not 1>
<cfset tempval=#reverse(newvalue)#>
<cfset tempval=#Replace(tempval,"_","", "ONE")#>
<cfset tempval=#reverse(newvalue)#>
<cfset newvalue=#tempval#>
<cfelse>
<cfset newvalue = #Replace(newvalue,"_","", "ONE")#>
</cfif>
<cfelse><cfset newvalue = #tnewvalue#>
</cfif>
<cfset tnewvalue = #Replace(newvalue,"-","#repFct#", "ALL")#>
<cfif #Find(tnewvalue,"%%",1)# is not 0>
<cfif #Find(newvalue,"-",1)# is not 1>
<cfset tempval=#reverse(newvalue)#>
<cfset tempval=#Replace(tempval,"-","", "ONE")#>
<cfset tempval=#reverse(newvalue)#>
<cfset newvalue=#tempval#>
<cfelse>
<cfset newvalue = #Replace(newvalue,"-","", "ONE")#>
</cfif>
<cfelse><cfset newvalue = #tnewvalue#>
</cfif>
#findit# LIKE `%#Replace(newvalue,"``","`", "ALL")#%`
</cfloop>
Order By Rcpt_Dt DESC
</cfquery>
<cfif rpt.recordcount is 0><!---cflocation url="#form.refer#?
status=x"---></cfif>
</cfif>
The error I am getting:
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in query
expression `NI%`` AND Remarks LIKE ``% Remarks LIKE `%NI%` AND
Remarks LIKE `%%` Remarks LIKE `%NATC%``.
SQL = "SELECT * from table1 WHERE NI%`` AND Remarks LIKE ``%
tnewvalue Remarks LIKE `%NI%` AND Remarks LIKE `%%` Remarks LIKE `%
NATC%` Order By Rcpt_Dt DESC"
Data Source = "START"
The error occurred while processing an element with a general
identifier of (CFQUERY), occupying document position (26:1) to
(26:38).
Date/Time: 05/12/05 12:15:11
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;
SV1; .NET CLR 1.1.4322)
Remote Address: 127.0.0.1
HTTP Referer: http://127.0.0.1/START/query3.cfm
Joined: 02 Apr 2005
Posts: 20
replacing strings for sql query unsuccessfully..
Check the output SQL statement towards the end. It looks like it is missing
a few "AND"s or "OR"s, and some quotes may be a little off to.
At first glance the CF looks like it stands a pretty good chance of getting
quotes in the right place but seems to not be. You might try throwing all
the evaluation stuff together before the query and then just plunking in a
list that requires less stuff in the SQL statement to be evaluated. If
nothing else, that might make it easier to read and bugshoot.
Mark Gregory
----- Original Message -----
From: "Jennifer" <chrystalheart@...>
To: <cold_fusion@yahoogroups.com>
Sent: Thursday, May 12, 2005 7:06 PM
Subject: [cold_fusion] replacing strings for sql query unsuccessfully..
> I am taking in a multiple-valued string and need to match each
> variable against a column of records in a database (MS Access).
>
> The string may contain values like ASV-10 or ASV 10 or all other
> sorts of variations. What I am doing is stripping out the special
> characters, replacing them with text that should take each phrase on
> either side of the special character and treat them as search
> strings in the WHERE clause of the sql statement with AND between
> them, and that way building the query WHERE string ad hoc. If the
> last character in a list element is a special character, I am
> stripping it off without adding any text.
>
> I`ve been trying variations for hours and need a fresh set of eyes..
> Any help is greatly appreciated. B/R -- Jen
>
> Here is a sample of what I am doing -- I`ve used the - special
> character for this example:
>
> my sample querystring is form.searchstring=NI-,NATC
>
> <cfquery name="rpt" datasource="#ds#">
> SELECT *
> from table1
> WHERE
> <cfloop from="1" to="#listlen(temp)#" index="countme">
> <cfset newvalue=#trim(ListGetAt(form.searchstring,countme))#>
> <cfif countme is 1>
>
> <cfset tnewvalue = #Replace(newvalue,"_","#repFct#", "ALL")#>
> <cfif #Find(tnewvalue,"%%",1)# is not 0>
> <cfif #Find(newvalue,"_",1)# is not 1>
> <cfset tempval=#reverse(newvalue)#>
> <cfset tempval=#Replace(tempval,"_","", "ONE")#>
> <cfset tempval=#reverse(newvalue)#>
> <cfset newvalue=#tempval#>
> <cfelse>
> <cfset newvalue = #Replace(newvalue,"_","", "ONE")#>
> </cfif>
> <cfelse><cfset newvalue = #tnewvalue#>
> </cfif>
>
> <cfset tnewvalue = #Replace(newvalue,"-","#repFct#", "ALL")#>
> <cfif #Find(tnewvalue,"%%",1)# is not 0>
> <cfif #Find(newvalue,"-",1)# is not 1>
> <cfset tempval=#reverse(newvalue)#>
> <cfset tempval=#Replace(tempval,"-","", "ONE")#>
> <cfset tempval=#reverse(newvalue)#>
> <cfset newvalue=#tempval#>
> <cfelse>
> <cfset newvalue = #Replace(newvalue,"-","", "ONE")#>
> </cfif>
> <cfelse><cfset newvalue = #tnewvalue#>
> </cfif>
>
> #findit# LIKE `%#Replace(newvalue,"``","`", "ALL")#%`
>
> </cfloop>
> Order By Rcpt_Dt DESC
> </cfquery>
> <cfif rpt.recordcount is 0><!---cflocation url="#form.refer#?
> status=x"---></cfif>
> </cfif>
>
>
> The error I am getting:
>
> Error Diagnostic Information
> ODBC Error Code = 37000 (Syntax error or access violation)
>
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in query
> expression `NI%`` AND Remarks LIKE ``% Remarks LIKE `%NI%` AND
> Remarks LIKE `%%` Remarks LIKE `%NATC%``.
>
>
> SQL = "SELECT * from table1 WHERE NI%`` AND Remarks LIKE ``%
> tnewvalue Remarks LIKE `%NI%` AND Remarks LIKE `%%` Remarks LIKE `%
> NATC%` Order By Rcpt_Dt DESC"
>
> Data Source = "START"
>
>
> The error occurred while processing an element with a general
> identifier of (CFQUERY), occupying document position (26:1) to
> (26:38).
>
>
> Date/Time: 05/12/05 12:15:11
> Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;
> SV1; .NET CLR 1.1.4322)
> Remote Address: 127.0.0.1
> HTTP Referer: http://127.0.0.1/START/query3.cfm
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
Joined: 05 Apr 2005
Posts: 37
replacing strings for sql query unsuccessfully..
The thing I`m having the biggest problem with is that when I do
replaces, it is putting in single quotes when it adds it to the sql.
So, I am ending up with double single quotes around some variables.
If I try to replace them, it doesn`t work. I`ve tried changing them
to b`s to strip them out, but i still end up with the double single
quotes. Have you seen that before? Any ideas?
Jen
Joined: 10 Sep 2002
Posts: 9
replacing strings for sql query unsuccessfully..
Yes I have seen that all the time use the fuction:
preservesinglequotes(variableName)
this should do the trick..
-----Original Message-----
From: cold_fusion@yahoogroups.com [mailto:cold_fusion@yahoogroups.com] On
Behalf Of Jennifer
Sent: Friday, May 13, 2005 2:32 AM
To: cold_fusion@yahoogroups.com
Subject: [cold_fusion] Re: replacing strings for sql query unsuccessfully..
The thing I`m having the biggest problem with is that when I do
replaces, it is putting in single quotes when it adds it to the sql.
So, I am ending up with double single quotes around some variables.
If I try to replace them, it doesn`t work. I`ve tried changing them
to b`s to strip them out, but i still end up with the double single
quotes. Have you seen that before? Any ideas?
Jen
Yahoo! Groups Links
Joined: 05 Apr 2005
Posts: 37
replacing strings for sql query unsuccessfully..
THANK YOU SO MUCH!
Jen
--- In cold_fusion@yahoogroups.com, "Brannon Hightower"
<hightower@a...> wrote:
> Yes I have seen that all the time use the fuction:
> preservesinglequotes(variableName)
> this should do the trick..
>
Joined: 14 May 2005
Posts: 15
replacing strings for sql query unsuccessfully..
We are now offering CF 7 hosting, Reseller hosting as well.
Thank you and have a great day!!
Online Corporation of America
Sales Support & Customer Service
=========================================
Sales:1-570-686-2300
Fax:1-570-686-9090
ICQ#:149075399
http://www.OnlineCorp.com
COLDFUSIONHOSTING.COM
COLDFUSION HOSTING & DEDICATED SERVERS
http://www.ColdFusionHosting.com
QUICKCART.COM
ADVANCED E-COMMERCE SHOPPING SYSTEM
http://www.QuickCart.com
BULK DOMAIN HOSTING
Wholesale Bulk Hosting for Resellers
http://www.BulkDomainHosting.com
-----Original Message-----
From: cold_fusion@yahoogroups.com [mailto:cold_fusion@yahoogroups.com] On
Behalf Of Jennifer
Sent: Friday, May 13, 2005 5:36 PM
To: cold_fusion@yahoogroups.com
Subject: [cold_fusion] Re: replacing strings for sql query unsuccessfully..
THANK YOU SO MUCH!
Jen
--- In cold_fusion@yahoogroups.com, "Brannon Hightower"
<hightower@a...> wrote:
> Yes I have seen that all the time use the fuction:
> preservesinglequotes(variableName)
> this should do the trick..
>
Yahoo! Groups Links
All times are GMT
Page 1 of 1
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Freelace Website Designer - Customer web design and software building.
China Wholesale - Electronics Products
Character Studio - Tutorials and Help
China Wholesale - Electronics Products
Character Studio - Tutorials and Help