asked on

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>

_agx_

I don't see anything wrong with it. Is that the exact code you're using?

sglee

ASKER

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.

gdemaria

You are missing the end of form tag...

</FORM>

That shouldn't cause that error, but it's the only issue I'm seeing..

sglee

ASKER

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

gdemaria

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

gdemaria

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

gdemaria

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.

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sglee

ASKER

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

_agx_

Possibly. That happens sometimes. Which db are you using?

SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sglee

ASKER

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.

sglee

ASKER

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

_agx_

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

_agx_

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.

_agx_

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

gdemaria

I think splitting is the right thing to do, we were all on the same page :)

Glad it worked out sglee..

sglee

ASKER

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.

_agx_

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.