error executing query message

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>
finnstoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrichmonCommented:
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
finnstoneAuthor Commented:
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
mrichmonCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

hartCommented:
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
hartCommented:
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
jyokumCommented:
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
hartCommented:
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
jyokumCommented:
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
hartCommented:
finnstone : any success ??
0
jyokumCommented:
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
mrichmonCommented:
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
moduloCommented:
Closed, no points refunded.
modulo
Community Support Moderator
Experts Exchange
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.