Solved

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

Posted on 2006-10-26
8
699 Views
Last Modified: 2013-12-24
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.
0
Comment
Question by:austintre
  • 4
  • 3
8 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 17813730

 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
 

Author Comment

by:austintre
ID: 17813781
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
 

Author Comment

by:austintre
ID: 17813786
Access Database
0
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 17813802
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
 

Author Comment

by:austintre
ID: 17813803
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
 
LVL 20

Accepted Solution

by:
trailblazzyr55 earned 500 total points
ID: 17813823
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
 

Author Comment

by:austintre
ID: 17813859
You were correct.  It was because I had named a field the same as a SQL syntax word.  

Thanks for your help

Cheers
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 17813904
Yeah no prob, glad I could help, those little syntax things will catch ya sometimes...

thanks and best regards,
~trail
0

Featured Post

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question