• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

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>
0
sglee
Asked:
sglee
  • 7
  • 6
  • 6
2 Solutions
 
_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
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.

 
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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 7
  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now