Solved

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

Posted on 2006-10-26
8
706 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

691 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