?
Solved

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

Posted on 2006-10-26
8
Medium Priority
?
721 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 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