Error in Updating Query

[jenmerr]

Hi

I am getting this error message below from this code:

<CFQUERY NAME="updateText" DATASOURCE="#APPLICATION.dsn#" USERNAME="#APPLICATION.DB_UserName#" PASSWORD="#APPLICATION.DB_Password#" MAXROWS="1">
UPDATE #FORM.tName#
SET
Title = '#FORM.Title#', Content = '#FORM.content#', timeStmp = #CreateODBCDateTime(Now())#, modBy = #SESSION.userDet#
WHERE ID = #FORM.ID#
</CFQUERY>


-------------------------------------------------
Error Executing Database Query.
Syntax error (missing operator) in query expression 'text goes here'.

SET
Title = '#FORM.Title#', Content = '#FORM.content#', timeStmp = #CreateODBCDateTime(Now())#, modBy = #SESSION.userDet#
WHERE ID = #FORM.ID#</CFQUERY>

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

I really dont know why this wont work? It works for other sites that i have

Jennifer

 

[r937]

instead of CFQUERY, do this --

<CFOUTPUT>
UPDATE #FORM.tName#
SET
Title = '#FORM.Title#', Content = '#FORM.content#', timeStmp = #CreateODBCDateTime(Now())#, modBy = #SESSION.userDet#
WHERE ID = #FORM.ID#
</CFOUTPUT>

and let us see the query that you are passing to the database



r937.com | rudy.ca

 

[cfStarlight]

This has nothing to do with your error, but using form parameters in a query that way is a sql injection risk. So I hope you're scrubbing form parameters and/or using a db that doesn't allow multiple statements per query.

 

[jenmerr]

Hi

Tried CFOUTPUT and doesnt give the error i have been getting but the data i insert is not updated in the database?

 

[jenmerr]

I changed the query slightly to this:

<CFQUERY NAME="updateText" DATASOURCE="#APPLICATION.dsn#" MAXROWS="1">

UPDATE #form.tname#
SET
title = '#FORM.title#', content = '#FORM.content#', timestmp = #CreateODBCDateTime(Now())#, modBy = '#SESSION.userdet#'
WHERE id = #FORM.id#

</CFQUERY>

Now gives me a different Error

____________________________________

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

The error occurred in content_action.cfm: line 23

21 : SET
22 : title = '#FORM.title#', content = '#FORM.content#', timestmp = #CreateODBCDateTime(Now())#, modBy = '#SESSION.userdet#'
23 : WHERE id = #FORM.id#</CFQUERY>

____________________________________

 

[r937]

Tried CFOUTPUT and doesnt give the error i have been getting but the data i insert is not updated in the database?

heh, good one ;-)


the purpose of doing that was so that you could then copy and paste the actual query (with all the coldfusion tags evaluated) so that we could see the SQL that was causing the error

i'm betting you forgot to initialize form.tname

r937.com | rudy.ca

 

[jenmerr]

Hi
When you say initialize do you mean this
<cfparam name="#form.tname#" default="">?

My Datasource is MS Access with Unicode becuase i have a character set problem when i use MS Access in Coldfusion.

From the Coldfusion Documentaion it says
"Inserts and updates fail with the error "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data." This occurs because the data fails to truncate to the maximum column size. "

It doesnt give an answer to how you can get over this problem.

this is such a pain..

 

[r937]

could we please see the result of putting CFOUTPUT tags around the query, i.e. see the actual SQL

r937.com | rudy.ca

 

[jenmerr]

This is the code:

<cfoutput>
UPDATE #form.tname#
SET
title = '#FORM.title#', content = '#FORM.content#', timestmp = #CreateODBCDateTime(Now())#, modBy = '#SESSION.userdet#'
WHERE id = #FORM.id#
</cfoutput>


This is what is displayed when run:


_________________________________


UPDATE WhatDoWeDo SET title = 'What We Do', content = '
Our mission is -

'Wexford County Enteprise Boards mission is to generate sustainable jobs through support for micro-enterprise, to improve business skills and to create an enterprise culture in County Wexford within the framework of our Enterprise Action Plan'

The Wexford County Enterprise Board offers support to start ups and small business in a number of ways. These supports inlcude Grant Aid, training programmes, advice and information through our mentor programme.

To date (Sept 2006), the Board has approved grant assistance for over 800 projects representing a financial commitment of over €8 million. This in turn has helped to create and sustain over 1,200 full-time jobs and 400 part-time jobs in local businesses.

Over 4,000 clients have availed of our range of business supports such as management development programmes, mentoring and other training programmes.

Wexford County Enterprise Board is part of a network of 35 Enterprise Boards, operating in very county in Ireland. Ciy Enterprise Boards are located in Dublin, Cork, Limerick and Waterford.

To find out more about the National Enterprise Board Network check out

http://www.enterpriseboards.ie

', timestmp = {ts '2007-11-23 12:46:45'}, modBy = 'WebPublish Web IT [Wexford County Enterprise Board]' WHERE id = 1

_________________________________


The reason why i didnt have an output before was becuase i had <CFLOCATION url="content_view.cfm?leafID=#FORM.leafID#">
so it was just bring me back to the view page.

 

[r937]

okay, thanks

it looks like your table name did get set properly

unfortunately i cannot see anything wrong with that query, it should work

r937.com | rudy.ca

 

[jenmerr]

hi

Thats why i mentioned the MS Access Unicode datasourse

I have other websites that use this exact same code and works no problem, it's just this database has to have Access with Unicode to solve a character set problem.

 

[cfStarlight]

You should try using cfqueryparam. Untested, but change the cfsqltypes to match your columns if needed

And again using form variables that way is a sql injection risk (ie someone could submit code that could harm your database)

<CFQUERY NAME="updateText" DATASOURCE="#APPLICATION.dsn#" USERNAME="#APPLICATION.DB_UserName#" PASSWORD="#APPLICATION.DB_Password#" MAXROWS="1">
UPDATE #FORM.tName#
SET  	Title = <cfqueryparam value="#FORM.Title#" cfsqltype="cf_sql_varchar">, 
	Content = <cfqueryparam value="#FORM.content#" cfsqltype="cf_sql_varchar">, 	timeStmp = <cfqueryparam value="#CreateODBCDateTime(Now())#" 	cfsqltype="cf_sql_timestamp">, 
	modBy = <cfqueryparam value="#SESSION.userDet#" cfsqltype="cf_sql_integer">
WHERE ID = <cfqueryparam value="#FORM.ID#" cfsqltype="cf_sql_integer">
</CFQUERY>