EasyCFM.COM ColdFusion Forums / ColdFusion Applications / Can anyone tell me what's wrong with this query?

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Can anyone tell me what's wrong with this query?

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

raym444
08-25-2006 @ 2:51 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 14
Joined: Apr 2006

I'm getting this error when processing an update query:

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.  
  
The error occurred in E:\contractor\process_slidingscale.cfm: line 22

20 : ScaleMaximum= "#variables.ScaleMaximum#",
21 : MarkupPercentage= "#variables.MarkupPercentage#",
22 : WHERE ScaleID= #variables.ScaleID#
23 : </cfquery>
24 :    

------------------------------------------------------

SQL
UPDATE tblSlidingScale
SET ScaleMinumum= "0.00", ScaleMaximum= "100.00", MarkupPercentage= "0.4", WHERE ScaleID= 1

DATASOURCE   contractor_db
VENDORERRORCODE   -3503
SQLSTATE   42000

Please try the following:
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.

Browser   Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)
Remote Address   127.0.0.1
Referrer   http://localhost/contractor/slidingscale.cfm?
Date/Time   25-Aug-06 02:23 PM

Stack Trace (click to expand)


Here is my page code:

<cfloop index="LoopCount" from=1 to=#Val(form.ScaleCount)#>
   <cfset variables.ScaleID="form.ScaleID#Evaluate(LoopCount)#">
   <cfset variables.ScaleID="#Evaluate(variables.ScaleID)#">
   <cfset variables.ScaleMinumum="form.ScaleMinumum#Evaluate(LoopCount)#">
   <cfset variables.ScaleMinumum="#Evaluate(variables.ScaleMinumum)#">
   <cfset variables.ScaleMinumum=ReReplace(variables.ScaleMinumum, "[^0-9.]", "", "ALL")>
   <cfset variables.ScaleMaximum="form.ScaleMaximum#Evaluate(LoopCount)#">
   <cfset variables.ScaleMaximum="#Evaluate(variables.ScaleMaximum)#">
   <cfset variables.ScaleMaximum=ReReplace(variables.ScaleMaximum, "[^0-9.]", "", "ALL")>
   <cfset variables.MarkupPercentage="form.MarkupPercentage#Evaluate(LoopCount)#">
   <cfset variables.MarkupPercentage="#Evaluate(variables.MarkupPercentage)#">
   <cfset variables.MarkupPercentage=ReReplace(variables.MarkupPercentage, "[^0-9.]", "", "ALL")>
   <cfset variables.MarkupPercentage="#(variables.MarkupPercentage/100)#">
  
   <cfquery name="qUpdateSlidingScale" datasource="contractor_db">
      UPDATE tblSlidingScale
      SET ScaleMinumum= "#variables.ScaleMinumum#",
             ScaleMaximum= "#variables.ScaleMaximum#",
      MarkupPercentage= "#variables.MarkupPercentage#",
            WHERE ScaleID= #variables.ScaleID#
   </cfquery>

</cfloop>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<InvalidTag http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Markup Percentages Updated</title>
<style type="text/css">
<!--
.style2 {
     font-size: 18px;
     font-weight: bold;
     color: #000066;
}
-->
</style>
</head>

<body>

  <div align="center"><span class="style2">
  Markup percentages have been successfully updated.
  </span></div>
<div align="center">
<br><br>
<span class="style2">Click <a href="slidingscale.cfm">here</a> to view your changes or <a href="mainswitchboard.cfm">here</a> to return to the main switchboard.</span>
</div>

</body>
</html>

JJfutbol
08-25-2006 @ 2:56 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1250
Joined: Nov 2004

Look very very closely at line 21. Wink  What character do you see there at the very end?? Should it be there right before your WHERE statement?? Once you remove it let us know if you are still having trouble.

----------------------------------------------------
Some free CF applications available at my site, such as the popular CFC Validator www.javier-julio.com My new site design is up!! Let me know what you all think! Working on a fully accessible Forum, which validates as full CSS and XHTML 1.0 Strict.

raym444
08-25-2006 @ 2:59 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 14
Joined: Apr 2006

Got it, thanks. That's what I get for cutting and pasting lines to save time.  LOL

raym444
08-25-2006 @ 3:03 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 14
Joined: Apr 2006

Oh great, now i'M getting this message:

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Invalid bracketing of name '[0.00]'.  
  
The error occurred in E:\contractor\process_slidingscale.cfm: line 20

18 : ScaleMaximum= "#variables.ScaleMaximum#",
19 : MarkupPercentage= "#variables.MarkupPercentage#"
20 : WHERE ScaleID= #variables.ScaleID#
21 : </cfquery>
22 :  

------------------------------------------------------

SQL    UPDATE tblSlidingScales SET ScaleMinumum= "0.00", ScaleMaximum= "100.00", MarkupPercentage= "0.5" WHERE ScaleID= 1  
DATASOURCE   contractor_db
VENDORERRORCODE   -1002
SQLSTATE   42000

Please try the following:
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.

Browser   Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)
Remote Address   127.0.0.1
Referrer   http://localhost/contractor/slidingscale.cfm?
Date/Time   25-Aug-06 02:58 PM

Stack Trace (click to expand)  


I've never even seen this error before.

JJfutbol
08-25-2006 @ 3:12 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1250
Joined: Nov 2004

lol Don't worry mate I have had that issue so many times. I'm a victim of copy and paste. Smile  You know I can't believe how slow I am today. Although I can say the same of you too eh! Wink  Take a look at whats wrong:

18 : ScaleMaximum= "#variables.ScaleMaximum#",
19 : MarkupPercentage= "#variables.MarkupPercentage#"

Why the double quotes?? Wink  SQL won't be happy with that, it'll throw an error so you'll need to replace it with single quotes.

[edit]
Don't forget, if its not a string field there is no need to put single quotes there. Not sure how you are storing those decimals in your database but just wanted to make sure I pointed that out.

----------------------------------------------------
Some free CF applications available at my site, such as the popular CFC Validator www.javier-julio.com My new site design is up!! Let me know what you all think! Working on a fully accessible Forum, which validates as full CSS and XHTML 1.0 Strict.

This message was edited by JJfutbol on 8-25-06 @ 3:13 PM


Website Designed and Developed by Pablo Varando.