problem with UPDATE

[OccasionalCoder]

Hi:
I used the following to do an update on my database:

<cfquery name="update_data" datasource="#db#" username="#un#" password="#pw#">
update fellows
set
first_name='#form.first_name#',
mi='#form.mi#',
last_name='#form.last_name#',
nee='#form.nee#',
photo='#form.photo#',
agency='#form.agency#',
abbr='#form.abbr#',
division='#form.division#',
unit='#form.unit#',
school='#form.school#',
program='#form.program#',
synopsis='#form.synopsis#',
website='#form.website#',
email='#form.email#',
<cfif form.start_year is 'no date' or form.start_year is ''>
start_year=null,
<cfelse>
start_year='#dateformat(form.start_year, "mm/yyyy")#',
</cfif>
<cfif form.end_year is 'no date' or form.end_year is ''>
end_year=null,
<cfelse>
end_year='#dateformat(form.end_year, "mm/yyyy")#',
</cfif>
<cfif form.twostart_year is 'no date' or form.twostart_year is ''>
twostart_year=null,
<cfelse>
twostart_year='#dateformat(form.twostart_year, "mm/yyyy")#',
</cfif>
<cfif form.twoend_year is 'no date' or form.twoend_year is ''>
twoend_year=null,
<cfelse>
twoend_year='#dateformat(form.twoend_year, "mm/yyyy")#',
</cfif>
note='#form.note#',
status='#form.status#'
where entry_id=#form.entry_id#
</cfquery>

It worked ok, then I added the "note='#form.note#',"
line (4 up from the bottom), now I get a UPDATE syntax error message.

I take out "note='#form.note#'," and it works ok again.

I can't see anything wrong with the "form.note" line and am totally puzzled.

Any help would be greatly appreciated.

Thanks

 

[ecobb]

What does the error say?


Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[r937]

and the error message is .... ?

r937.com | rudy.ca

 

[OccasionalCoder]

Here's what it says:

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

The error occurred in C:\Inetpub\

http://wwwroot\consortium\admin\edit\edit3.cfm:

line 40

38 : note='#form.note#',
39 : status='#form.status#'
40 : where entry_id=#form.entry_id#
41 : </cfquery>
42 :

 

[r937]

thanks, i hoped it would be more informative than it is



the table does have a column named note, right?

r937.com | rudy.ca

 

[FALCONSEYE]

could you compare your note field from the DB table to form.note to make sure you are passing the same types.
i.e. varchar, nchar
you can also try

<cfif IsDefined('form.note'>
note='#form.note#',
<cfelse>
note=NULL,
</cfif>


hope it helps

http://www.jetansolutions.com

 

[OccasionalCoder]

That is correct...It's an access table named fellows and the datatype is memo.

 

[FALCONSEYE]

hmm instead of having datatype memo, why not use varchar(1000)
that will work perfect

http://www.jetansolutions.com

 

[r937]

because in access the equivalent of varchar is called text but it has a limit of 255

r937.com | rudy.ca

 

[TruthInSatire]

thanks, i hoped it would be more informative than it is

no kidding

38 : note='#form.note#',
39 : status='#form.status#'
40 : where entry_id=#form.entry_id#
41 : </cfquery>
42 :

CF shows the last few lines of the cfquery when calling an error like this so the error is most likely NOT in the above lines. please paste the ENTIRE error including the generated sql.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[OccasionalCoder]

Here's the entire error message:

____________

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

The error occurred in C:\Inetpub\

http://wwwroot\consortium\admin\edit\edit3.cfm:

line 40

38 : note='#form.note#',
39 : status='#form.status#'
40 : where entry_id=#form.entry_id#
41 : </cfquery>
42 :



--------------------------------------------------------------------------------

SQL update fellows set first_name='Jason', mi='', last_name='Kring', nee='', photo='Jason-Kring.jpg', agency='U.S. Army Research Institute for the Behavioral & Social Sciences', abbr='ARI', division='Simulator Systems Research Unit', unit='', school='', program='', synopsis='Past work evaluated team performance in a virtual environment for local vs. distributed teams, and the role of instructional features in VE-based training. Current work focused on the effect of video game experience on interactions with digital display systems.', website='', email='Jason.kring@us.army.mil', start_year='05/1999', end_year='07/2005', twostart_year=null, twoend_year=null, note='this is a test note', status='current' where entry_id=74
DATASOURCE consortium
VENDORERRORCODE -3503
SQLSTATE 42000

_____________

Thanks

 

[r937]

all the fields which you are setting to NULL actually allow nulls, right? like, twostart_year and twoend_year?

r937.com | rudy.ca

 

[TruthInSatire]

If I were you i'd set the DB NOT to accept '' and only allow NULL. If it were the '' causing the problems you should probably get a 'field does not allow zero length strings' error. same with the null. What format is your date field?

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[OccasionalCoder]

Everything worked in this code until I added the "note" line. The dates (or nulls) were updated as they should have been.

If I comment out the "note" line and the textarea field for the "note" on the form, all works well. When I un-comment those lines, I get the error.

To answer your question, the date field accepts NULL. The date fields are date/time (short date). I input the date in a mm,yyyy format. When I use my edit template, it pulls the date from the database correctly.

Thanks again for your help.

 

[OccasionalCoder]

The form is used is an edit form. The values come from the database from a simple query, i.e. select * from fellows where entry_id=entry_id). Here's the code from the form template:

<form action="edit3.cfm" method="post">
<input type="hidden" name="entry_id" value="#entry_id#">

First Name: <input type="text" name="first_name" size="15" maxlength="30" value="#first_name#"> MI: <input type="text" name="mi" size="5" maxlength="5" value="#mi#"> Last Name: <input type="text" name="last_name" size="15" maxlength="30" value="#last_name#"> Nee: <input type="text" name="nee" size="15" maxlength="30" value="#nee#"><br>
Photo: <input type="text" name="photo" size="40" maxlength="150" value="#photo#"><br>
Agency: <input type="text" name="agency" size="40" maxlength="250" value="#agency#"> Agency Abbr: <input type="text" name="abbr" size="10" maxlength="15" value="#abbr#"><br>
Division: <input type="text" name="division" size="40" maxlength="250" value="#division#"><br>
Unit: <input type="text" name="unit" size="40" maxlength="250" value="#unit#"><br>
School: <textarea cols="40" rows="2" name="school">#school#</textarea><br>
Program of Study: <textarea cols="40" rows="2" name="program">#program#</textarea><br>
Work Synopsis: <textarea cols="40" rows="5" name="synopsis">#synopsis#</textarea><br>
Website: <input type="text" name="website" size="40" maxlength="150" value="#website#"><br>
Email: <input type="text" name="email" size="40" maxlength="150" value="#email#"><br>
Start Year: <input type="text" name="start_year" <cfif len(#start_year#) is 0>
value="No date"><br>
<cfelse>
value="#dateformat(start_year, "mm/yyyy")#"><br></cfif><br>
End Year: <input type="text" name="end_year" <cfif len(#end_year#) is 0>
value="No date"><br>
<cfelse>
value="#dateformat(end_year, "mm/yyyy")#"><br></cfif><br>
2nd Start Year: <input type="text" name="twostart_year" <cfif len(#twostart_year#) is 0>
value="No date"><br>
<cfelse>
value="#dateformat(twostart_year, "mm/yyyy")#"><br></cfif><br>
2nd End Year: <input type="text" name="twoend_year" <cfif len(#twoend_year#) is 0>
value="No date"><br>
<cfelse>
value="#dateformat(twoend_year, "mm/yyyy")#"><br></cfif><br>
Note: <textarea cols="25" rows="4" name="note">#get_data.note#</textarea><br>
Status:

<cfif get_data.status eq "current">
Current: <input type="radio" name="status" value="current" checked>
Past: <input type="radio" name="status" value="past">
<cfelse><br>

Current: <input type="radio" name="status" value="current">
Past: <input type="radio" name="status" value="past" checked>
</cfif><br>


<input type="submit" name="submit"><input type="reset" name="reset">

 

[TruthInSatire]

I didn't see note in the reserved word list, but try changing the field name to something else or change note='#form.note#' to "note"='#form.note#',

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.