CODEWITHSUNDEEP
sql-servercoldfusioncoldfusion-11
nam
nam

Reputation: 23868

ColdFusion - Variable variableName is undefined

In my cfoutput tag, I've set a variable that stores the name of a customer returned from a query. Then I use that variable in the WHERE clause in cfquery tag to retrieve the customer info. But I'm getting the error Variable CUSTOMER is undefined under certain search criteria when the form is submitted. I think the error occurs when CUSTOMER variable is null. I've tried various options in the following Where clause such as where Customers.CustomerName = #IIF(isDefined('Form.CUSTOMER'),de(Form.CUSTOMER),de(''))# but I still get the same error. I'm using SQL Server 2012 on the backend. Note that I cannot use cfparam inside cfoutput: 

<cfoutput query="qry_customers">
<cfset  CUSTOMER = #CUSTOMER#>
</cfoutput>
<cfquery name="get_customers" datasource="#request.dsn_name#">
         SELECT * from Customers
         where Customers.CustomerName = '#CUSTOMER#'
</cfquery>

UPDATE The error occurs in the WHERE clause in the cfquery tag below. And it occurs only when select statement in the cfquery tag does not return any records. The CF error shows the line # of the error to be the line where the WHERE clause is.

Upvotes: 0

Views: 3472

Answers (2)

Joe DeRose
Joe DeRose

Reputation: 3558

I'd recommend trying a few things:

  1. I don't think the line <cfset CUSTOMER = #CUSTOMER#> is necessary. If I read it correctly, you're just setting a variable name as the exact same name. Also, it appears that this is a text field (based on your query that matches it to a column named "CustomerName" rather than something like "CustomerNumber"). So, at a minimum, you'd need to include the quotation marks: <cfset CUSTOMER = "#CUSTOMER#">.
  2. I think you'll do better scoping your call to the variable: qry_customers.CUSTOMER instead of just CUSTOMER.
  3. I suspect you're problem will resolve itself if you pass your variable in a <cfqueryparam> tag (e.g., <cfqueryparam value="#qry_customers.CUSTOMER#", CFSQLType="CF_SQL_VARCHAR">)
  4. If null values in the source continue to pose a problem, you might try adding a space to the string (to force a conversion from null to a string), and then trimming out that space (because you don't really need it): value="#trim(qry_customers.CUSTOMER & ' ')#".

Upvotes: 2

Tim Jasko
Tim Jasko

Reputation: 1542

You'll get that error anytime qry_customers has no rows. Just reference qry_customer.customer directly. (If there were no rows, this will be equivalent to an empty string. You may end up wanting to check qry_customers.RecordCount instead.)

Upvotes: 2

Related Questions