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.
austintreAsked:
Who is Participating?
 
trailblazzyr55Connect With a Mentor Commented:
with your original query, I'm willing to bet it was having the error here....


<CFQUERY NAME="CHUpdate" DATASOURCE="inout">
  UPDATE users
     SET Name='#Form.Name#',
         in='#Form.in#',    <!--- with the use of "in" it's thinking it's part of a statement rather than a column name --->
         out='#Form.out#',
          destination='#Form.destination#'
     WHERE ID=#val(URL.ID)#
</CFQUERY>

you'd see IN used normally in a where clause example: WHERE id IN (1,2,3)
to get around column names such as "in" you would put brackets around it example [in] so it knows it's not a function of the statement.
0
 
gdemariaCommented:

 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?
0
 
austintreAuthor Commented:
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
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
austintreAuthor Commented:
Access Database
0
 
trailblazzyr55Commented:
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".
0
 
austintreAuthor Commented:
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#&amp;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>
0
 
austintreAuthor Commented:
You were correct.  It was because I had named a field the same as a SQL syntax word.  

Thanks for your help

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

thanks and best regards,
~trail
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.