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
Solved

error executing query message

Posted on 2004-04-02
14
139 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

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.

Question has a verified solution.

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

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…
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 …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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