Coldfusion 9 form processing bug - needs resolution

roger v
roger v used Ask the Experts™
on
Hi,
I've been struggling with this chunk of code for a while now. I got it working but then the requirements changed and I modified the code accordingly but it still doesn't save the form fields like it's supposed to. Code attached.

When the page loads up, the form displays all the right values by comparing the inner loop to the outer loop and populating the form variables. But when I save, it only saves a couple of fields but misses the rest of them. Obviously there is a flaw in my code logic in the save function. But I just can't seem to figure it out.
<cfif len(strError) eq 0>
		<cfquery name="qryCheckContacts" datasource="#application.datasource#">
			SELECT	process_id
			FROM	tb_process_role_contact
			WHERE	process_id = <cfqueryparam cfsqltype="cf_sql_numeric" value="#val(url.processid)#">
			AND		process_contact_actv_ind = 'Yes'
		</cfquery>
		<cfif qryCheckContacts.recordcount gt 0>
			<!---<cfinvoke component="xxx.xxxx.xxxxx" method="funcGetExistingContacts" returnvariable="qryAllRoles1">
				<cfinvokeargument name="processid" value="#val(url.processid)#">
				<!---<cfinvokeargument name="scopeval2" value="1">--->
			</cfinvoke>--->
			<cfinvoke component="cfc.xxx.xxxx" method="funcGetExistingRoles" returnvariable="qryAllRoles1">
				<cfinvokeargument name="processid" value="#val(url.processid)#">
				<cfinvokeargument name="scopeval2" value="1">
			</cfinvoke>
			<cfinvoke component="xxx.xxxx.xxxxx" method="funcGetExistingContacts" returnvariable="qryUsers">
				<cfinvokeargument name="processid" value="#val(url.processid)#">
			</cfinvoke>
			<cfloop query="qryAllRoles1">
				<cfloop query="qryUsers">
					<cfset form["rolename_"   & qryAllRoles1.currentRow] = qryAllRoles1.rolename>
					<cfif qryUsers.rolename eq qryAllRoles1.rolename>
						<cfset form["txt_admins_"  & qryAllRoles1.currentRow] = qryUsers.admins>
						<cfset form["txt_approvers_" & qryAllRoles1.currentRow] = qryUsers.approvers>
						<cfset form["txt_escalation_approvers_" & qryAllRoles1.currentRow] = qryUsers.esc_approvers>
						<cfset form["txt_secondlevel_approvers_" & qryAllRoles1.currentRow] = qryUsers.secondlevel_approvers>
						<cfset form["txt_secondlevel_escalation_approvers_" & qryAllRoles1.currentRow] = qryUsers.secondlevel_esc_approvers>
						<cfset form["hRoleNameContactSource_"   & qryAllRoles1.currentRow] = qryUsers.RoleNameContactSource>
					<cfelse>
						<cfset form["txt_admins_"  & qryAllRoles1.currentRow] = "">
						<cfset form["hRoleNameContactSource_"   & qryAllRoles1.currentRow] = "">
						<cfset form["txt_approvers_" & qryAllRoles1.currentRow] = "">
						<cfset form["txt_escalation_approvers_" & qryAllRoles1.currentRow] = "">
						<cfset form["txt_secondlevel_approvers_" & qryAllRoles1.currentRow] = "">
						<cfset form["txt_secondlevel_escalation_approvers_" & qryAllRoles1.currentRow] = "">
						<cfset form["hRoleNameContactSource_"   & qryAllRoles1.currentRow] = "">
					</cfif>
				</cfloop>
			</cfloop>
			<cfset form.recordCount = qryAllRoles1.recordCount>
			
		<cfelse>
			<cfinvoke component="xxx.xxxx.xxxxx" method="funcGetExistingRoles" returnvariable="qryAllRoles2">
				<cfinvokeargument name="processid" value="#val(url.processid)#">
				<cfinvokeargument name="scopeval2" value="1">
			</cfinvoke>
			<cfloop query="qryAllRoles2">
				<cfset form["rolename_"   & qryAllRoles2.currentRow] = qryAllRoles2.rolename>
				<cfset form["txt_admins_"  & qryAllRoles2.currentRow] = "">
				<cfset form["hRoleNameContactSource_"   & qryAllRoles2.currentRow] = "">
				<!---<cfset form["chk_approvers_"   & qryAllRoles2.currentRow] = "">--->
				
				<cfset form["txt_approvers_" & qryAllRoles2.currentRow] = "">
				<cfset form["txt_escalation_approvers_" & qryAllRoles2.currentRow] = "">
				<cfset form["txt_secondlevel_approvers_" & qryAllRoles2.currentRow] = "">
				<cfset form["txt_secondlevel_escalation_approvers_" & qryAllRoles2.currentRow] = "">
			</cfloop>
			<cfset form.recordCount = qryAllRoles2.recordCount>
		</cfif>
	
     <cfelse>
    	<cfset form.recordCount = form.frmtempItemCounter>
   	</cfif>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

If I understood you correctly, the problem is when saving ?

But the code you're showing is not your form action section... true?

Author

Commented:
gde, you're right. The form action code is essentially the same one that you provided with some modifications. It's been kinda buggy maybe because of some changes that I made. Code attached.
<cfquery name="qryDeleteUsers" datasource="#application.datasource#">
				delete from dbo.tb_process_Role_Contact
				where process_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(url.processid)#">
			</cfquery>
			<!---end delete--->
        	<!---<cfdump var="#form#"><cfabort>--->
        	<cfloop index="loopCount" from="1" to="#val(form.hrecordcount)#">
            		<cfset variables.rolename = form["rolename_#LoopCount#"]>
                	<cfset variables.admins = form["txt_admins_#LoopCount#"]>
					 <cfparam name="form.txt_approvers_#LoopCount#" default="">
					<cfparam name="form.txt_chk_approvers_#LoopCount#" default="">
					<cfset variables.chkApprovers = form["txt_chk_approvers_#LoopCount#"]>
					<!---<cfoutput>variables.chkApprovers is: #variables.chkApprovers#</cfoutput>--->
					<cfif variables.chkApprovers is "Y">
						<cfset variables.role_contact_source = "User's Manager">
					<cfelse>
						<cfset variables.role_contact_source = "">
					</cfif>
					<cfset variables.approvers = form["txt_approvers_#LoopCount#"]>
					<cfset variables.esc_approvers = form["txt_escalation_approvers_#LoopCount#"]>
                    <cfset variables.second_approvers = form["txt_secondlevel_approvers_#loopcount#"]>
                    <cfset variables.second_esc_approvers = form["txt_secondlevel_escalation_approvers_#loopcount#"]>
					<cfset lstExistingUsers = "">
					
                    <cfloop from="1" to="5" index="idx2">
                    	<cfswitch expression="#idx2#">
              				<cfcase value="1">       
                            	<cfset role = variables.admins>
                                <cfset rname = variables.rolename>
								
            				</cfcase>
            				<cfcase value="2">
                        		<cfset role =  variables.approvers>
                                <cfset rname = variables.rolename>
								
            				</cfcase>
            				<cfcase value="3">
                        		<cfset role = variables.esc_approvers>
                                <cfset rname = variables.rolename>
								
            				</cfcase>
            				<cfcase value="4">
                        		<cfset role = variables.second_approvers>
                                <cfset rname = variables.rolename>
								
            				</cfcase>
            				<cfcase value="5">
                        		<cfset role = variables.second_esc_approvers>
                                <cfset rname = variables.rolename>
								
            				</cfcase>
        				</cfswitch>
             		 <!---<br /> <cfoutput>#idx2#&nbsp;&nbsp;&nbsp;#rname#&nbsp;&nbsp;&nbsp;#role#<br /><br /></cfoutput>--->
					<!---check for multpile userid's--->
					<cfset debugNum = 0>
                    <cfif listlen(PreserveSingleQuotes(role),',') gte 2>		
                    	<cfloop index="r_role" list="#PreserveSingleQuotes(role)#" delimiters=",">
							<cftry>
									<cfquery name="qryAddAdmins" datasource="#application.datasource#">
										insert into dbo.tb_process_Role_Contact 
                							(process_id,Version,RoleName,
											process_contact_role_id,process_contact_userid,process_contact_Actv_ind,
											process_contact_DTm_Upt,process_contact_upt_userid,RoleNameContactSource)
										values	(<cfqueryparam cfsqltype="cf_sql_integer" value="#val(url.processid)#">,<cfqueryparam cfsqltype="cf_sql_numeric" value="1">,'#preservesinglequotes(rname)#',#idx2#,<cfqueryparam cfsqltype="cf_sql_varchar" value="#r_role#">,'Yes',getDate(),<cfqueryparam cfsqltype="cf_sql_varchar" value="#url.userid#">,<cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuotes(variables.role_contact_source)#">)
									</cfquery>
									<cfset lstExistingUsers = ListAppend(lstExistingUsers,r_role)>
									<cfset actionPerformed = "userid's added.">
                        <cfcatch type="any">
                        	<cfset isDuplicate = "true">
                        	<p style="color:##F00; font-weight:bold; font-size:12px">Error: <cfoutput>#cfcatch.Message#</cfoutput></p>
                        </cfcatch>
                        </cftry>
                    </cfloop>

					<cfelseif listlen(PreserveSingleQuotes(role),',') gte 1>
                   		<cftry>
         					<cfquery name="qryAddAdmins2" datasource="#application.datasource#">
             					insert into dbo.tb_process_Role_Contact 
                						(process_id,Version,RoleName,
										process_contact_role_id,process_contact_userid,process_contact_Actv_ind,
										process_contact_DTm_Upt,process_contact_upt_userid,RoleNameContactSource)
                				values	(<cfqueryparam cfsqltype="cf_sql_integer" value="#val(url.processid)#">,<cfqueryparam cfsqltype="cf_sql_integer" value="1">,'#preservesinglequotes(rname)#',#idx2#,<cfqueryparam cfsqltype="cf_sql_varchar" value="#role#">,'Yes',getDate(),<cfqueryparam cfsqltype="cf_sql_varchar" value="#url.userid#">,<cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuotes(variables.role_contact_source)#">)
            				</cfquery>
							<cfset lstExistingUsers = ListAppend(lstExistingUsers,role)>
							<cfset actionPerformed = "userid's added.">
                         <cfcatch type="any">
                         	<cfset isDuplicate = "true">
                        	<p style="color:##F00; font-weight:bold; font-size:12px">Error: <cfoutput> #cfcatch.Message#</cfoutput></p>
                        </cfcatch>
                        </cftry>
                        
                   </cfif>
             	</cfloop> 
             </cfloop>

Open in new window


It looks like you are looping records and the looping again for a list of roles.   Which is not being inserted?

If you are not getting all the records you should, then first check the value of

  form.hrecordcount

and make sure it matches the number of records you're processing.   You can add a flag (output a note) at the top of the loop to ensure you are looping the right number of times.

 
<cfloop index="loopCount" from="1" to="#val(form.hrecordcount)#">
   <h1>Processing Record #loopCount#...</h1>
   <cfset variables.rolename = form["rolename_#LoopCount#"]>
   <cfset variables.admins = form["txt_admins_#LoopCount#"]>

Open in new window


You don't have any CFIF statements that would keep you from getting the the insert statements, so the only other factor is the value of ROLE.   You want to display that to see what you're looping over.   I don't understand why you are using preserveSingleQuotes inthis, what is that doing for you?

 
</cfswitch>
             		 <!---<br /> <cfoutput>#idx2#&nbsp;&nbsp;&nbsp;#rname#&nbsp;&nbsp;&nbsp;#role#<br /><br /></cfoutput>--->
					<!---check for multpile userid's--->
					<cfset debugNum = 0>
                	<cfloop index="r_role" list="#PreserveSingleQuotes(role)#" delimiters=",">
                         <h2>Inserting Role: #r_role#</h2>
							<cftry>
									<cfquery name="qryAddAdmins" datasource="#application.datasource#">
										insert into dbo.tb_process_Role_Contact 
                							(process_id,Version,RoleName,
											process_contact_role_id,process_contact_userid,process_contact_Actv_ind,
											process_contact_DTm_Upt,process_contact_upt_userid,RoleNameContactSource)
										values	(<cfqueryparam cfsqltype="cf_sql_integer" value="#val(url.processid)#">,<cfqueryparam cfsqltype="cf_sql_numeric" value="1">,'#preservesinglequotes(rname)#',#idx2#,<cfqueryparam cfsqltype="cf_sql_varchar" value="#r_role#">,'Yes',getDate(),<cfqueryparam cfsqltype="cf_sql_varchar" value="#url.userid#">,<cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuotes(variables.role_contact_source)#">)
									</cfquery>
									<cfset lstExistingUsers = ListAppend(lstExistingUsers,r_role)>
									<cfset actionPerformed = "userid's added.">
                        <cfcatch type="any">
                        	<cfset isDuplicate = "true">
                        	<p style="color:##F00; font-weight:bold; font-size:12px">Error: <cfoutput>#cfcatch.Message#</cfoutput></p>
                        </cfcatch>
                        </cftry>
                    </cfloop>

Open in new window


I think I mentioned before that it seems your two role loops at the bottom do exactly the same thing (unless I am missing a small difference).   So I removed the CFIF and put in the one CFLOOP.   I added a flag to show what role is being processed so you can see if any are being skipped.

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Author

Commented:
"It looks like you are looping records and the looping again for a list of roles.   Which is not being inserted?"

My bad gde, I checked now and the records are all being inserted. The problem is when it completes the database operation, it then shows only the last row's values on the form. In the db, I see that that it has inserted separate rows for each userid. So there is something not right in the code where I use nested loops to retrieve the values from the db.

Author

Commented:
Oh I see what's happening GDE, when I'm populating the form variables inside the inner loop, it gives me the last row because by the time it's done with the loop, all the previous values are overwritten by the last row of values.

So I need a way to create a struct of the form variables and then loop through that struct and display them on the form. Any ideas on how to do this? :)

Author

Commented:
No wait, but isn't that why the currentrow is for? It's creating a structure of items with unique keys so why is it being overwritter?

<cfloop query="qryAllRoles1">
                        <cfloop query="qryUsers">
                              <cfset form["rolename_"   & qryAllRoles1.currentRow] = qryAllRoles1.rolename>
                              <cfif qryUsers.rolename eq qryAllRoles1.rolename>
                                    <cfset form["txt_admins_"  & qryAllRoles1.currentRow] = qryUsers.admins>
                                    <cfset form["txt_approvers_" & qryAllRoles1.currentRow] = qryUsers.approvers>
                                    <cfset form["txt_escalation_approvers_" & qryAllRoles1.currentRow] = qryUsers.esc_approvers>
                                    <cfset form["txt_secondlevel_approvers_" & qryAllRoles1.currentRow] = qryUsers.secondlevel_approvers>
                                    <cfset form["txt_secondlevel_escalation_approvers_" & qryAllRoles1.currentRow] = qryUsers.secondlevel_esc_approvers>
                                    <cfset form["hRoleNameContactSource_"   & qryAllRoles1.currentRow] = qryUsers.RoleNameContactSource>
                              <cfelse>
                                    <cfset form["txt_admins_"  & qryAllRoles1.currentRow] = "">
                                    <cfset form["hRoleNameContactSource_"   & qryAllRoles1.currentRow] = "">
                                    <cfset form["txt_approvers_" & qryAllRoles1.currentRow] = "">
                                    <cfset form["txt_escalation_approvers_" & qryAllRoles1.currentRow] = "">
                                    <cfset form["txt_secondlevel_approvers_" & qryAllRoles1.currentRow] = "">
                                    <cfset form["txt_secondlevel_escalation_approvers_" & qryAllRoles1.currentRow] = "">
                                    <cfset form["hRoleNameContactSource_"   & qryAllRoles1.currentRow] = "">
                              </cfif>
                              <cfoutput>
                                    #form["txt_admins_" & currentrow]#
                              </cfoutput>
                        </cfloop>


Let's look at these lines as an example...

<cfloop query="qryAllRoles1">
    <cfloop query="qryUsers">
        <cfset form["txt_admins_"  & qryAllRoles1.currentRow] = qryUsers.admins>

I will plug in the current row of 1 for qryAllRoles1.currentRow, so we are running just one loop of the outer and three loops of the inner, it looks like this...

The first loop of qryUsers
        <cfset form["txt_admins_"  & 1] = qryUsers.admins>

The 2nd loop of qryUsers would be this...
        <cfset form["txt_admins_"  & 1] = qryUsers.admins>

The 3rd loop of qryUsers would be this...
        <cfset form["txt_admins_"  & 1] = qryUsers.admins>

As you can see the outer loop of qryAllRoles1 does not change the currentRow value until the inner loop of users is done, so each time you are writing to txt_admin_1 and writing over it with every loop of qry Users.


Author

Commented:
Ahh I see, so it is indeed overwriting the txt_ values. So how would I make sure that it doesn't overwrite? Do I need another struct and do a struct append or something? I'm lost. :(

It depends on what you want for the result?

Do you want the value of the form field to be a comma delimited list of the admins?

If so, you can add ValueList() function to make a list of from the query columns..

<cfloop query="qryAllRoles1">
   <cfloop query="qryUsers">
        <cfset form["rolename_"  & qryAllRoles1.currentRow] = valueList(qryAllRoles1.rolename)>


You will have another problem though.  Your query qryAllRoles1 seems to never change for each loop of the outer loop.  That means, every row will have the same values in the form field.   Shouldn't qryUsers change for each row of qryAllRoles?   I don't really know the big picture, just pointing that out.

Author

Commented:
"Shouldn't qryUsers change for each row of qryAllRoles? "

Yes that is correct. Here is sample data:

qryAllRoles returns the following recordset containing four rows (1 column):

rolename
-----------
contractor
mason
drill operator
forklift operator

qryUsers has the following recordset containing these rows:

rolename            admins       approvers                         2nd_approvers                 esc_approvers    
-----------             ---------     --------------                        -------------------                -------------------
drill operator        x7ccud     gg87dg,x7ccud,tt78dd8     x7ccud,dt87dd                 dt87dd,x7ccud
forklift operator   gg87dg      tt78dd8,eeuy76                 gg87dg,x7ccud,tt78dd8    x7ccud,dt87dd

 So when both the loops run and the form vars are populated and displayed on the form, they should be the following:

rolename                 admins                 approvers                   2nd_approvers            esc_approvers
-----------                ----------                ----------------              --------------------          --------------------
drill operator          x7ccud               gg87dg,x7ccud,tt78dd8     x7ccud,dt87dd        dt87dd,x7ccud  
forklift operator   gg87dg      tt78dd8,eeuy76                 gg87dg,x7ccud,tt78dd8    x7ccud,dt87dd
contractor
mason

As you can see, since the qryUsers does not have rolename of contractor and mason with values in it, contractor and mason are populated by the outer query (qryAllRoles1) but with no values for admins, approvers and so on.

Does this give you a lil bit of an insight?
     
You need to put the query to fetch userRoles inside of the outer loop.   The user query will have a where clause for each role and will fetch only the records for that role.

CFLOOP Roles
  --- select users for that role (only)
  --- populate form fields for the role with a list of the users
Next Role

Author

Commented:
Yup, you were right! I'm running the users query inside the loop of the roles query, and now it's populating the appropriate fields. I'll do some more checking and let you know. thanks much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial