14 Replies - 2371 Views - Last Post: 28 November 2008 - 01:29 PM Rate Topic: -----

#1 cumupkid  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 22-January 08

CF Update not updating data

Post icon  Posted 26 November 2008 - 09:54 AM

Can not get info to update to data base.


<cfparam name="URL.Phone" default="1">
<cfparam name="URL.ZipRecordNum" default="1">

<cfquery name="rsCustomer" datasource="DTDatabase">
SELECT *
FROM Customers
WHERE Phone = <cfqueryparam value="#URL.Phone#" cfsqltype="cf_sql_clob" maxlength="10"> 
</cfquery>

<cfquery name="rsCustomerCitySt" datasource="DTDatabase">
SELECT *
FROM Zips
WHERE ZipCodeNum = <cfqueryparam value="#URL.Zip#" cfsqltype="cf_sql_clob" maxlength="10">
</cfquery>

<cfif isdefined("form.phone")>
<cffunction  name="customeredit" access="public" returntype="void">
	 <cfargument name="formdata" type="struct" required="yes" value="#form#"> 

		<cfquery name="customeredit" datasource="DTDatabase">
			Update customers
			set firstname1 = '#formdata.firstname1#',
				lastname = '#formdata.lastname#',
				firstname2 = '#formdata.firstname2#',
				phone = '#formdata.phone#',
				add1 = '#formdata.add1#',
				zip = '#formdata.zip#',
			where phone = #phone#
   		</cfquery>
</cffunction>

<cflocation url="index.cfm?Phone=#rsCustomer.Phone#" addtoken="no">

<cfelse>
<cffunction name="getcustomerquerybyphone" access="public" returntype="query">
		<cfargument name="phone" type="numeric" required="yes">

		<cfquery name="qCustomerList" datasource="DTDatabase"> 
			SELECT FIRSTNAME1, LASTNAME, ADD1, ZIP, FIRSTNAME2, PHONE
			FROM customers
			where phone = #arguments.phone# 
		</cfquery>
		
	   <cfreturn qCustomerList>

</cffunction>
</cfif>


<!-- pageName -->
	<table width="100%">
		<td valign="top" class="pageName"><div align="center">Edit Customer Details</div></td>
	</table>
	
<table border="0" cellspacing="0" cellpadding="0" width="100%">
		<tr>
			<td valign="bottom" colspan="" nowrap="nowrap" class="pageHeader" height="85" width="100%">
				<cfoutput> Details for #rsCustomer.FirstName1# #rsCustomer.LastName#</cfoutput>		  </td>
		</tr>
</table>

<table border="0" cellspacing="0" cellpadding="0" width="100%">
		<tr valign="bottom">
			<td height="60" colspan="7" align="center" nowrap="nowrap" id="alphaLinks"><p>
		</tr>
</table>


<cfform>

<table bgcolor="99CCFF" align="center" width="50%" border="0" id="table">

  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">First Name:</td>
	<td width="50%" height="20" align=" right"><cfinput type="text" name="firstname1" id="firstname1" tabindex="1" value="#rsCustomer.FirstName1#" maxlength="200"></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">Last Name:</td>
	<td width="50%" height="20"><cfinput type="text" name="lastname" id="lastname" tabindex="2" value="#rsCustomer.LastName#" maxlength="20"></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">Email:</td>
	<td width="50%" height="20"><cfinput type="text" name="email" validateat="onsubmit" validate="Email" id="email" tabindex="3" value="#rsCustomer.FirstName2#" maxlength="50" typeahead="no" showautosuggestloadingicon="true"></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">Phone:</td>
	<td width="50%" height="20"><cfinput type="text" name="phone" validateat="onsubmit" validate="telephone" id="phone" tabindex="4" value="#rsCustomer.Phone#" maxlength="12" readonly="true" typeahead="no" showautosuggestloadingicon="true"></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">Street:</td>
	<td width="50%" height="20"><cfinput type="text" name="add1" id="add1" tabindex="5" value="#rsCustomer.Add1#" maxlength="50"></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">City:</td>
	<td width="50%" height="20"><cfinput type="text" name="city" id="city" tabindex="6" value="#rsCustomerCitySt.City#" maxlength="20"></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">State:</td>
<td width="50%" height="20"><cfselect name="State" id="State" tabindex="7"> <cfoutput>
  <option value="#rsCustomerCitySt.State#" selected="selected">#rsCustomerCitySt.State#</option>
</cfoutput>
	<option value="AK">AK</option>
	<option value="AL">AL</option>
	<option value="AR">AR</option>
	<option value="AZ">AZ</option>
	<option value="CA">CA</option>
	<option value="CO">CO</option>
	<option value="CT">CT</option>
	<option value="DC">DC</option>
	<option value="DE">DE</option>
	<option value="FL">FL</option>
	<option value="GA">GA</option>
	<option value="HI">HI</option>
	<option value="IA">IA</option>
	<option value="ID">ID</option>
	<option value="IL">IL</option>
	<option value="IN">IN</option>
	<option value="KS">KS</option>
	<option value="KY">KY</option>
	<option value="LA">LA</option>
	<option value="MA">MA</option>
	<option value="MD">MD</option>
	<option value="ME">ME</option>
	<option value="MI">MI</option>
	<option value="MN">MN</option>
	<option value="MO">MO</option>
	<option value="MS">MS</option>
	<option value="MT">MT</option>
	<option value="NC">NC</option>
	<option value="ND">ND</option>
	<option value="NE">NE</option>
	<option value="NH">NH</option>
	<option value="NJ">NJ</option>
	<option value="NM">NM</option>
	<option value="NV">NV</option>
	<option value="NY">NY</option>
	<option value="OH">OH</option>
	<option value="OK">OK</option>
	<option value="OR">OR</option>
	<option value="PA">PA</option>
	<option value="PR">PR</option>
	<option value="RI">RI</option>
	<option value="SC">SC</option>
	<option value="SD">SC</option>
	<option value="TN">TN</option>
	<option value="TX">TX</option>
	<option value="UT">UT</option>
	<option value="VA">VA</option>
	<option value="VT">VT</option>
	<option value="WA">WA</option>
	<option value="WI">WI</option>
	<option value="WV">WV</option>
	<option value="WY">WY</option>
</cfselect></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">Zip:</td>
	<td width="50%" height="20"><cfinput type="text" name="zip" validateat="onsubmit" validate="zipcode" id="zip" tabindex="8" value="#rsCustomer.Zip#" maxlength="5"></td>
  </tr>
  <tr>
	<td width="25%" height="20" align="right">&nbsp;</td>
	<td width="25%" height="20" align="right"><cfinput type="submit" name="Submit" value="Submit" id="Submit"></td>
	<td width="50%" height="20"><cfinput type="button" name="cancel" id="Reset" value="Cancel" onclick="history.back()"></td>
  </tr>
</table>
</cfform>	





Is This A Good Question/Topic? 0
  • +

Replies To: CF Update not updating data

#2 CR250  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 58
  • Joined: 14-June 08

Re: CF Update not updating data

Posted 26 November 2008 - 10:57 AM

Whats the error message?
Was This Post Helpful? 0
  • +
  • -

#3 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: CF Update not updating data

Posted 26 November 2008 - 02:09 PM

View Postcumupkid, on 26 Nov, 2008 - 08:54 AM, said:

Can not get info to update to data base.



I see a few problems, but the important ones first. I bet nothing happens because you have defined two functions, but you never invoke them. You have to call the functions for the update to run. Call the function and pass in the form values:

<cfif isdefined("form.phone")>
.....
<!--- run the function --->
<cfset customeredit( formdata= FORM)>

</cfif>

Quote

<cfparam name="URL.Phone" default="1">
....
<cflocation url="index.cfm?Phone=#rsCustomer.Phone#" addtoken="no">


You should probably be using the #form.phone# value, not #rsCustomer.Phone#. The #rsCustomer.Phone# value is based on URL.Phone, which won't exist when you submit the form. So it will contain the wrong phone number.
Was This Post Helpful? 0
  • +
  • -

#4 cumupkid  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 22-January 08

Re: CF Update not updating data

Posted 27 November 2008 - 07:26 AM

Thanks for the info.
Im not sure where it is I am supposed to place this code

<cfif isdefined("form.phone")>
.....
	<!--- run the function --->
	<cfset customeredit( formdata= FORM)>

</cfif>



Tried different places and nothing worked.

and to the other person that saked what error message I was getting.
No error message. it does everything but upload the changes.

Also heres another question.
I have to upload changes to two differnt DB Tables, city and state are in a different table then the other info.

My question is:
How do I INSERT the city, state and zipcodenumber into the other DB table at the same time as updating the first table?
I DO NOT want to change any of the info in second DB table if it is already there.

Thanks again
Was This Post Helpful? 0
  • +
  • -

#5 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: CF Update not updating data

Posted 27 November 2008 - 10:17 AM

View Postcumupkid, on 27 Nov, 2008 - 06:26 AM, said:

Im not sure where it is I am supposed to place this code
...
and to the other person that saked what error message I was getting.
No error message. it does everything but upload the changes.


Yes, that is because the function to do the update is never called. The way you have your code setup, the block below should execute whenever you submit the form. Call the function inside that block, but _before_ the cflocation. If you accidentally put it after the cflocation, the user will be redirected first and the function will never run.

<cfif isdefined("form.phone")>
	 <cffunction  name="customeredit" access="public" returntype="void">
		..... rest of the code omitted for brevity ...
	 </cffunction>

	  <!--- run the function --->
	 <cfset customeredit( formdata= FORM)>

	<cflocation url="index.cfm?Phone=#form.Phone#" addtoken="no">

<cfelse>
		..... rest of the code omitted for brevity ...
</cfif>




View Postcumupkid, on 27 Nov, 2008 - 06:26 AM, said:

Also heres another question.
I have to upload changes to two differnt DB Tables, city and state are in a different table then the other info.

My question is:
How do I INSERT the city, state and zipcodenumber into the other DB table at the same time as updating the first table?
I DO NOT want to change any of the info in second DB table if it is already there.


So you want to insert the city, state and zipCodeNumber only if they do _not_ exist in the second table? What database type are you using?
Was This Post Helpful? 0
  • +
  • -

#6 cumupkid  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 22-January 08

Re: CF Update not updating data

Posted 27 November 2008 - 11:14 AM

Whn I place the code like you said I get:

java.lang.StackOverflowError

by the way I am using a MySQL database.

Strike that last entry

The error message is:

Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

Code for update statement is:

 <cfif isdefined("form.phone")>
<cffunction  name="customeredit" access="public" returntype="void">
	 <cfargument name="formdata" type="struct" required="yes" value="#form#"> 

		<cfquery name="customeredit" datasource="DTDatabase">
			Update customers
			set firstname1 = '#formdata.firstname1#',
				lastname = '#formdata.lastname#',
				firstname2 = '#formdata.firstname2#',
				phone = '#formdata.phone#',
				add1 = '#formdata.add1#',
				zip = '#formdata.zip#',
			where phone = #phone#
   		</cfquery>
			   
</cffunction>


Was This Post Helpful? 0
  • +
  • -

#7 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: CF Update not updating data

Posted 27 November 2008 - 11:30 AM

View Postcumupkid, on 27 Nov, 2008 - 10:14 AM, said:

by the way I am using a MySQL database.

Strike that last entry

The error message is:

Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

Code for update statement is:

 <cfif isdefined("form.phone")>
<cffunction  name="customeredit" access="public" returntype="void">
	 <cfargument name="formdata" type="struct" required="yes" value="#form#"> 

		<cfquery name="customeredit" datasource="DTDatabase">
			Update customers
			set firstname1 = '#formdata.firstname1#',
				lastname = '#formdata.lastname#',
				firstname2 = '#formdata.firstname2#',
				phone = '#formdata.phone#',
				add1 = '#formdata.add1#',
				zip = '#formdata.zip#',
			where phone = #phone#
   		</cfquery>
			   
</cffunction>



From the error message, you are using MS Access not MySQL. The syntax error is probably caused by the extra comma after the zip code:

zip = '#formdata.zip#', <!--- remove the comma at the end --->

(Once you get the query working, be sure should use cfqueryparam everywhere)
Was This Post Helpful? 0
  • +
  • -

#8 cumupkid  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 22-January 08

Re: CF Update not updating data

Posted 27 November 2008 - 11:39 AM

Okay did that no the error is:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

what did you mean about using cfqueryparam everywhere?

Thanks
Was This Post Helpful? 0
  • +
  • -

#9 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: CF Update not updating data

Posted 27 November 2008 - 11:54 AM

View Postcumupkid, on 27 Nov, 2008 - 10:39 AM, said:

Okay did that no the error is:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.



That did fix the error .. the first one anyway ;-) Your current error is different. The message it is saying there is a problem with data types. It is likely due to the #phone# value. What is the data type of that column in your table? If it is 'Text' you have to put single quotes around the variable in your query. Right now you don't have it in quotes so it is being treated as a number, causing an error.

...
 where phone = #phone#



Quote

what did you mean about using cfqueryparam everywhere?


Look at your first query "rsCustomer". You are using cfqueryparam for the user supplied values (ie the URL variables). You should do the same in your UPDATE query for the FORM variables. But get your query working with plain strings first.
Was This Post Helpful? 0
  • +
  • -

#10 cumupkid  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 22-January 08

Re: CF Update not updating data

Posted 27 November 2008 - 12:09 PM

Sweet that last thing got the update working. Thanks.

Can you give me an example of what you mean about the cfqueryparam.

Thanks again for the help.
Was This Post Helpful? 0
  • +
  • -

#11 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: CF Update not updating data

Posted 27 November 2008 - 12:37 PM

View Postcumupkid, on 27 Nov, 2008 - 11:09 AM, said:

Can you give me an example of what you mean about the cfqueryparam.


It just means whenever you use a user value (like URL or FORM) in sql you should put it inside a cfqueryparam tag. The key part is to use the correct cfsqltype. The cfsqltype corresponds to the data type of whatever column you are using. Since your fields all appear to be MS Access "text" you would use cfsqltype="cf_sql_varchar"

 Update customers
SET  firstname1 =  <cfqueryparam value="#formdata.firstname1#" cfsqltype="cf_sql_varchar"> ,
.... other fields




View Postsansclue, on 27 Nov, 2008 - 11:36 AM, said:

View Postcumupkid, on 27 Nov, 2008 - 11:09 AM, said:

Can you give me an example of what you mean about the cfqueryparam.


It just means whenever you use a user value (like URL or FORM) in sql you should put it inside a cfqueryparam tag. The key part is to use the correct cfsqltype. The cfsqltype corresponds to the data type of whatever column you are using. Since your fields all appear to be MS Access "text" you would use cfsqltype="cf_sql_varchar"

<cfquery ...>
 Update customers
SET  firstname1 =  <cfqueryparam value="#formdata.firstname1#" cfsqltype="cf_sql_varchar"> ,
.... other fields
</cfquery>



Was This Post Helpful? 0
  • +
  • -

#12 cumupkid  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 22-January 08

Re: CF Update not updating data

Posted 27 November 2008 - 03:14 PM

Okay got all that working now. Thanks alot.

Now, any suggestions on getting the city state and zip to be inserted into a different table, but only if its not already there.


<cfparam name="URL.Phone" default="1">
<cfparam name="URL.ZipCodeNum" default="1">

<cfquery name="rsCustomer" datasource="DTDatabase">
SELECT *
FROM Customers
WHERE Phone = <cfqueryparam value="#URL.Phone#" cfsqltype="cf_sql_clob" maxlength="10"> 
</cfquery>

<cfquery name="rsCustomerCitySt" datasource="DTDatabase">
SELECT *
FROM Zips
WHERE ZipCodeNum = <cfqueryparam value="#URL.Zip#" cfsqltype="cf_sql_clob" maxlength="10">
</cfquery>

<cfif isdefined("form.phone")>
<cffunction  name="customeredit" access="public" returntype="void">
	 <cfargument name="formdata" type="struct" required="yes" value="#form#"> 

		<cfquery name="customeredit" datasource="DTDatabase">
			Update customers
			set firstname1 = '#formdata.firstname1#',
				lastname = '#formdata.lastname#',
				firstname2 = '#formdata.firstname2#',
				phone = '#formdata.phone#',
				add1 = '#formdata.add1#',
				zip = '#formdata.zip#'
			where phone = '#phone#'
   		</cfquery>
			   
</cffunction>
<cfset customeredit( formdata= FORM)>
<cflocation url="index.cfm?Phone=#form.Phone#" addtoken="no">

<cfelse>
<cffunction name="getcustomerquerybyphone" access="public" returntype="query">
		<cfargument name="phone" type="numeric" required="yes">

		<cfquery name="qCustomerList" datasource="DTDatabase"> 
			SELECT FIRSTNAME1, LASTNAME, ADD1, ZIP, FIRSTNAME2, PHONE
			FROM customers
			where phone = #arguments.phone# 
		</cfquery>
		
	   <cfreturn qCustomerList>

</cffunction>


</cfif>


<!-- pageName -->
	<table width="100%">
		<td valign="top" class="pageName"><div align="center">Edit Customer Details</div></td>
	</table>
	
<table border="0" cellspacing="0" cellpadding="0" width="100%">
		<tr>
			<td valign="bottom" colspan="" nowrap="nowrap" class="pageHeader" height="85" width="100%">
				<cfoutput> Details for #rsCustomer.FirstName1# #rsCustomer.LastName#</cfoutput>		  </td>
		</tr>
</table>

<table border="0" cellspacing="0" cellpadding="0" width="100%">
		<tr valign="bottom">
			<td height="60" colspan="7" align="center" nowrap="nowrap" id="alphaLinks"><p>
		</tr>
</table>


<cfform>

<table bgcolor="99CCFF" align="center" width="50%" border="0" id="table">

  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">First Name:</td>
	<td width="50%" height="20" align=" right"><cfinput type="text" name="firstname1" id="firstname1" tabindex="1" value="#rsCustomer.FirstName1#" cfsqltype="cf_sql_varchar" maxlength="200"></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">Last Name:</td>
	<td width="50%" height="20"><cfinput type="text" name="lastname" id="lastname" tabindex="2" value="#rsCustomer.LastName#" cfsqltype="cf_sql_varchar" maxlength="20"></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">Email:</td>
	<td width="50%" height="20"><cfinput type="text" name="firstname2" validateat="onsubmit" validate="Email" id="firstname2" tabindex="3" value="#rsCustomer.FirstName2#" cfsqltype="cf_sql_varchar" maxlength="50" typeahead="no" showautosuggestloadingicon="true"></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">Phone:</td>
	<td width="50%" height="20"><cfinput type="text" name="phone" validateat="onsubmit" validate="telephone" id="phone" tabindex="4" value="#rsCustomer.Phone#" cfsqltype="cf_sql_varchar" maxlength="12" readonly="true" typeahead="no" showautosuggestloadingicon="true"></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">Street:</td>
	<td width="50%" height="20"><cfinput type="text" name="add1" id="add1" tabindex="5" value="#rsCustomer.Add1#" cfsqltype="cf_sql_varchar" maxlength="50"></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">City:</td>
	<td width="50%" height="20"><cfinput type="text" name="city" id="city" tabindex="6" value="#rsCustomerCitySt.City#" cfsqltype="cf_sql_varchar" maxlength="20"></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">State:</td>
<td width="50%" height="20"><cfselect name="State" id="State" tabindex="7"> <cfoutput>
  <option value="#rsCustomerCitySt.State#" selected="selected">#rsCustomerCitySt.State#</option>
</cfoutput>
	<option value="AK">AK</option>
	<option value="AL">AL</option>
	<option value="AR">AR</option>
	<option value="AZ">AZ</option>
	<option value="CA">CA</option>
	<option value="CO">CO</option>
	<option value="CT">CT</option>
	<option value="DC">DC</option>
	<option value="DE">DE</option>
	<option value="FL">FL</option>
	<option value="GA">GA</option>
	<option value="HI">HI</option>
	<option value="IA">IA</option>
	<option value="ID">ID</option>
	<option value="IL">IL</option>
	<option value="IN">IN</option>
	<option value="KS">KS</option>
	<option value="KY">KY</option>
	<option value="LA">LA</option>
	<option value="MA">MA</option>
	<option value="MD">MD</option>
	<option value="ME">ME</option>
	<option value="MI">MI</option>
	<option value="MN">MN</option>
	<option value="MO">MO</option>
	<option value="MS">MS</option>
	<option value="MT">MT</option>
	<option value="NC">NC</option>
	<option value="ND">ND</option>
	<option value="NE">NE</option>
	<option value="NH">NH</option>
	<option value="NJ">NJ</option>
	<option value="NM">NM</option>
	<option value="NV">NV</option>
	<option value="NY">NY</option>
	<option value="OH">OH</option>
	<option value="OK">OK</option>
	<option value="OR">OR</option>
	<option value="PA">PA</option>
	<option value="PR">PR</option>
	<option value="RI">RI</option>
	<option value="SC">SC</option>
	<option value="SD">SC</option>
	<option value="TN">TN</option>
	<option value="TX">TX</option>
	<option value="UT">UT</option>
	<option value="VA">VA</option>
	<option value="VT">VT</option>
	<option value="WA">WA</option>
	<option value="WI">WI</option>
	<option value="WV">WV</option>
	<option value="WY">WY</option>
</cfselect></td>
  </tr>
  <tr>
	<td width="25%" height="20" class="subHeader">&nbsp;</td>
	<td width="25%" height="20" class="subHeader">Zip:</td>
	<td width="50%" height="20"><cfinput type="text" name="zip" validateat="onsubmit" validate="zipcode" id="zip" tabindex="8" value="#rsCustomer.Zip#" cfsqltype="cf_sql_varchar" maxlength="5"></td>
  </tr>
  <tr>
	<td width="25%" height="20" align="right">&nbsp;</td>
	<td width="25%" height="20" align="right"><cfinput type="submit" name="Submit" value="Submit" id="Submit"></td>
	<td width="50%" height="20"><cfinput type="button" name="cancel" id="Reset" value="Cancel" onclick="history.back()"></td>
  </tr>
</table>
</cfform>	



Was This Post Helpful? 0
  • +
  • -

#13 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: CF Update not updating data

Posted 27 November 2008 - 08:20 PM

View Postcumupkid, on 27 Nov, 2008 - 02:14 PM, said:

Now, any suggestions on getting the city state and zip to be inserted into a different table, but only if its not already there.


I don't really use MS Access. With MS SQL I would probably use a trigger or alternatively an insert/select with a not exists clause. Something like the untested code below


INSERT INTO someTable (City,State,Zip)
SELECT  '#form.city#', '#form.state#', '#form.zipcode#'
WHERE   NOT EXISTS (
SELECT  City 
FROM	 someTable
WHERE   City = '#form.city#' 
AND	   State = '#form.state#' 
AND	   ZipCode = '#form.zipcode#'
)



But I highly doubt that would work with Access.

A so-so option is to first query your table records for the #city#,#state# and #zip# values. Then check the query recordCount. If it is equal to zero, the information does not exist. So do the insert. That would work ... well, to a degree. But if two users hit the page at about the same time you may still end up with duplicates in your table.

Since this isn't specific to ColdFusion, you might ask around in an MS Access forum. Someone there might be able to give you a better answer. Sorry, I could not be of more help with that part.
Was This Post Helpful? 0
  • +
  • -

#14 cumupkid  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 22-January 08

Re: CF Update not updating data

Posted 28 November 2008 - 08:54 AM

Tried this:
<cffunction name="customerzipedit" access="public" returntype="void">
	<cfargument name="formdata" type="struct" required="yes" value="#form#">	   
		<cfquery name="customerzipedit" datasource="DTDatabase">
					INSERT INTO Zips (City,State,ZipCodeNum)
			SELECT  '#formdata.city#', '#formdata.state#', '#formdata.zip#'
			WHERE   NOT EXISTS (
			SELECT  City 
			FROM	 Zips
			WHERE   City = '#formdata.city#' 
			AND	   State = '#formdata.state#' 
			AND	   ZipCodenum = '#formdata.zip#'
			)
		</cfquery>
</cffunction>
			   

<cfset customeredit and customerzipedit( formdata= FORM)>



Got this:

Cannot convert the value of type class cfcustomerEdit2ecfm2089543862$funcCUSTOMEREDIT to a boolean

Any thoughts?
Was This Post Helpful? 0
  • +
  • -

#15 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: CF Update not updating data

Posted 28 November 2008 - 01:29 PM

View Postcumupkid, on 28 Nov, 2008 - 07:54 AM, said:

Tried this:
<cfset customeredit and customerzipedit( formdata= FORM)>



Got this:

Cannot convert the value of type class cfcustomerEdit2ecfm2089543862$funcCUSTOMEREDIT to a boolean

Any thoughts?


It is the extra <cfset customeredit and ... portion. in your cfset statement. Since the function does not return anything, it should be more like:

<cfset customerzipedit( formdata= FORM)>

But again, I do not know if the syntax I mentioned even works with MS Access. It might .. but I would be surprised.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1