[lezlieao]

Okay, so I am creating an admin page that lists different sets of the database for possible updates. I know i am going to need some kind of loop (i think) to get this to go through each of many fields and update, but I don't know where to begin!

This is what i have for the form (it is repeated twice for the different options)

 
<cfform action="admin2.cfm" method="post">

			
			<table width="80%">
			<tr>
				<td><strong>Status</strong></td>
				<td><strong>First Name</strong></td>
				<td><strong>Last Name</strong></td>
				<td><strong>Email</strong></td>
				<td><strong>Position</strong></td>
				<td><strong>Telephone</strong></td>
				<td><strong>Ext</strong>.</td>
				<td><strong>Fax </strong></td>
				<td><strong>Login </strong></td>
				<td><strong>Password </strong></td>
				<td><strong>ASP Wet Chem </strong></td>
				<td><strong>ASP Soybean </strong></td>
				<td><strong>ASP Soybean Meal </strong></td>
				<td><strong>Study Collaborator NIR </strong></td>
				<td><strong>Study Collaborator AAM </strong></td>
			</tr>
			<cfif appearance is "waiting">
			<cfoutput query="waiting">
			<tr>
				<td><input <cfif (#status# EQ 1)>checked="checked"</cfif> type="checkbox" name="status" size="2" value="#status#"></td>
				<td><input type="text" name="first_name" size="8" value="#first_name#"></td>
				<td><input type="text" name="last_name" size="8" value="#last_name#"></td>
				<td><input type="text" name="email" size="15" value="#email#"></td>
				<td><input type="text" name="title" size="8" value="#title#"></td>
				<td><input type="text" name="phone" size="8" value="#phone#"></td>
				<td><input type="text" name="ext" size="2" value="#ext#"></td>
				<td><input type="text" name="fax" size="8" value="#fax#"></td>
				<td><input type="text" name="uid" size="8" value="#uid#"></td>
				<td><input type="text" name="passwd" size="6" value="#passwd#"></td>
				<td><input <cfif (#asp_wc# EQ 1)>checked="checked"</cfif> type="checkbox" name="wc" size="2" value="#asp_wc#"></td>
				<td><input <cfif (#asp_sb# EQ 1)>checked="checked"</cfif> type="checkbox" name="sb" size="2" value="#asp_sb#"></td>
				<td><input <cfif (#asp_sbm# EQ 1)>checked="checked"</cfif> type="checkbox" name="sbm" size="2" value="#asp_sbm#"></td>
				<td><input <cfif (#sc_nir# EQ 1)>checked="checked"</cfif> type="checkbox" name="scnir" size="2" value="#sc_nir#">
				<td><input<cfif (#sc_aam# EQ 1)>checked= "checked"</cfif> type="checkbox" name="scaam" size="2" value="#sc_aam#">
			</tr>
			</cfoutput>
			<cfelseif appearance is "new">
			<cfoutput query="new">
			<tr>

and this is my processing page:

<title>Admin2</title>
<cfif IsDefined("Form.insertcontacts") is True>
   
   <cfquery name="edit" datasource="sqt_access">
   	UPDATE contacts
	SET 
	first_name = '#form.first_name#',
	last_name = '#form.last_name#',
	email = '#form.email#',
	title = '#form.title#',
	phone = '#form.phone#',
	ext = '#form.ext#',
	fax = '#form.fax#',
	uid = '#form.uid#',
	asp_sb = '#form.sb#',
	asp_sbm = '#form.sbm',
	asp_wc = '#form.wc#',
	sc_nir = '#form.nir#',
	sc_aam = '#form.aam#',
	edited = '1',
	status = '#form.status#'
	
	WHERE passwd = '#(form.passwd)#'

   </cfquery> 
   </cfif>
   <cflocation url = "admin.cfm">

Please help! Thanks

[dswens]

Are you generating any errors with the current setup or does this work as it is and you are just trying to make your code more intelligent?

[lezlieao]

dswens, on 23 Oct, 2007 - 06:03 PM, said:

Are you generating any errors with the current setup or does this work as it is and you are just trying to make your code more intelligent?

It doesn't generate any errors as is, it just doesn't actually update the database.

[skyhawk133]

Can you see the entire query being executed in the debugger? What happens when you copy/paste that query in to SQL directly?

[lezlieao]

skyhawk133, on 24 Oct, 2007 - 12:21 PM, said:

Can you see the entire query being executed in the debugger? What happens when you copy/paste that query in to SQL directly?

I'm not sure I understand, but I believe it would start sending errors about each line of the form, if they were not filled in.

[skyhawk133]

lezlieao, on 24 Oct, 2007 - 02:48 PM, said:

skyhawk133, on 24 Oct, 2007 - 12:21 PM, said:

Can you see the entire query being executed in the debugger? What happens when you copy/paste that query in to SQL directly?

I'm not sure I understand, but I believe it would start sending errors about each line of the form, if they were not filled in.

You should be able to turn on the debugger for your IP address in the CF Admin. This will show you all the queries that were run on the page and actually show you the full query that was passed to the database. if you don't see the query in the debugger, chances are your code never made it to it and it's a logic problem... if you see the query, copy and paste it in to SQL Manager and run it... see if any rows were effected, if not, then you need to re-work your query... probably a where clause being too restrictive or incorrectly done.

[MizAthyKat]

With regard to your query not updating the database, it is likely that you are searching for a value that does not exist, or you need to reconsider the WHERE clause in your update query.
You might want to try using:
WHERE trim(passwd) = '#(form.passwd)#'

With regard to turning on/off your debug info and query results, I put this in the top of my page:
<cfset request.showquerydetails = "Y">

While ALSO having this in the application.cfm page:
<CFsetting showdebugoutput = "Yes">

[supersssweety]

are all of those entries filled out...I mean...will any of them be null?

[lezlieao]

supersssweety, on 29 Oct, 2007 - 07:56 AM, said:

are all of those entries filled out...I mean...will any of them be null?

Some checkboxes and even possibly some text fields could be left null. Yes...I know this is causing me some problems, but I didn't think it would affect the update yet??