Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

error executing query message

Posted on 2004-04-02
14
Medium Priority
?
143 Views
Last Modified: 2013-12-24
the below code worked in cf with access db but now it wont work with a mysql db?????

      <cfquery datasource="#Application.DataSource#" username="xxx" password="xxxx" NAME = "GetNewOrderID">
      SELECT MAX(OrderID) as MaxOrderID
      FROM itemlist
      </cfquery>
      
      <cfquery datasource="#Application.DataSource#" username="xx" password="cccc" name="test4">
            UPDATE orderhistory
            SET orderhistory.OrderID = #GetNewOrderID.MaxOrderID#
            WHERE orderhistory.OrderID = 0
            AND orderhistory.itemoid <> 0
      </cfquery>
0
Comment
Question by:finnstone
[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
  • 3
  • +2
14 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 10744515
Have you tried :

<cfquery datasource="#Application.DataSource#" username="xx" password="cccc" name="test4">
          UPDATE orderhistory
          SET orderhistory.OrderID = #GetNewOrderID.MaxOrderID#
          WHERE orderhistory.OrderID = 0
          AND NOT orderhistory.itemoid = 0
     </cfquery>
0
 

Author Comment

by:finnstone
ID: 10744546
well before i did that i just tested this line

#GetNewOrderID.MaxOrderID#

i switched it with 9999

and it worked.

Why cant i reference that line like i had done?
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10744564
You should be able to reference it that way.  Are you sure that you are getting results from the first query?

Try this :

<cfquery datasource="#Application.DataSource#" username="xxx" password="xxxx" NAME ="GetNewOrderID">
     SELECT MAX(OrderID) as MaxOrderID
     FROM itemlist
     </cfquery>

     <cfif GetNewOrderID.RecordCount EQ 0><cfabort showerror="No Records Pulled"></cfif>

     <cfquery datasource="#Application.DataSource#" username="xx" password="cccc" name="test4">
          UPDATE orderhistory
          SET orderhistory.OrderID = #GetNewOrderID.MaxOrderID#
          WHERE orderhistory.OrderID = 0
          AND orderhistory.itemoid <> 0
     </cfquery>

Also try removing the space between name= and the "GetNewOrderID">
0
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
LVL 11

Expert Comment

by:hart
ID: 10746588
try this , i think the max returned is null..
     <cfquery datasource="#Application.DataSource#" username="xxx" password="xxxx" NAME = "GetNewOrderID">
     SELECT isNull(MAX(OrderID),1) as MaxOrderID
     FROM itemlist
     </cfquery>
     
     <cfquery datasource="#Application.DataSource#" username="xx" password="cccc" name="test4">
          UPDATE orderhistory
          SET orderhistory.OrderID = #GetNewOrderID.MaxOrderID#
          WHERE orderhistory.OrderID = 0
          AND orderhistory.itemoid <> 0
     </cfquery>
0
 
LVL 11

Expert Comment

by:hart
ID: 10746640
if isNull doesn't work that way in mysql then do this

<cfquery datasource="#Application.DataSource#" username="xxx" password="xxxx" NAME = "GetNewOrderID">
     SELECT MAX(OrderID) as nMaxOrderID
     FROM itemlist
     </cfquery>

<cfif GetNewOrderID.Recordcount eq 0>
   <cfset nMaxOrderID = 1>
<cfelse>
   <cfset nMaxOrderID = GetNewOrderID.nMaxOrderID>
</cfif>

<cfquery datasource="#Application.DataSource#" username="xx" password="cccc" name="test4">
          UPDATE orderhistory
          SET orderhistory.OrderID = <CFQUERYPARAM CFSQLTYPE="CF_SQL_NUMERIC" VALUE="#nMaxOrderID#">
          WHERE orderhistory.OrderID = 0
          AND orderhistory.itemoid <> 0
 </cfquery>

PS: i would suggest that you don't do it this way, could u explain your requirement and why you are updating the table and not inserting.

Regards
Hart
0
 
LVL 12

Expert Comment

by:jyokum
ID: 10750934
could you just do it in one query?

<cfquery datasource="#Application.DataSource#" username="xx" password="cccc" name="test4">
UPDATE orderhistory
SET orderhistory.OrderID = (SELECT MAX(OrderID) as MaxOrderID FROM itemlist)
WHERE orderhistory.OrderID = 0
AND orderhistory.itemoid <> 0
</cfquery>
0
 
LVL 11

Expert Comment

by:hart
ID: 10755731
jyokum : again if the max returned is null, won't the query give an error.
the idea is good if u incorporate the isnull into it :-)

Regards
Hart
0
 
LVL 12

Expert Comment

by:jyokum
ID: 10757283
I don't know that it would give an error but it would set the value to null... which may or may not be a good thing
0
 
LVL 11

Expert Comment

by:hart
ID: 10774841
finnstone : any success ??
0
 
LVL 12

Expert Comment

by:jyokum
ID: 10878209
I'd like to see an update from finnstone before a refund is given... several suggestions have been provided and there has been no response.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10879472
I agree with jyokum - a lot of suggestions were made and there was never any responses from finnstone.

The experts should be given a chance to respond and further I do not see why it would be PAQ/refund since finnstone never said that it was sovled nor posted any solution...
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 10911394
Closed, no points refunded.
modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

661 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