Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

A Tough One: why isnt this code working ? 2

Status
Not open for further replies.

leadman

Programmer
Joined
Jun 11, 2001
Messages
177
Location
US
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?
 
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
 
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 :

Code:
Change this:
<cfif checkbdup.recordcount gt 0>

to:
<cfif checkbdup.bill_id is NOT &quot;&quot;>
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top