Error when looping through array 1

[Fleer]

I fill up an array (Session.myArray) with finds users make in my database, when they hit submit I generate my query based on the values in the array :

the values in the array are
[x][1] : FieldName
[x][2] : Symbol (=, >, LIKE, <=, etc)
[x][3] : string or value entered by the user
[x][4] : 'And' OR 'Or' for next find

I get an error saying that 'The element at position 1, of dimension 2, of an array object used as part of an expression, cannot be found'

This is my code :

<cfquery name=&quot;dbname&quot; datasource=&quot;Mydsn&quot;>
select * from dbname where 0=0
<cfloop index=&quot;x&quot; from=&quot;1&quot; to=&quot;#Number_Of_Finds#&quot;>
<cfif left(#Session.myArray[x][1]#,1) EQ &quot;n&quot; OR left(#Session.myArray[x][1]#,1) EQ &quot;d&quot;>
<cfif #Session.myArray[x][2]# EQ &quot;LIKE&quot;>
#Session.myArray[x][4]# #Session.myArray[x][1]# #Session.myArray[x][2]# #Session.myArray[x][3]#
<cfelse>
<cfif #Session.myArray[x][2]# EQ &quot;NOT LIKE&quot;>
#Session.myArray[x][4]# #Session.myArray[x][1]# <> #Session.myArray[x][3]#
<cfelse>
#Session.myArray[x][4]# #Session.myArray[x][1]# #Session.myArray[x][2]# #Session.myArray[x][3]#
</cfif>
</cfif>
<cfelse>
<cfif #Session.myArray[x][2]# EQ &quot;LIKE&quot; OR #Session.myArray[x][2]# EQ &quot;NOT LIKE&quot;>
#Session.myArray[x][4]# upper(trim(#Session.myArray[x][1]#)) #Session.myArray[x][2]# upper('%#Session.myArray[x][3]#%')
<cfelse>
#Session.myArray[x][4]# upper(trim(#Session.myArray[x][1]#)) #Session.myArray[x][2]# upper(#Session.myArray[x][3]#)
</cfif>
</cfif>
</cfloop>
</cfquery>

Does anybody have a answer, or maybe a less complex solution for this problem ? Any help is appreciated...

Fleer

 

[pcorreia]

My question would be, where does the variable #Number_of_Finds# come from? Is it possible that you're getting this error because the session has expired and the array really is empty?

I'm also concerned about the frequent calls to the SESSION-scope variable without any locking. Of course, you really don't want to put a lock around a <cfquery>, so perhaps you should be copying the SESSION.myArray array into the REQUEST scope before you do the query.

I do think the whole ball of conditional logic could be simplified, but before I write any code I would also need to know what the significance of the field name starting with &quot;n&quot; or &quot;d&quot; is...

 

[pcorreia]

Also, in going through your code, I can't understand why you did an upper() and a trim() on a field name. I would expect those would give you errors from the database since they both take string values as parameters, not field names.

 

[pcorreia]

OK, obviously it's Friday morning. I understand now why you did that, to make sure you were comparing the same case. Please disregard my last post. I'm a doofus.

 

[pcorreia]

I couldn't stay away. Assuming there's a significance to the first character of the field name being &quot;n&quot; or &quot;d&quot;, here's my suggested code update:

   <cflock type=&quot;READONLY&quot; scope=&quot;SESSION&quot; timeout=&quot;10&quot; throwontimeout=&quot;Yes&quot;>
      <cfset myArray = Duplicate(SESSION.myArray)>
   </cflock>
   <cfquery name=&quot;dbname&quot; datasource=&quot;Mydsn&quot;>
      SELECT * FROM tablename WHERE 0=0
      <cfloop index=&quot;x&quot; from=&quot;1&quot; to=&quot;#ArrayLen(myArray)#&quot;>
         #myArray[x][4]# 
         <cfif ListFind(&quot;n,d&quot;,Left(myArray[x][1],1))>
            #myArray[x][1]# 
            <cfif myArray[x][2] IS &quot;NOT LIKE&quot;>
               <> '#myArray[x][3]#'
            <cfelse>
               #myArray[x][2]# '#myArray[x][3]#'
            </cfif>
         <cfelse>
            UPPER(TRIM(#myArray[x][1]#)) 
            <cfif ListFind(&quot;LIKE,NOT LIKE&quot;,myArray[x][2])>
               #myArray[x][2]# UPPER('%#myArray[x][3]#%')
            <cfelse>
               #myArray[x][2]# UPPER('#myArray[x][3]#')
            </cfif>
         </cfif>
      </cfloop>
   </cfquery>

 

[Fleer]

In reply to your last post here, you're not a doofus You gave me some great assistance, thanks for the help.

The first character of my field names begins with the field type, so the n and d stands for Numeric and Date.

 

[pcorreia]

Ah, I see. In the light of Monday morning, everything becomes clear ;o)

Glad to be of help.