No value given for one or more required parameters

Hi,

  I am trying to create a drop down menu where a user can select as sort field.
  When I run this .cfm page, I get an error as follows:
--------------------------------------------------------
Error Executing Database Query.  
No value given for one or more required parameters.  
 
The error occurred in E:\webroot\PO2000\POStatus.cfm: line 18
 
16 :       <CFELSEIF #SORTFIELD# IS "">      
17 :             ORDER BY ShipDate
18 :       </CFIF>
19 : </CFQUERY>
20 :

 ----------------------------------------------------------------


**** Here is the CF page *****

<CFparam NAME="SORTFIELD" Default="">
<CFQUERY NAME="GetFields" datasource="PO2000">
      SELECT      PONo, PortETA, ShipDate, DODate, OrderDate
         FROM         PurchaseOrder
      <CFIF #SORTFIELD# IS "PONo">
            ORDER BY PONo
      <CFELSEIF #SORTFIELD# IS "PortETA">
            ORDER BY PortETA
      <CFELSEIF #SORTFIELD# IS "ShipDate">      
            ORDER BY ShipDate
      <CFELSEIF #SORTFIELD# IS "DODate">      
            ORDER BY DODate
      <CFELSEIF #SORTFIELD# IS "">      
            ORDER BY ShipDate
      </CFIF>
</CFQUERY>

<BODY>
<FORM ACTION="POStatus.cfm" METHOD="post">
<FONT color="white" FACE="MS Sans Serif" SIZE=-1>Sort Order:
   <SELECT NAME="SORTFIELD" onChange="this.form.submit()">
         <OPTION VALUE="" selected>Select One
         <OPTION VALUE="PONo">PONo
         <OPTION VALUE="PortETA">PortETA
         <OPTION VALUE="ShipDate">ShipDate
         <OPTION VALUE="DODate">DODate
  </SELECT>
</FONT>
</BODY>
LVL 1
sgleeAsked:
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.

_agx_Commented:
I don't see anything wrong with it. Is that the exact code you're using?
0
sgleeAuthor Commented:
Yes it is pretty much the same code except I took out some fields (from the table) in select statement because there was no error with select statement until I added if sortfield stuff.
0
gdemariaCommented:

You are missing the end of form tag...

 </FORM>


That shouldn't cause that error, but it's the only issue I'm seeing..
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

sgleeAuthor Commented:
gdemaria:
-----------------------
I added </FORM> but same error ...
I included the entire code and error message shot in attached file. I used the same code in another website and it worked fine. I don't know why this CF server refuses to run.

CF-Code-and-Error.doc
0
gdemariaCommented:
I see a couple things that could be wrong, but none specifically go with the error message...



WHERE    Shipdate >= now() - 60

now() is a coldfusion expression, but isn't in #s... unless it happens to also be the date function in your database, this needs to change.  Also, now all database allow you to subtract a value from the date.

where shipdate >= #createODBCdate(dateAdd('d',-60,now())#

or in SQL Server...

where shipDate >= dateAdd(d,-60,getDate())


0
gdemariaCommented:
Also, there is no database column namded PoNo as when referrred in ORDER BY PONo

Things could have changed with newer versions (and depending on your database), but I believe you can't order-by  by using the alias, you need to specify the column name or the position of the column in the list...

ORDER BY [Po ##]

ORDER BY 1

0
gdemariaCommented:
If the error can be reproduced every time, I would remove a block of code and see if the error is gone.  Once it is, put the code back in bit-by-bit to see exactly what which line is causing the issue.
0
_agx_Commented:
> Things could have changed with newer versions (and depending on your database), but
> I believe you can't order-by  by using the alias,

You're right. It depends on the db.  MS SQL allows you to order by an alias, but ones like MS Access don't (though that may have changed too).  Which db are you using?  

> I would remove a block of code and see if the error is gone.

Yep. It's most likely one of things gd mentioned:  the WHERE clause or ORDER'ing by an alias. So I'm guessing one of their last 2 suggestions will do the trick.
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
sgleeAuthor Commented:


When I remove these IF ELSE .. out of the query and just run a query, there is no error.
Please see attached.
So maybe when I copied and pasted the code (IF ... ELSE IF) from my previous CFM page into this CFM page, it might have inserted "invisible" code????
Let me hand type the code line by line this time. I will kepp you posted.

<CFQUERY NAME="GetFields" datasource="PO2000">
      SELECT      [po ##] as PONo, PortETA, [ship date] as ShipDate, [DO Date] as DODate, [Order date] as OrderDate, OrderDeliveredtoWH,
             [Bill Of Lading Number] as BOL, HBL, [Container Number] as ContainerNo, [Vessle Name] as VessleName, WHDeliveryDate,
            [Fgt Chgs] as FgtChgs
         FROM         [Purchase Order Headers]
</CFQUERY>
 CF-Code-and-Error.doc
0
_agx_Commented:
Possibly.  That happens sometimes. Which db are you using?
0
gdemariaCommented:
Take the CFIF logic out of it and just put in a hard coded ORDER BY For testing...  then build up your cfif as you go...

You should try this...

SELECT  [po ##] as PONo, PortETA
    , [ship date] as ShipDate
    , [DO Date] as DODate
    , [Order date] as OrderDate
    , OrderDeliveredtoWH
    , [Bill Of Lading Number] as BOL
    , HBL
    , [Container Number] as ContainerNo
    , [Vessle Name] as VessleName
    , WHDeliveryDate
    , [Fgt Chgs] as FgtChgs
 FROM [Purchase Order Headers]
 ORDER BY PONo

and then try this...

SELECT  [po ##] as PONo, PortETA
    , [ship date] as ShipDate
    , [DO Date] as DODate
    , [Order date] as OrderDate
    , OrderDeliveredtoWH
    , [Bill Of Lading Number] as BOL
    , HBL
    , [Container Number] as ContainerNo
    , [Vessle Name] as VessleName
    , WHDeliveryDate
    , [Fgt Chgs] as FgtChgs
 FROM [Purchase Order Headers]
 ORDER BY [po ##]

IF the last one works, you need to change the order by clauses to use the actual column names..
0
sgleeAuthor Commented:
gdemaria:
---------------
I retyped the text line by line, but the same error...

"IF the last one works, you need to change the order by clauses to use the actual column names.."
YES indeed I had to use ORDER BY [po ##] and IT WORKED!!!

THANK YOU SO MUCH.
0
sgleeAuthor Commented:
_agx_:
------------------

"You're right. It depends on the db.  MS SQL allows you to order by an alias, but ones like MS Access don't (though that may have changed too).  Which db are you using? "

I am using MS ACCESS. Thanks for your suggestion.
0
_agx_Commented:
> IF the last one works, you need to change the order by clauses to use the actual column names..

If their db is Access, that's almost certainly the problem. That's why I keep asking which db type ;-)
0
_agx_Commented:
Posts overlapped...

@sglee - Cool. I was going to say "then use gd's suggestion" but you already figured it out ;)  Glad it's working now.
0
_agx_Commented:
@sglee - BTW: Thanks for the points.. but I didn't provide the answer, gd did ;-) Feel free to reallocate. I wouldn't be at all offended.
0
gdemariaCommented:
I think splitting is the right thing to do, we were all on the same page :)

Glad it worked out sglee..
0
sgleeAuthor Commented:
gdemaria:
---------
Thanks for being so gracious.
I read over entire comments in order again and based on last comment by _agx_:, I was going to re-allocate the points all to you, but I will leave it as it is then.

THANK YOU AGAIN both of you.
0
_agx_Commented:
Thanks guys. I just felt a little bad because it wasn't my suggestion. But wanted to confirm it definitely doesn't work under Access. So you could jump right to using gd's suggestion.  Anyway, glad everything worked out.
0
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
ColdFusion Language

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.