Solved

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

Posted on 2006-10-26
8
696 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

785 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