View comments | RSS feed
Contents > CFML Reference > ColdFusion Tags > cfupdate PreviousNext

cfupdate

Updates records in a data source from data in a ColdFusion form or form Scope.

Database manipulation tags

<cfupdate 
   dataSource = "ds_name"
   tableName = "table_name"
   tableOwner = "name"
   tableQualifier = "qualifier"
   username = "username"
   password = "password"
   formFields = "field_names">

cfinsert, cfprocparam, cfprocresult, cfquery, cfqueryparam, cfstoredproc, cftransaction

ColdFusion MX: Deprecated the connectString, dbName, dbServer, dbtype, provider and providerDSN attributes. They do not work, and might cause an error, in releases later than ColdFusion 5.

Attribute

Req/Opt

Default

Description

dataSource

Required

 

Name of the data source that contains the table.

tableName

Required

 

Name of table to update.

  • For ORACLE drivers, must be uppercase.
  • For Sybase driver: case-sensitive; must be in same case as used when the table was created

tableOwner

Optional

 

For data sources that support table ownership (for example, SQL Server, Oracle, Sybase SQL Anywhere), the table owner.

tableQualifier

Optional

 

For data sources that support table qualifiers. The purpose of table qualifiers is as follows:

  • SQL Server and Oracle: name of database that contains table
  • Intersolv dBASE driver: directory of DBF files

username

Optional

 

Overrides username value specified in ODBC setup.

password

Optional

 

Overrides password value specified in ODBC setup.

formFields

Optional

(all on form, except keys)

Comma-delimited list of form fields to update.

If a form field is not matched by a column name in the database, ColdFusion throws an error.

The formFields lies must include the database table primary key field, which must be present in the form. It can be hidden. 

<!--- This example shows the use of CFUPDATE to change
records in a datasource. --->
<!--- if course_ID has been passed to this form, then
perform the update on that record in the datasource --->

<cfif IsDefined("form.Course_ID")>
   <!--- check that course_id is numeric --->
      <cfif Not IsNumeric(form.Course_ID)>
         <cfabort>
      </cfif>
   <!--- Now, do the update --->
   <cfupdate datasource="cfsnippets"
      tablename="Courses" 
      formfields="Course_ID,Number,Descript">
</cfif>

<!--- Perform a query to reflect any updated information if Course_ID is passed
through a url, we are selecting a record to update ... select only that 
record with the WHERE clause. --->
<cfquery name="GetCourseInfo" DATASOURCE="cfsnippets">
   SELECT Course_Number, Course_ID, Descript
   FROM Courses
      <cfif IsDefined("url.Course_ID")>
         WHERE Course_ID = #Trim(url.Course_ID)#
      </cfif>
   ORDER by Course_Number
</cfquery>
<html>
<head>
   <title>CFUPDATE Example</title>
   <cfset css_path = "../../css">
   <cfinclude template="../../resource/include/mm_browsersniff.cfm">
</head>
<body>

<H3>CFUPDATE Example</H3>
<!--- If we are updating a record, don't show the entire list. --->
<cfif IsDefined("url.Course_ID")>
   <form method="post" action="index.cfm">
   <H3>You can alter the contents of this record, and then click "Update" 
      to use CFUPDATE and alter the database</H3>
   <P>Course Number <INPUT TYPE="Text" name="Number" value="<cfoutput>#Trim(GetCourseInfo.Course_Number)#</cfoutput>">
   <P>Course Description<BR>
   <textarea name="Descript" cols="40" rows="5">
      <cfoutput>#Trim(GetCourseInfo.Descript)#</cfoutput>
   </textarea><br>
   <input type="Hidden" NAME="Course_ID"
      value="<cfoutput>#Trim(GetCourseInfo.Course_ID)#</cfoutput>">
   <p><input type="Submit" value="Click to Update">
   </form>

<cfelse>
   <!--- Show the entire record set in CFTABLE form --->
   <cftable query="GetCourseInfo" htmltable colheaders>
      <cfcol text="<a href='index.cfm?Course_ID=#Trim(Course_ID)#'>Edit Me</a>" 
         width=10 header="Edit<br>this Entry">
      <cfcol text="#Trim(Course_Number)#" WIDTH="4" HEADER="Course Number">
      <cfcol text="#Trim(Descript)#" WIDTH=100 HEADER="Course Description">
   </cftable>
</cfif>
</body>
</html>
Contents > CFML Reference > ColdFusion Tags > cfupdate PreviousNext

ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | Bug Reporting

Version 6.1

Comments are no longer accepted for ColdFusion MX 6.1. ColdFusion 8 is the current version.

Comments


Pat Lib said on Sep 17, 2003 at 8:39 AM :
Following your example exactly, I made a live database test. All seems fine, except that the <cfupdate> did not do ANYTHING.
No data change happend.
I am not sure if this is a problem on <cfupdate> or on the sample codes in this doc.
If you would like to follow up, I will be more than happy to provide you my test codes and db results.

Thanks!
halL said on Sep 17, 2003 at 2:30 PM :
There are several problems with this example.

First, the cfsnippets Access database Courses table uses Name as a field name.
This is an Access reserved word and causes errors in cfupdate.

Second, the example code inconsistently uses Name and Course_Name for the field name.

Third, the example code attempts to include an mm_browsersniff.cfm file that is not likely to exist.

ColdFusion bugs 53454 and 53455 have been logged against these issues.

The following example code will work IF you rename the Number field to Course_Number in the snippets database.
It must be saved as index.cfm to work.

<!--- This example shows the use of CFUPDATE to change
records in a datasource. --->
<!--- if course_ID has been passed to this form, then
perform the update on that record in the datasource --->

<cfif IsDefined("form.Course_ID")>
<!--- check that course_id is numeric --->
<cfif Not IsNumeric(form.Course_ID)>
<cfabort>
</cfif>
<!--- Now, do the update --->
<cfupdate datasource="cfsnippets"
tablename="Courses"
formfields="Course_ID,Course_Number,Descript">
</cfif>

<!--- Perform a query to reflect any updated information if Course_ID is passed
through a url, we are selecting a record to update ... select only that
record with the WHERE clause. --->
<cfquery name="GetCourseInfo" DATASOURCE="cfsnippets">
SELECT Course_Number, Course_ID, Descript
FROM Courses
<cfif IsDefined("url.Course_ID")>
WHERE Course_ID = #Trim(url.Course_ID)#
</cfif>
ORDER by Course_Number
</cfquery>
<html>
<head>
<title>CFUPDATE Example</title>
</head>
<body>

<H3>CFUPDATE Example</H3>
<!--- If we are updating a record, don't show the entire list. --->
<cfif IsDefined("url.Course_ID")>
<form method="post" action="index.cfm">
<H3>You can alter the contents of this record, and then click "Update"
to use CFUPDATE and alter the database</H3>
<P>Course Number <INPUT TYPE="Text" name="Course_Number" value="<cfoutput>#Trim(GetCourseInfo.Course_Number)#</cfoutput>">
<P>Course Description<BR>
<textarea name="Descript" cols="40" rows="5">
<cfoutput>#Trim(GetCourseInfo.Descript)#</cfoutput>
</textarea><br>
<input type="Hidden" NAME="Course_ID"
value="<cfoutput>#Trim(GetCourseInfo.Course_ID)#</cfoutput>">
<p><input type="Submit" value="Click to Update">
</form>

<cfelse>
<!--- Show the entire record set in CFTABLE form --->
<cftable query="GetCourseInfo" htmltable colheaders>
<cfcol text="<a href='index.cfm?Course_ID=#Trim(Course_ID)#'>Edit Me</a>"
width=10 header="Edit<br>this Entry">
<cfcol text="#Trim(Course_Number)#" WIDTH="4" HEADER="Course Number">
<cfcol text="#Trim(Descript)#" WIDTH=100 HEADER="Course Description">
</cftable>
</cfif>
</body>
</html>
Malvina said on Feb 11, 2004 at 12:37 PM :
I do not understand this sentence:

"The formFields lies must include the database table primary key field, which must be present in the form. It can be hidden. "

comment
My experience with cfupdate is:
In cf 5 it was not necessary to include the formfields. In MX 6.1 it is required, otherwise I get an error of a missing key field.
MarkZet said on Mar 2, 2004 at 4:01 AM :
I've found that if ONLY the primary key is passed in the fieldlist, an error occurs ("You have an error in your SQL syntax").
halL said on Mar 2, 2004 at 6:46 AM :
In response to Malvina: The word lies should be keys.

In response to MarkZet: If you specify only the primary key, you are asking the database to update no fields in the table. ColdFusion generates an invalid SQL expression as a result (an empty set clause). We have submitted a bug (54462) against the fact that ColdFusion does not catch and report the invalid formfields value before generating the SQL and sending it to the data source.
wlee said on Mar 24, 2004 at 7:30 AM :
We are working to improve the examples in the ColdFusion reference pages. We propose to replace the current example on this page with the the following example. If you have any comments on this example, add them to this page.

<!--- This example allows you to update a person's telephone number in the employee table --->
<cfif isDefined("form.phone")>
<cfupdate datasource="cfsnippets"
tablename="Employees">
</cfif>

<cfquery name="empTable" datasource="cfsnippets">
select * from employees
</cfquery>

<!--- This code shows the contents of the employee table and allows you to choose a row for updating. --->
<table border="1">
<cfoutput query="empTable">
<tr>
<td>#firstName#</td><td>#lastName#</td><td>#phone#</td><td><a href="cfupdate.cfm?id=#emp_id#">Edit</a></td>
</tr>
</cfoutput>
</table>

<cfif isDefined("url.id")>
<cfquery name="phoneQuery" datasource="cfsnippets">
select * from employees where emp_id=#url.id#
</cfquery>
<!--- This code displays the row to edit for update --->
<cfoutput query="phoneQuery">
<form action="cfupdate.cfm" method="post">
#phoneQuery.firstName# #phoneQuery.lastName# <input name="phone" type="text" value="#phone#" size="12"> <input type="submit" value="Update">
<input name="emp_id" type="hidden" value="#emp_id#"><!--- The emp_id is passed as a hidden field to be used as a primary key in the CFUPDATE --->
</form>
</cfoutput>
</cfif>
No screen name said on Dec 13, 2004 at 10:30 AM :
This is just FYI. You might get an error code that looks like this

[Macromedia][SQLServer JDBC Driver]Invalid parameter binding(s).

I got this error code on the cfupdate tag with a listed formfield. The error has an error, if you can imagine.

on the debug info I noticed that it was trying to find a column named TYPETITLEABSTRACT.= (param 4) Im my table those are 3 columns. The actual problem is that I have a column (none of the 3) specified in the form fields that is not in the table. However the error does not tell you that. You might be wasting your time trying to find out why CF is trying to combine those fields. It as simple as making sure the fields are available.
yunfengwu@yahoo.com said on Mar 23, 2005 at 9:08 AM :
It did happen on some my servers, all the "CFUPDATE" got the same error "[Macromedia][Oracle JDBC Driver]Invalid parameter binding(s).", and this "CFUPDATE" can work on other servers.

 

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-c19.htm