7 Replies Latest reply: May 12, 2009 2:08 PM by kodemonki RSS

    Access Query Error

    kodemonki Community Member
    kodemonki May 12, 2009 5:47 AM

      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>

      • 1392 Views
      • Tags:
        • 1. Re: Access Query Error
          Dan Bracuk Community Member
          Dan Bracuk May 12, 2009 6:04 AM (in response to kodemonki)

          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.

          • 2. Re: Access Query Error
            kodemonki Community Member
            kodemonki May 12, 2009 6:23 AM (in response to Dan Bracuk)

            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.

            • 3. Re: Access Query Error
              ilssac Community Member
              ilssac May 12, 2009 6:32 AM (in response to kodemonki)

              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.

              • 4. Re: Access Query Error
                kodemonki Community Member
                kodemonki May 12, 2009 6:58 AM (in response to ilssac)

                I've been pasting it back and forth from CF to Access, thinking exactly what you were.  I even put [] around it just in case.  This is really the first time I've used Access (I regularly use Oracle) and our Access guy has no idea and thinks it's a CF thing.  eligible_value is set up as Currency, just like many other fields that I'm pulling . . .

                • 5. Re: Access Query Error
                  ilssac Community Member
                  ilssac May 12, 2009 7:21 AM (in response to kodemonki)

                  It is unlikely to be a ColdFusion thing...

                   

                  All ColdFusion does is package up the SQL text and send it to the database throught the database driver.  The database is generating and throwing the error....

                   

                  But you may want to look at the Database driver... they have been know to cause problems.  Make sure you try both of the Access drivers (with and without unicode) they are known to behave very differently.  If this does not change anything you may want to see if other drivers are available and|or try to use the odbc driver aka odbc to jdbc bridge.  This latter would require one to set up a windows odbc driver to the access database and then a ColdFusion datasource using the odbc driver that talks to the windows configuration.

                  • 6. Re: Access Query Error
                    kodemonki Community Member
                    kodemonki May 12, 2009 1:51 PM (in response to ilssac)

                    I agree.

                     

                    Apparently I can request data from any table or field created before eligible_value was created, but not after.  I made new test tables with all the same fields to see if I could pull from that, and I got "The Microsoft Office Access database engine cannot find the input table or query 'kate'. Make sure it exists and that its name is spelled correctly. "

                     

                    I'm using the driver found here: http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C 891

                    and retracing these steps: http://www.adobe.com/support/documentation/en/coldfusion/8/releasenotes.pdf (see MS Access 2007 issues)

                     

                    Then restarting all the CF services.  Hope this works!

                    • 7. Re: Access Query Error
                      kodemonki Community Member
                      kodemonki May 12, 2009 2:08 PM (in response to kodemonki)

                      w00t!!!

                       

                      Hopefully that doesn't happen again . . .