asked on

Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

So I thought I was getting the hang of this until I came across this annoying error:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (11:1) to (11:44).

Here is what I am trying to do:

<BODY>
<CFQUERY NAME="CHUpdate" DATASOURCE="inout">
UPDATE users
      SET Name='#Form.Name#',
       in='#Form.in#',
       out='#Form.out#',
            destination='#Form.destination#'
      WHERE ID=#URL.ID#
</CFQUERY>
<cflocation url="main.cfm?id=#id#">
</BODY>

What is my problem? I need a quick answer here.

gdemaria

first glance it looks ok. Does the URL.ID have a value?

You may want to wrap it in val() just in case (a good approach for security anyway)

<CFQUERY NAME="CHUpdate" DATASOURCE="inout">
UPDATE users
SET Name='#Form.Name#',
in='#Form.in#',
out='#Form.out#',
destination='#Form.destination#'
WHERE ID=#val(URL.ID)#
</CFQUERY>

If that doesn't work, perhaps you can show us the query from the debug info at the bottom of the page so we can see the variables values...

btw, what database is this?

austintre

ASKER

This isn't the first update of a form I have done, which is why I am at a loss.

Here is the full error since it doesn't look like I typed in something stupid.

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (11:1) to (11:44).

Date/Time: 10/26/06 11:41:25
Browser: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; SV1)
Remote Address: 10.0.44.19
HTTP Referer: http://www.wadafarms.com/apps/main.cfm?id=1
Query String: ID=1&name=Bob

austintre

ASKER

Access Database

trailblazzyr55

try this statement....

<CFQUERY NAME="CHUpdate" DATASOURCE="inout">
UPDATE users
SET [Name] ='#Form.Name#',
[in] ='#Form.in#',
[out] ='#Form.out#',
destination ='#Form.destination#'
WHERE ID=#URL.ID#
</CFQUERY>

when naming you're table columns, try to name them something that isn't a reserved word in SQL syntax such as "IN".

austintre

ASKER

In fact just so you get the whole thing - here is my form page:

<html>
<head>
      <title>Untitled</title>
<CFQUERY datasource="inout" name="counter">
SELECT *
FROM users
</cfquery>
<CFQUERY datasource="inout" name="passthru">
SELECT *
FROM users
WHERE ID=#URL.ID#
</cfquery>
</head>

<body bgcolor="white">

<table>
<TR><TD>Name</td><TD>IN</td><TD>OUT</td><TD>Destination</td></tr>
<CFOUTPUT query="counter">
<tr>
      <TD>#name#</td>
      <FORM ACTION="mainupdate.cfm?ID=#ID#&name=#name#" METHOD="post">
      <INPUT TYPE="hidden" NAME="ID" VALUE="#ID#">
      <INPUT TYPE="hidden" NAME="name" VALUE="#name#">
      <TD>
      <INPUT type="hidden" NAME="in" SIZE="15" VALUE="#in#" tabindex="1">
      <INPUT NAME="in" SIZE="15" VALUE="#in#" tabindex="1" >
      </td>
      <TD>
      <INPUT type="hidden" NAME="out" SIZE="15" VALUE="#out#" tabindex="1">
      <INPUT NAME="out" SIZE="15" VALUE="#out#" tabindex="1" >
      </td>
      <TD>
      <INPUT type="hidden" NAME="destination" SIZE="15" VALUE="#destination#" tabindex="1">
      <INPUT NAME="destination" SIZE="15" VALUE="#destination#" tabindex="1" >
      </td>
      <td>
      <INPUT TYPE="submit" VALUE="Update">
      </td>
</tr>
</CFOUTPUT>
</table>
</body>
</html>

ASKER CERTIFIED SOLUTION

trailblazzyr55

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

austintre

ASKER

You were correct. It was because I had named a field the same as a SQL syntax word.

Thanks for your help

Cheers

trailblazzyr55

Yeah no prob, glad I could help, those little syntax things will catch ya sometimes...

thanks and best regards,
~trail