8 Replies - 1968 Views - Last Post: 29 October 2007 - 11:37 AM Rate Topic: -----

#1 lezlieao  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 26-September 07

database updates

Post icon  Posted 17 October 2007 - 12:19 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: database updates

#2 dswens  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 16-October 07

Re: database updates

Posted 23 October 2007 - 06:03 PM

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?
Was This Post Helpful? 0
  • +
  • -

#3 lezlieao  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 26-September 07

Re: database updates

Posted 24 October 2007 - 12:18 PM

View Postdswens, 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.
Was This Post Helpful? 0
  • +
  • -

#4 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1877
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: database updates

Posted 24 October 2007 - 12:21 PM

Can you see the entire query being executed in the debugger? What happens when you copy/paste that query in to SQL directly?
Was This Post Helpful? 0
  • +
  • -

#5 lezlieao  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 26-September 07

Re: database updates

Posted 24 October 2007 - 01:48 PM

View Postskyhawk133, 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.
Was This Post Helpful? 0
  • +
  • -

#6 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1877
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: database updates

Posted 24 October 2007 - 01:55 PM

View Postlezlieao, on 24 Oct, 2007 - 02:48 PM, said:

View Postskyhawk133, 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.
Was This Post Helpful? 0
  • +
  • -

#7 MizAthyKat  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 29-October 07

Re: database updates

Posted 29 October 2007 - 07:47 AM

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">
Was This Post Helpful? 0
  • +
  • -

#8 supersssweety  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 22
  • View blog
  • Posts: 373
  • Joined: 16-March 07

Re: database updates

Posted 29 October 2007 - 07:56 AM

are all of those entries filled out...I mean...will any of them be null?
Was This Post Helpful? 0
  • +
  • -

#9 lezlieao  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 26-September 07

Re: database updates

Posted 29 October 2007 - 11:37 AM

View Postsupersssweety, 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??
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1