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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CFLOOP if data exists, then Update, if not....exclude 1

Status
Not open for further replies.

rojas1mg

Programmer
Jun 15, 2004
119
0
0
US
Here's what I have on my display page
Code:
<cfloop from="1" to="10" index="Register">
<tr>
<!---Enter name (Last, First)--->
	<td><input type="text" name="name#register#" size="45"></td>
<!---Rank--->
	<td><select name="rank#register#">
		<cfoutput query="rank">
		<option value="#Rank_ID#">#Rank#</option>
		</cfoutput></select></td>
<!---Enter SSAN--->
	<td><input type="Text" name="SSN#register#" size="15"></td>
	<!---Enter e-mail address--->
	<td><input type="Text" name="email#register#" value="@varich.ang.af.mil" size="30"></td>
<!---Select Unit--->
	<td><select name="Unit_ID#register#">
		<cfoutput query="dp_unit">
		<option value="#Unit_ID#">#Unit#</option>
		</cfoutput></select></td>
<!---Select Refresher Class Dated--->
	<td><cfoutput query="class">#Course_Date#</cfoutput></td>
</tr>
</cfloop>

On my action page I have:

Code:
<cfquery datasource="#dsource#">
INSERT INTO tbl_roster(name, Rank_ID, SSN, email, Unit_ID, Course_ID)
VALUES('#form.name#', #form.Rank#, #form.ssn#,'#form.email#', #form.unit_ID#, #form.course_id#)
</cfquery>

What I believe I have to do is write a loop for the action page to check and make sure data is being passed through on those input fields and if so, update the table. If not, exclude it. If this doesn't make sense, let me know. Thanks guys.

rojas1mg - - - I love Tek-Tips and all members who reply.
 
i've done something very similar with adding a point of contact
the cfif in the beginning will check to see if it has to be skipped. it checks the poc's name field. you can check any field or as many as you want.

Code:
<cfloop index = "epoc" from = "1" to = "10">
	<cfif IsDefined("FORM.POCName#epoc#") AND #trim(evaluate("FORM.POCName"&epoc))# NEQ "">
		<cfquery datasource = "#application.dsn#">
			INSERT INTO emergencyPOCTable (
				employeeID,
				POCName,
				streetAddress,
				relation,
				city,
				state,
				zip,
				homephone,
				workphone,
				cellPhone)
			VALUES (
				#form.empid#,
				'#trim(evaluate("FORM.POCName"&epoc))#',
				<cfif IsDefined("FORM.POCstreetAddress#epoc#") AND #trim(evaluate("FORM.POCstreetAddress"&epoc))# NEQ "">
					'#trim(replace(evaluate("FORM.POCstreetAddress"&epoc),"'", "''"))#'
				<cfelse>
					NULL
				</cfif>
				,
				<cfif IsDefined("FORM.relation#epoc#") AND #trim(evaluate("FORM.relation"&epoc))# NEQ "">
					'#trim(replace(evaluate("FORM.relation"&epoc),"'", "''"))#'
				<cfelse>
					NULL
				</cfif>
				,
				<cfif IsDefined("FORM.POCcity#epoc#") AND #trim(evaluate("FORM.POCcity"&epoc))# NEQ "">
					'#trim(replace(evaluate("FORM.POCcity"&epoc),"'", "''"))#'
				<cfelse>
					NULL
				</cfif>
				,
				<cfif IsDefined("FORM.POCstate#epoc#") AND #trim(evaluate("FORM.POCstate"&epoc))# NEQ "">
					'#trim(replace(evaluate("FORM.POCstate"&epoc),"'", "''"))#'
				<cfelse>
					NULL
				</cfif>
				,
				<cfif IsDefined("FORM.POCzip#epoc#") AND #trim(evaluate("FORM.POCzip"&epoc))# NEQ "">
					'#trim(replace(evaluate("FORM.POCzip"&epoc),"'", "''"))#'
				<cfelse>
					NULL
				</cfif>
				,
				<cfif IsDefined("FORM.POChomephone#epoc#") AND #trim(evaluate("FORM.POChomephone"&epoc))# NEQ "">
					'#trim(evaluate("FORM.POChomephone"&epoc))#'
				<cfelse>
					NULL
				</cfif>
				,
				<cfif IsDefined("FORM.POCworkphone#epoc#") AND #trim(evaluate("FORM.POCworkphone"&epoc))# NEQ "">
					'#trim(evaluate("FORM.POCworkphone"&epoc))#'
				<cfelse>
					NULL
				</cfif>
				,
				<cfif IsDefined("FORM.POCcellPhone#epoc#") AND #trim(evaluate("FORM.POCcellPhone"&epoc))# NEQ "">
					'#trim(evaluate("FORM.POCcellPhone"&epoc))#'
				<cfelse>
					NULL
				</cfif>
				)
		</cfquery>
	</cfif>
</cfloop>

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
Your code is very complex for me. I see 'evaluate' and seem to understand it, but the 'replace' is throwing me for a loop. Why are some evaluate while others are replace?

rojas1mg - - - I love Tek-Tips and all members who reply.
 
I was having issues with preserving single quotes

CF usualy automatically dubbles up on single quotes when you're between <cfquery> tags

<cfset myVar = "bob's rib house">

using #myVar# inside a query would make the value into the DB
"bob''s rib house" it's a 'courtesy' CF gives its users.

for some reason it was not working correctly so I had to do it manually you don't need to worry about it.

the Replace function is not used instead of evaluate they are in different places.


Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
Oh ok. Still a bit over my head, but I'm sure I'll learn it.

What I'm trying to do on my page is this. I have a form where a manager can register up to 10 of his people for training. If the manager only has 4, then it should only pass the first 4 and ignore the rest.

Can you help me out with my code on this?

rojas1mg - - - I love Tek-Tips and all members who reply.
 
Am I on the right track with the following:
Code:
<cfloop index = "register" from = "1" to = "10">
	<cfif IsDefined("form.name#register#") and #trim(evaluate("form.name"&register))#NEQ"">
		<cfquery datasource="#dsource#">
		INSERT INTO tbl_roster(
			name, 
			Rank_ID, 
			SSN, 
			email, 
			Unit_ID, 
			Course_ID)
		VALUES(
			'#trim(evaluate("form.name"&register))#', 
			<cfif IsDefined("form.rank#register#") AND #trim(evaluate("form.rank"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>
			#form.Rank#, 
			#form.ssn#,
			'#form.email#', 
			#form.unit_ID#, 
			#form.course_id#)
</cfquery>
	</cfif>
</cfloop>

rojas1mg - - - I love Tek-Tips and all members who reply.
 
that looks about right yep.

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
I don't get any errors and nothing shows up in the DB. Any ideas? Here's my action page code updated:
Code:
<cfloop index = "register" from = "1" to = "10">
	<cfif IsDefined("form.name#register#") and #trim(evaluate("form.name"&register))#NEQ"">
		<cfquery datasource="#dsource#">
		INSERT INTO tbl_roster(
			name, 
			Rank_ID, 
			SSN, 
			email, 
			Unit_ID, 
			Course_ID)
		VALUES(
			'#trim(evaluate("form.name"&register))#', 
			<cfif IsDefined("form.name#register#") AND #trim(evaluate("form.name"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.Rank_id"&register))#, 
			<cfif IsDefined("form.rank#register#") AND #trim(evaluate("form.rank"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.ssn"&register))#,
			<cfif IsDefined("form.ssn#register#") AND #trim(evaluate("form.ssn"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			'#trim(evaluate("form.email"&register))#',
			<cfif IsDefined("form.email#register#") AND #trim(evaluate("form.email"&register))# GT 18>
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.unit_ID"&register))#,
			<cfif IsDefined("form.unit_id#register#") AND #trim(evaluate("form.unit_id"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.course_id"&register))#,
			<cfif IsDefined("form.course_id#register#") AND #trim(evaluate("form.course_id#register#"))# NEQ"">
			<cfelse>
				NULL
			</cfif>
</cfquery>
	</cfif>
</cfloop>

rojas1mg - - - I love Tek-Tips and all members who reply.
 
sounds like
<cfif IsDefined("form.name#register#") and #trim(evaluate("form.name"&register))#NEQ"">
is never evaluating to true

lets say register = 1 you're asking the following

<cfif isdefined("form.name1") and trim(form.name1) neq "">

so make sure the form fields in your form are named correctly with numbers (i may have not explained that part, sorry)

name1, name2 name3...
rank1, rank2, rank3...
and so on.

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
They are being dynamically numbered with the loop:
Code:
<cfloop from="1" to="10" index="Register">
<tr>
<!---Enter name (Last, First)--->
    <td><input type="text" name="name#register#" size="45"></td>
<!---Rank--->
    <td><select name="rank#register#">
        <cfoutput query="rank">
        <option value="#Rank_ID#">#Rank#</option>
        </cfoutput></select></td>
<!---Enter SSAN--->
    <td><input type="Text" name="SSN#register#" size="15"></td>
    <!---Enter e-mail address--->
    <td><input type="Text" name="email#register#" value="@varich.ang.af.mil" size="30"></td>
<!---Select Unit--->
    <td><select name="Unit_ID#register#">
        <cfoutput query="dp_unit">
        <option value="#Unit_ID#">#Unit#</option>
        </cfoutput></select></td>
<!---Select Refresher Class Dated--->
    <td><cfoutput query="class">#Course_Date#</cfoutput></td>
</tr>
</cfloop>

I know it's usually Syntax, Syntax, Syntax, but I just can't see it. See if you can. Below is the completed action page:
Code:
<cfloop index = "register" from = "1" to = "10">
	<cfif IsDefined("form.name#register#") and #trim(evaluate("form.name"&register))#NEQ"">
		<cfquery datasource="#dsource#">
		INSERT INTO tbl_roster(
			name, 
			Rank_ID, 
			SSN, 
			email, 
			Unit_ID, 
			Course_ID)
		VALUES(
			'#trim(evaluate("form.name"&register))#', 
			<cfif IsDefined("form.name#register#") AND #trim(evaluate("form.name"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.Rank_id"&register))#, 
			<cfif IsDefined("form.rank#register#") AND #trim(evaluate("form.rank"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.ssn"&register))#,
			<cfif IsDefined("form.ssn#register#") AND #trim(evaluate("form.ssn"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			'#trim(evaluate("form.email"&register))#',
			<cfif IsDefined("form.email#register#") AND #trim(evaluate("form.email"&register))# GT 18>
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.unit_ID"&register))#,
			<cfif IsDefined("form.unit_id#register#") AND #trim(evaluate("form.unit_id"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.course_id"&register))#,
			<cfif IsDefined("form.course_id#register#") AND #trim(evaluate("form.course_id#register#"))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			url.course_id
</cfquery>
	</cfif>
</cfloop>
<html>
<head>
<title>Thank you for Registering</title>
</head>
<body>
<h3><br>Thank you for registering your people for Disaster Preparedness training online!  Please be sure to mark your calendar.</h3>
</body>
</html>



rojas1mg - - - I love Tek-Tips and all members who reply.
 
I turned on debugging and I'm even more confused. Does this make sense?
Code:
Form Fields:
EMAIL#REGISTER#=joe.bagadonuts@varich.ang.af.mil,peter.piper@varich.ang.af.mil,@varich.ang.af.mil,@varich.ang.af.mil,@varich.ang.af.mil,@varich.ang.af.mil,@varich.ang.af.mil,@varich.ang.af.mil,@varich.ang.af.mil,@varich.ang.af.mil
FIELDNAMES=NAME#REGISTER#,RANK_ID#REGISTER#,SSN#REGISTER#,EMAIL#REGISTER#,UNIT_ID#REGISTER#,REGISTER
NAME#REGISTER#=Bagadonuts, Joe,Piper, Peter,Rojas, Greg
RANK_ID#REGISTER#=4,6,9,1,1,1,1,1,1,1
REGISTER=Register
SSN#REGISTER#=111111111,222222222,3
UNIT_ID#REGISTER#=4,14,1,1,1,1,1,1,1,1

rojas1mg - - - I love Tek-Tips and all members who reply.
 
HA yeah i see it

you need cfoutput tags around your #register# in your form

the form field's name coming back to the action page is
form.name#register#
form.rank#register#
...
...

and not what you want which is
form.name1
form.rank1

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
I'm a little bit closer. Now I get the following error:
Code:
Error Occurred While Processing Request  
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 12: Incorrect syntax near ','.  
  
The error occurred in C:\Intranet\192ces\dp_training\utmregister_thanks.cfm: line 40
 
38 : 			</cfif>,
39 : 			'Now()',
40 : 			#form.course_id#
41 : 			<!---
42 : 			#trim(evaluate("form.course_id"&register))#,

 

--------------------------------------------------------------------------------
 
SQL    INSERT INTO tbl_roster( name, Rank_ID, SSN, email, Unit_ID, SignUp_date, Course_ID) VALUES( 'Bunny, Bugs', , '3', NULL , '111111111', , '1111@varich.ang.af.mil', NULL , '7', , 'Now()', 4,4,4,4,4,4,4,4,4,4  
DATASOURCE   dp 
VENDORERRORCODE   170 
SQLSTATE   HY000
Where is it getting the blank after the name?

rojas1mg - - - I love Tek-Tips and all members who reply.
 
<cfset variables.damn="nice">

<cfoutput>#Damn# cfoutput's</cfoutput> ;-)

Some people can learn, some people can teach.
 
you have extra ,'s in there in a few places

for example

</cfif>,
#trim(evaluate("form.Rank_id"&register))# extra comma inside the cfif block right here ,
<cfif IsDefined("form.rank#register#") AND #trim(evaluate("form.rank"&register))# NEQ"">
<cfelse>
NULL
</cfif>,
you don't need them inside the <cfif>'s only after the closing </cfif> tag

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
you also need a closing ) just before your </cfquery>

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
Here's my action page, followed by the error.
Code:
<cfloop index = "register" from = "1" to = "10">
	<cfif IsDefined("form.name#register#") and #trim(evaluate("form.name"&register))#NEQ"">
		<cfquery datasource="#dsource#">
		INSERT INTO tbl_roster(
			name, 
			Rank_ID, 
			SSN, 
			email, 
			Unit_ID,
			SignUp_date, 
			Course_ID)
		VALUES(
			'#trim(evaluate("form.name"&register))#' 
			<cfif IsDefined("form.name#register#") AND #trim(evaluate("form.name"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.Rank_id"&register))#
			<cfif IsDefined("form.rank_id_id#register#") AND #trim(evaluate("form.rank_id"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.ssn"&register))#
			<cfif IsDefined("form.ssn#register#") AND #trim(evaluate("form.ssn"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			'#trim(evaluate("form.email"&register))#'
			<cfif IsDefined("form.email#register#") AND #trim(evaluate("form.email"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.unit_ID"&register))#
			<cfif IsDefined("form.unit_id#register#") AND #trim(evaluate("form.unit_id"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			'Now()',
			#form.course_id#)
			<!---
			#trim(evaluate("form.course_id"&register))#,
			<cfif IsDefined("form.course_id#register#") AND #trim(evaluate("form.course_id#register#"))# NEQ"">
			<cfelse>
				NULL
			</cfif>
			--->
		</cfquery>
	</cfif>
</cfloop>

Here's the errors I'm getting.
Code:
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'NULL'.  
  
The error occurred in C:\Intranet\192ces\dp_training\utmregister_thanks.cfm: line 40
 
38 : 			</cfif>,
39 : 			'Now()',
40 : 			#form.course_id#)
41 : 			<!---
42 : 			#trim(evaluate("form.course_id"&register))#,

 

--------------------------------------------------------------------------------
 
SQL    INSERT INTO tbl_roster( name, Rank_ID, SSN, email, Unit_ID, SignUp_date, Course_ID) VALUES( 'Rojas, Greg' , 11 NULL , 111111111 , '1111@varich.ang.af.mil' , 4 , 'Now()', 4,4,4,4,4,4,4,4,4,4)  
DATASOURCE   dp 
VENDORERRORCODE   156 
SQLSTATE   HY000 
 
Please try the following: 
Check the ColdFusion documentation to verify that you are using the correct syntax. 
Search the Knowledge Base to find a solution to your problem. 

 
Browser   Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; VAANG) 
Remote Address   132.50.181.132 
Referrer   [URL unfurl="true"]https://intranet.varich.ang.af.mil/192ces/dp_training/utm_register_continue.cfm?course_id=4[/URL] 
Date/Time   07-Oct-04 10:01 AM 
 
Stack Trace (click to expand)
It seems to be putting in the NULL after the Rank_ID and then not excluding the next couple of course_id lines. I'm sorry to be such a pest, but it's really kicking my butt.

rojas1mg - - - I love Tek-Tips and all members who reply.
 
these are sql errors so you can easily find the problems by looking at the syntax provided in the error. for example.

INSERT INTO tbl_roster( name, Rank_ID, SSN, email, Unit_ID, SignUp_date, Course_ID) VALUES( 'Rojas, Greg' , 11 NULL , 111111111 , '1111@varich.ang.af.mil' , 4 , 'Now()', 4,4,4,4,4,4,4,4,4,4)


you will notice that there is no comma between 11 and NULL

you also have 'Now()' which is not a date it will be a string, you need to make it '#now()#'

you also have 7 fields but you have 17 values. that will be a problem too. where are all the 4's coming from at the end? it looks like your course ID in the form is outside of your loop and not getting named correctly.

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
I'm so close. Yet I can't see WHY it's keeps putting in the NULL after the RANK_ID. Here's my action page code:
Code:
<cfloop index = "register" from = "1" to = "10">
	<cfif IsDefined("form.name#register#") and #trim(evaluate("form.name"&register))#NEQ"">
		<cfquery datasource="#dsource#">
		INSERT INTO tbl_roster(
			name, 
			Rank_ID, 
			SSN, 
			email, 
			Unit_ID,
			SignUp_date, 
			Course_ID)
		VALUES(
			'#trim(evaluate("form.name"&register))#' 
			<cfif IsDefined("form.name#register#") AND #trim(evaluate("form.name"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.Rank_id"&register))#
			<cfif IsDefined("form.rank_id_id#register#") AND #trim(evaluate("form.rank_id"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.ssn"&register))#
			<cfif IsDefined("form.ssn#register#") AND #trim(evaluate("form.ssn"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			'#trim(evaluate("form.email"&register))#'
			<cfif IsDefined("form.email#register#") AND #trim(evaluate("form.email"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			#trim(evaluate("form.unit_ID"&register))#
			<cfif IsDefined("form.unit_id#register#") AND #trim(evaluate("form.unit_id"&register))# NEQ"">
			<cfelse>
				NULL
			</cfif>,
			
			#Now()#,
			
			#trim(evaluate("form.course_id"&register))#,
			<cfif IsDefined("form.course_id#register#") AND #trim(evaluate("form.course_id#register#"))# NEQ"">
			<cfelse>
				NULL
			</cfif>

		</cfquery>
	</cfif>
</cfloop>
And here's the error screen:
Code:
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'NULL'.  
  
The error occurred in C:\Intranet\192ces\dp_training\utmregister_thanks.cfm: line 46
 
44 : 			<cfelse>
45 : 				NULL
46 : 			</cfif>
47 : 
48 : 		</cfquery>

 

--------------------------------------------------------------------------------
 
SQL    INSERT INTO tbl_roster( name, Rank_ID, SSN, email, Unit_ID, SignUp_date, Course_ID) VALUES( 'Bunny, Bugs' , 3 NULL , 111111111 , '111@varich.ang.af.mil' , 3 , {ts '2004-10-07 11:00:49'}, 4,  
DATASOURCE   dp 
VENDORERRORCODE   156 
SQLSTATE   HY000

rojas1mg - - - I love Tek-Tips and all members who reply.
 
you have the value in the wrong spot

Code:
 #trim(evaluate("form.Rank_id"&register))#
            <cfif IsDefined("form.rank_id_id#register#") AND #trim(evaluate("form.rank_id"&register))# NEQ"">
            <cfelse>
                NULL
            </cfif>,

should be

Code:
            <cfif IsDefined("form.rank_id_id#register#") AND #trim(evaluate("form.rank_id"&register))# NEQ"">
[b]#trim(evaluate("form.Rank_id"&register))#[/b]
            <cfelse>
                NULL
            </cfif>,

you also removed the closing ) again. put it back.

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top