I keep on getting an Error Executing Database Query

Page 1 of 1

1 Replies - 1236 Views - Last Post: 06 February 2012 - 07:04 AM Rate Topic: -----

Topic Sponsor:

#1 epidemik  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 14-June 08

I keep on getting an Error Executing Database Query

Posted 05 February 2012 - 10:09 PM

I have ran into and error below:

Error Executing Database Query.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order, place, compID)
VALUES ('test', 'karma', '1', 'place', 'compID')' at line 1

The error occurred in D:/home/rappinlounge.com/wwwroot/post/post_banner.cfm: line 23
21 :
22 :
23 : <cfquery datasource="#dsn#" name="insert">
24 : INSERT INTO banners (url, image, order, place, compID)
25 : VALUES ('test', 'karma', '1', 'place', 'compID')

I have no idea what I did wrong. Been at it all day any help

here is my code:

This is my process part.

<cfset UploadFolder="#ExpandPath( '..\images\banners\' )#">
<cfparam default="false" name="edit">
<cfparam default="null" name="tags">


<cfoutput>  
<cfif DirectoryExists(UploadFolder)>  
  <cfif IsDefined("Form.UploadFile") AND Form.UploadFile NEQ "">  
    
             <cffile   
                 action="upload"   
                 filefield="UploadFile"   
                 destination="#UploadFolder#"   
                 nameconflict="overwrite"  
                 accept="image/*"  
                 >  
                 File uploaded successfully!  
                 <br />  
                 Uploaded file: #cffile.ClientFile#  



<cfquery datasource="#dsn#">
INSERT INTO banners (url, image, order, place, compID)
VALUES ('test', 'karma', '1', 'place', 'compID') 
</cfquery>




Your Post Has been successful
       
      
      
     <cfelse>  
         Select a file first!          
     </cfif>  
<cfelse>  
  Upload Directory not exists      
</cfif>  
</cfoutput>


The form code:



<script language="Javascript" type="text/javascript" src="wysiwyg.js">
</script>

<cfif session.logged_in eq 0>
	<cflocation url="../index.cfm" addtoken="no">
</cfif>






<cfif session.user_level neq 'admin'>
	<cflocation url="../index.cfm" addtoken="no">
</cfif>

<cfparam default="0" name="perid">
<cfparam default="false" name="edit">

<cfif perid NEQ 0>
   <cfset edit = 'true'></cfif>
<cfoutput>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Post</title>
</head>

<body>
<form method="post" enctype="multipart/form-data" action="post_banner.cfm">
<table>
	<tr><td width="167">CompID</td><td width="336"><input type="text" name="compID" /></td></tr>
   
    <tr><td>Please upload an image: </td><td><input type="file" name="UploadFile"></td></tr>
    <tr><td>Url:</td><td>
<input name="url" type="text" />
    <tr><td></td>
   
    <tr><td><input type="hidden" name="place" value="home" />
    <input type="hidden" name="order" value="1" />

    </td><td align="right"><input type="submit" value="POST" /></td></tr>
</table>


</form>
</body>
</html>
</cfoutput>


Is This A Good Question/Topic? 0
  • +

Replies To: I keep on getting an Error Executing Database Query

#2 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1102
  • View blog
  • Posts: 2,607
  • Joined: 13-January 08

Re: I keep on getting an Error Executing Database Query

Posted 06 February 2012 - 07:04 AM

At first glance, all appears in order with your query. This is a good time though to discuss how you go about troubleshooting SQL issues given how much database interaction there is in your typical CF app.

So, from the error message you're using MySQL. Do you have a database management app and if so which one are you using? For instance, there are a few database apps you can use with MySQL like TOAD, Navicat and others. The reason I ask is that when you get an error like this and you look at your code and all appears well you need to troubleshoot this to discover the issue. In this case, what's breaking isn't your CF code as the error message is quite clear it's a SQL issue ("You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near"). If you didn't know, that error is being generated by the database you're using and is passed back to CF which spits out the exception to you on the page.

So, because it's a database error you're going to want to try running that query in something like TOAD or Navicat and see what you get. I suspect it could be one of two things: it could be a data type mismatch error or you could be running afoul of a MySQL reserved word. A data mismatch error simply means you're trying to insert the wrong kind of data into the database. Something like inserting a string into a field designated as an integer data type. The other possibility is that one of your column names is a reserved word and MySQL is choking on that. It's less common but when it happens the error message the database sends back is kind of generic and doesn't immediately clue you in that you've used a reserved word. In this case, one of your column names is indeed a reserved word. Now, I'm not saying that's the cause of your issue but it could be. That's why you run the query directly on the database via a database management app and see what you get.

Finally, I get that the code you posted for the query is merely an example and that you'll be replacing the VALUES with variable values. When you do that, remember to use CFQUERYPARAM. It protects your app, is good practice, will impart a very slight performance boost and gives you a better error message when you inadvertently attempt a mismatched data type insert.
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1