Syntax error in UPDATE statement. with datetime field

[dennislongnecker]

Using Coldfusion to update a MS Access Database. In my particular case I am trying to update field that is define in MS Access as date/time. My code is getting:

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

I stripped the code a pretty simple, and still get the error, so I know it is the update to that field... Here is my code with diagnostic stuff in it:

**DEBUG** In the update logic
In the update
<cfoutput>
<cfset field1 = form.left>
<cfset field2 = CREATEODBCDATETIME(Form.Left)>
<cfset field3 = CREATEODBCDATE(Form.Left)>
<br>field1 = #field1#
<br>field2 = #field2#
<br>field3 = #field3#

</cfoutput>

<cfquery name="UpdateMember" datasource="Members">
UPDATE Members
SET
Member = '#Form.Member#',
Left = '#field3#',
LastBrowser = '#Form.LastBrowser#'
WHERE ID = #ID#
</cfquery>

When I run, I see these outputs:

**DEBUG** Form is qwert **DEBUG** In the update logic In the update
field1 = 10/21/2009
field2 = {ts '2009-10-21 00:00:00'}
field3 = {d '2009-10-21'}

I have tried all the combinations of the left

Left = '#field1#',
or this one: Left = '#field2#',
or this one: Left = '#field3#',
or this one: Left = #field1#,
or this one: Left = #field2#,
or this one: Left = #field3#,

And they all get the same error.....any thoughts/suggestions?

 

[r937]

have you tested the SQL directly in Access?

this would be ~way~ more likely to pinpoint the "syntax error"

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[cfSearching]

Left = '#field3#',

1) Do not put quotes around a date/time object. A date/time object is not the same as a string value:

UPDATE TableName
SET    SomeDateColumn = #CREATEODBCDATE(Form.Left)#
.....

2) It is very likely that LEFT is a reserved word. Most databases have a string function by this name. If it is indeed a reserved word in MS Access, either

A) Rename the column (Best)
B) Escape the name with square brackets (in every query where this column is used).

UPDATE TableName
SET    [Left] = #CREATEODBCDATE(Form.Left)#
...etectera....

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

http://cfsearching.blogspot.com/

 

[dennislongnecker]

bingo!

Left was indeed a reserved word. Changed it to LeftDate and got a different error. Took the quotes off and all is well.

Thanks much.