Skip to main content
K
kodemonki
Inspiring
May 12, 2009
Question

Access Query Error

  • May 12, 2009
  • 1 reply
  • 1932 views

The following query works fine in Access (2007), but in CF8 it give me the error: Too few parameters.  Expected 1.  This problem is, I believe, a direct result of eligible_value being retrieved.  If I completely take out "eligible_value as e_eligible," it works fine.  I have added brackets around eligible_value in case it was a keywork for some reason.  I don't think I'm spelling it incorrectly (as it works in Access).  Any suggestions?

<cfquery name="inventory_data" datasource="accdb-remote">
SELECT dlrcode,
   eligible_value as e_eligible,

   Format(return_value, 'Currency') as returnable, IsNull(return_value) as returnable_test, return_value as returnable_value,
   Format(non_genuine, 'Currency') as non_returnable, IsNull(non_genuine) as non_returnable_test, non_genuine as non_returnable_value,
   Format(final_returned_value, 'Currency') as returned_val, IsNull(final_returned_value) as returned_val_test, final_returned_value as returned_val_value,
   iif([final_returned_value] is null,
    iif([return_value] is null,
     iif([non_genuine] is null,
      null,
      Format(non_genuine, 'Currency')
     ),
     iif([non_genuine] is null,
      Format(return_value, 'Currency'),
      Format(return_value + non_genuine, 'Currency')
     )
    ),
    iif([non_genuine] is null,
     Format(final_returned_value,'Currency'),
     Format(final_returned_value + non_genuine,'Currency')
    )
   ) as total
FROM  tdealers
</cfquery>

This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
May 12, 2009

eligible_value is one of the simplest lines in your select clause which means it's the least likely to be the cause of your problem.  To debug a long query like this, I suggest that you arrange it like this.

select 'a' a

<!---

, field1

, field2

, function1()

etc

, subquery()

--->

from yourtable

where 1 = 2

Then run it.  It should return 0 rows.

Then uncomment your select clause one line at a time and keep running it.  Once it crashes, you'll be able to isolate the cause.

K
kodemonkiAuthor
Inspiring
May 12, 2009

I whole-heartedly agree.  If I take that out, it works fine.  If I follow your directions and do:

SELECT     'a' as a

FROM         tdealers

this works fine

SELECT     'a' as a, dlrcode

FROM         tdealers

this also works fine

SELECT     'a' as a, dlrcode, eligible_value as e_eligible

FROM         tdealers

I get: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

cfdump confirms the first two examples executed as expected.

ilssac
ilssac
Inspiring
May 12, 2009

What about:

SELECT 'A' as a, dircode, eligible_value

Is there any chance that Access thinks eligible_value is a function for which it is expecting parameters?

You say the SQL works in access.  Have you tried directly copying the code from the Access 'SQL view' and pasting it into the CFML template?  Sometimes Access needs strange sets of parenthesis or quotes or something.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices