what does this query error mean? 1

[leadman]

I moved this query from one template to another (copy and paste) and now it doesnt work. I dont understand the error message either (it mentions a date?):

=============CODE BEGIN===============

<cfquery name=&quot;checkshipping&quot; datasource=&quot;mydata&quot; maxrows=&quot;1&quot;>
SELECT * FROM shipping
WHERE sID IN (SELECT frn_shipping_id FROM orders WHERE orderid = #form.orderid#)
</cfquery>
==============CODE END================

=============ERROR BEGIN===============

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'orderid = #form.orderid#'.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (2:1) to (2:77).
=============ERROR END===============

 

[Silvano]

it means that in the template that you copied the query, the &quot;form.orderid&quot; variable is not accessible; to define the variable without submitting the acctual form you can use this at the top of the template:

<cfparam name=&quot;form.orderid&quot; default=&quot;&quot;> Sylvano
dsylvano@hotmail.com

 

[leadman]

ive triple checked this one though - the form that leads to this template is passing a hidden variable called &quot;orderid&quot; and that teplate has access to the value (im having it appear on the page to make sure) !!! arrg

 

[Silvano]

if the 'form.orderid' value that is passed to the template is blank, it is possible that this is the reason for generating the error in the expression 'orderid = #form.orderid#' of the query;

just for the test purposes, use this before the acctual query:

<cfset form.orderid = &quot;testing&quot;> Sylvano
dsylvano@hotmail.com

 

[leadman]

okay - i put in the cfset line before the query - now I get the following error (i have gone over all field referenced with my database open and checked for spelling and case, etc - cant figure this one out):

============BEGIN ERROR EXAMPLE=============
ODBC Error Code = 07001 (Wrong number of parameters)

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly.

 

[Silvano]

in the sql u are using this:

...WHERE sID IN (SELECT frn_shipping_id...

try replacing 'IN' with '=';

also, make sure that 'orderid' is Numeric DataType or use single quotes with form.orderid (e.g.: '#form.orderid#') Sylvano
dsylvano@hotmail.com

 

[leadman]

orderid is definitely numeric (autonumber) and instead of using a sub query, i split them out and first ran this query (which used to be the sub query):
================BEGING CODE=================
<cfquery name=&quot;getsID&quot; datasource=&quot;mydata&quot;>
SELECT frn_shipping_id FROM orders WHERE orderid = #form.orderid#
</cfquery>
================END CODE=================
and it caused the same error - frn_shipping_id and orderid are definitely in the orders table and are both numeric (long integer) - could this error be from something else?

 

[Silvano]

try displaying the form.orderid BEFORE executing the query itself (make sure that it is not blank) Sylvano
dsylvano@hotmail.com

 

[Silvano]

and for the testing purposes, try removing the maxRows attribute or set it as '-1' Sylvano
dsylvano@hotmail.com

 

[leadman]

aha- Okay, i think some progress has been made - remember when you had me put in <cfset form.orderid = &quot;testing&quot;> just for test purposes, when i put &quot;1&quot; in instead of &quot;testing&quot; the template worked. So it seems your first instincts were correct and form.orderid isnt being recieved from the preceeding template which doesnt make sense because on the preceeding template i am displaying #orderid# at the top of the page (inside a <CFOUTPUT>) and it is the correct id number, then at the bottom of that same page (in the form) i have this line:

<input type=&quot;hidden&quot; name=&quot;orderid&quot; value=&quot;#orderid#&quot;>

perhaps i need <cfoutput> tags around this hidden element?

 

[Silvano]

you need <cfoutput> tags where ever and when ever the cf variable is used;

two tips:
1. to see what is the value of the 'hidden field (again, just for testing purposes), do this:
<input type=&quot;text&quot; name=&quot;orderid&quot; value=&quot;#orderid#&quot;>;
2. you can use <cfoutput> like this:
<input type=&quot;text&quot; name=&quot;orderid&quot;
value=&quot;<cfoutput>#orderid#</cfoutput>&quot;>; Sylvano
dsylvano@hotmail.com

 

[leadman]

Thank you so much - ive heard conflicting things about when to use and not use cfoutput - i think ill use them more from now on