0

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.

nam
  • 21,967
  • 37
  • 158
  • 332
  • 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 Jan 09 '16 at 01:43
  • Your iif statement is using a different variable than your set statement too btw – Matt Busche Jan 09 '16 at 01:43
  • 3
    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 Jan 09 '16 at 03:21

2 Answers2

2

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

Tim Jasko
  • 1,532
  • 1
  • 8
  • 12
2

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 & ' ')#".
Joe DeRose
  • 3,438
  • 3
  • 24
  • 34
  • #1 is partially incorrect. Adding " does nothing. Neither " or # are necessary even if you're expecting a string. – Matt Busche Jan 09 '16 at 03:02
  • 2
    Also, it does not *quite* do nothing. Assuming "Customer" is column in the query, the code actually copies that query value into the `variables` scope. Meaning there are now three "Customer" variables in play: `qry_customers.CUSTOMER`, `variables.customer` and `form.customer`. I suspect that was not the intent. @nam - As mentioned in the comments above, the lack of scoping makes the code very error prone, and may be related to the issue you are experiencing. If you could clarify your objective, I am sure someone can assist in resolving the issue and improving the clarify of the code. – Leigh Jan 09 '16 at 05:02
  • @Leigh I've added an update section to my original post. I think the main point is that in the `WHERE` clause how to set the variable CUSTOMER to an empty sting if no rows are returned from `qry_customers` – nam Jan 10 '16 at 20:37
  • 1
    @nam - You still did not post the full error message or answer questions #2 and #3 above. Hard to do more than guess without more complete details. What you describe should not happen if there actually were a `form.customer` variable defined, as your IIF code suggests. So either there is something you are not telling us or you are doing something different than what you posted... Take a step back and start by putting together a small, but *complete*, repro case that demonstrates the issue. Just a simple form with the "Customer" field and the code for both queries where the error occurs. – Leigh Jan 10 '16 at 22:42
  • @Leigh I think, per your suggestion, I need to put together a small, but complete, repro case that demonstrates the issue. Let me try that and post it as an update. – nam Jan 11 '16 at 03:13