ColdFusion - Variable variableName is undefined

0
votes

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.

2
sql-servercoldfusioncoldfusion-11
Why are you setting it to another variable anyway? Just use the value from the query. Like Henry said dump your query and look at the results. - Matt Busche
Your iif statement is using a different variable than your set statement too btw - Matt Busche
Since you have multiple variables all named "Customer" it is very difficult to know exactly where in the code the error occurs. Also, having multiple unscoped variables all named "Customer" is a recipe for confusion, at best - wrong or unexpected results at worst. Especially when a query loop is involved. 1) Please post the full error message with context line numbers so we can see exactly what code is throwing that error. 2) What is the purpose of qry_customers? ie Why not just use a JOIN? 3) What type of form field is "form.customer"? - Leigh

2 Answers

2
votes

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.)

2
votes

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 & ' ')#".