Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

error executing query message

Posted on 2004-04-02
14
Medium Priority
?
144 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
12 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
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 …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

824 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