Solved

error executing query message

Posted on 2004-04-02
14
137 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
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 35

Expert Comment

by:mrichmon
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 11

Expert Comment

by:hart
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 11

Expert Comment

by:hart
Comment Utility
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
Comment Utility
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
Comment Utility
finnstone : any success ??
0
 
LVL 12

Expert Comment

by:jyokum
Comment Utility
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
Comment Utility
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
Comment Utility
Closed, no points refunded.
modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now