A Tough One: why isnt this code working ? 2

[leadman]

Okay, at the top of the template i have this:

<cfset dbfields=&quot;firstname,lastname,phone,fax,email,address1,address2,city,state,zip&quot;>

Then I have this query which:

<cfquery name=&quot;checkbdup&quot; datasource=&quot;mydata&quot; maxrows=&quot;1&quot;>
SELECT max(bID) from billing
WHERE 0=0
<cfloop list=&quot;#dbfields#&quot; index=&quot;i&quot;>
<cfif len(evaluate(i))>
and #i# = '#evaluate(i)#'
</cfif>
</cfloop>
</cfquery>

Now, if no results from the query come back I want to do one thing, otherwise something else - here is the code:

<cfif checkbdup.recordcount gt 0>
<cfset billingID = #checkbdup.bID#>
<cfelse>
<!--- if not in database, we insert the info, then get their ID's for future ref --->
<cfinsert datasource=&quot;#application.ds#&quot; formfields=&quot;#dbfields#&quot; tablename=&quot;billing&quot;>

As i test this template there is no info in the billing table and there is a column called bID (i have double checked this). So, the cfif test for checkbdup.recordcount gt o should be false and therefore the else code should kick in BUT when i run this i get the following error:

Error resolving parameter CHECKBDUP.BID

The column BID is not present in the query named CHECKBDUP. It is likely that you have misspelled the name of the column.

The error occurred while evaluating the expression:

billingID = #checkbdup.bID#

The error occurred while processing an element with a general identifier of (CFSET), occupying document position (36:6) to (36:40).

First of all, that column is in the query, second, cf shouldnt be trying to evaluate that expression since the recordcount should not be greater than zero (again, there is nothing in that table yet). Anyone see the problem here?

 

[tleish]

You have to create an alias for max(bID), and it can't be the same name (ie bID). You could do it this way:

=== START CODE EXAMPLE ===
<cfquery name=&quot;checkbdup&quot; datasource=&quot;mydata&quot; maxrows=&quot;1&quot;>
SELECT max(bID) AS bill_id from billing
WHERE 0=0
<cfloop list=&quot;#dbfields#&quot; index=&quot;i&quot;>
<cfif len(evaluate(i))>
and #i# = '#evaluate(i)#'
</cfif>
</cfloop>
</cfquery>
=== END CODE EXAMPLE ===

Change the reference here to the query alias

=== START CODE EXAMPLE ===
<cfif checkbdup.recordcount gt 0>
<cfset billingID = checkbdup.bill_id>
<cfelse>
[COLOR=666666]<!--- if not in database, we insert the info, then get their ID's for future ref --->[/color]
<cfinsert datasource=&quot;#application.ds#&quot; formfields=&quot;#dbfields#&quot; tablename=&quot;billing&quot;>
</cfif>
=== END CODE EXAMPLE === - tleish

 

[CFDude]

Just another note -- Since your query is selecting a MAX, the query will always return a record, your cfif statement on the recordcount will always return true, even if there are no records. What you would need to do is check to make sure the bill_id is not blank. Use the alias method that tleish pointed out :

Change this:
<cfif checkbdup.recordcount gt 0>

to:
<cfif checkbdup.bill_id is NOT &quot;&quot;>