How can I join two strings to get one?

[josel]

Howdy!

I am working on a script (action form). Five variables are passed ... I want to check on variables (fields) and build a string to be used within my query command.

Here is the few lines of code where I am stuck

<cfparam name="SearchString" type="string" default="">
<cfif isdefined("form.homephonenumber") AND #form.homephonenumber# IS NOT "">
	<cfset SearchString="">
<cfif isdefined("form.workphonenumber") AND #form.workphonenumber# IS NOT "">
	<cfset SearchString="">
<cfelseif isdefined("form.lastname") AND #form.lastname# IS NOT "">
	<cfset SearchString="">
<cfelseif isdefined("form.companyname") AND  #form.companyname# IS NOT "">
	<cfset SearchString="">
<cfelseif isdefined("form.city") AND #form.city# IS NOT "">
	<cfset SearchString="">
</cfif>

I am hoping to build variable SearchString to use it within the WHERE clause of the cfquery.

How can I do this?

Thank you all in advance.


Jose Lerebours


KNOWLEDGE: Something you can give away endlessly and gain more of it in the process! - Jose Lerebours

 

[josel]

OK - I found out how to combine strings. I am now having prolem with my SQL query not knowing what to do with set variable

Here is the query command:

		<cfif #SearchString# IS NOT "">
		  <cfquery name="contacts" dbtype="odbc" datasource="#application.DSN#">
		  	select * from contacts WHERE #varWhere#
		  </cfquery>
		</cfif>

String variable varWhere is set and it contains the conditions I am intending to use - Like so:

ContactLastname = "lerebours" AND ContactCity = "miami"

and yet, I get the following error:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'lerebours'.  
  
The error occurred in C:\Inetpub\[URL unfurl="true"]wwwroot\asher.com\contacts.cfm:[/URL] line 106
 
104 : 		<cfif #SearchString# IS NOT "">
105 : 		  <cfquery name="contacts" dbtype="odbc" datasource="#application.DSN#">
106 : 		  	select * from contacts WHERE #varWhere#
107 : 		  </cfquery>
108 : 		</cfif>
 

--------------------------------------------------------------------------------
 
SQL    select * from contacts WHERE ContactLastname = "<cfoutput>lerebours</cfoutput>" AND ContactCity = "<cfoutput>miami</cfoutput>"  
DATASOURCE   asher 
VENDORERRORCODE   207 
SQLSTATE   42S22 
 
Please try the following:

I have tried a number of things ranging from evaluate() to cfoutput ... and nothing works. What am I doing wrong?

Thank you all in advance!


Jose Lerebours

KNOWLEDGE: Something you can give away endlessly and gain more of it in the process! - Jose Lerebours

 

[JohnMaas]

<cfquery name="contacts" ....
select * from contacts Where #EVALUATE("varWhere")#
</cfquery>


Might do the trick

 

[r937]

use singlequotes for string values and doublequotes for column names

correct --

WHERE ContactLastname = 'lerebours'
AND ContactCity = 'miami'

incorrect --

WHERE ContactLastname = "lerebours"
AND ContactCity = "miami"

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[TruthInSatire]

thread232-985449

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)