none
Too Few Parameters - Error RRS feed

  • Question

  • The program that i am working on is in Visual Basic 2008 using an Access 200 database.I call an Update query with  nine(9) fields and nine(9) parameters.

    When I try to run the program, I get the error message "Too few Parameters, expected 10".  My query ia as follows:

     UPDATE tblOnLoan SET LoanStatus = [@LoanStatus], Collection = [@Collection], Title = [@Title], LoanDate = [@LoanDate], LoanTo = [@Loanto], ReturnDate = [@ReturnDate], PhoneNumber = [@PhoneNumber], Comments = [@Comments] WHERE LoanID=[@LoanID];

    I count only nine(9) parameters and there are nine(9) in the Visual Basic code.

    I tried running the query in Access and itasked for the @Comments parameter twice.

    I have checked everything that I can think of but can find no cause for this problem, can anyone help?

    Saturday, September 11, 2010 5:05 PM

Answers

  • The program that i am working on is in Visual Basic 2008 using an Access 200 database.I call an Update query with  nine(9) fields and nine(9) parameters.

    When I try to run the program, I get the error message "Too few Parameters, expected 10".  My query ia as follows:

     UPDATE tblOnLoan SET LoanStatus = [@LoanStatus], Collection = [@Collection], Title = [@Title], LoanDate = [@LoanDate], LoanTo = [@Loanto], ReturnDate = [@ReturnDate], PhoneNumber = [@PhoneNumber], Comments = [@Comments] WHERE LoanID=[@LoanID];

    I count only nine(9) parameters and there are nine(9) in the Visual Basic code.

    I tried running the query in Access and itasked for the @Comments parameter twice.

    I have checked everything that I can think of but can find no cause for this problem, can anyone help?


    How are you connecting to the database? Can you post the full procedure?

    Based on the code, I suspect that the problem may actually be the DataSource control that you're using to access the database.


    David H
    • Marked as answer by sirMilt Monday, October 11, 2010 5:39 PM
    Saturday, September 11, 2010 6:17 PM
  • Milt,

    grab the *exact* sql query string at the point where your VB8 app is about to execute it and copy/paste it directly into a Querydef in MS Access.

    while your VB8 code remains at that breakpoint, go into Access and RUN that query directly from the database and observe the results there.

    This will give you the specifics on where the issue is arising.

    If your query performs as expected within access itself, then the issue is arising in how you are processing the query from your app. If the query itself prompts 10 times from within Access, then check your SQL syntax against your base tables/queries again - looking very carefully for spelling or similar issues.

    • Proposed as answer by Ji.ZhouModerator Wednesday, September 15, 2010 7:03 AM
    • Marked as answer by Bessie Zhao Monday, October 11, 2010 10:07 AM
    Saturday, September 11, 2010 6:35 PM
  • Mark and David

    Thank you both for your speedy replies. I ran the query from within Access and each field accepted input when I got to the last field - "Comments" -after the first input was accepted it asked for @Comments again, as if were in the query twice, and it is not. My first inclination was to look for a mis-spelling, but there is none, I also re wrote the query and changed its name, same result.

    I'm really at a loss, the same basic query construction for an update query works fine in several other placed.

    Milt

    • Marked as answer by sirMilt Saturday, September 11, 2010 8:22 PM
    • Unmarked as answer by sirMilt Saturday, September 11, 2010 8:22 PM
    • Marked as answer by sirMilt Saturday, September 11, 2010 8:23 PM
    • Unmarked as answer by sirMilt Saturday, September 11, 2010 8:23 PM
    • Proposed as answer by Ji.ZhouModerator Wednesday, September 15, 2010 7:03 AM
    • Marked as answer by Bessie Zhao Monday, October 11, 2010 10:07 AM
    Saturday, September 11, 2010 8:09 PM
  • sirMilt,

    Yup - misspellings _somewhere_ are almost always at the heart of these sort of issues.  That;s why I said to "look very carefully for spelling errors" in my first message.  :-)

     

    • Marked as answer by sirMilt Monday, October 11, 2010 5:40 PM
    Thursday, September 16, 2010 5:29 PM

All replies

  • The program that i am working on is in Visual Basic 2008 using an Access 200 database.I call an Update query with  nine(9) fields and nine(9) parameters.

    When I try to run the program, I get the error message "Too few Parameters, expected 10".  My query ia as follows:

     UPDATE tblOnLoan SET LoanStatus = [@LoanStatus], Collection = [@Collection], Title = [@Title], LoanDate = [@LoanDate], LoanTo = [@Loanto], ReturnDate = [@ReturnDate], PhoneNumber = [@PhoneNumber], Comments = [@Comments] WHERE LoanID=[@LoanID];

    I count only nine(9) parameters and there are nine(9) in the Visual Basic code.

    I tried running the query in Access and itasked for the @Comments parameter twice.

    I have checked everything that I can think of but can find no cause for this problem, can anyone help?


    How are you connecting to the database? Can you post the full procedure?

    Based on the code, I suspect that the problem may actually be the DataSource control that you're using to access the database.


    David H
    • Marked as answer by sirMilt Monday, October 11, 2010 5:39 PM
    Saturday, September 11, 2010 6:17 PM
  • Milt,

    grab the *exact* sql query string at the point where your VB8 app is about to execute it and copy/paste it directly into a Querydef in MS Access.

    while your VB8 code remains at that breakpoint, go into Access and RUN that query directly from the database and observe the results there.

    This will give you the specifics on where the issue is arising.

    If your query performs as expected within access itself, then the issue is arising in how you are processing the query from your app. If the query itself prompts 10 times from within Access, then check your SQL syntax against your base tables/queries again - looking very carefully for spelling or similar issues.

    • Proposed as answer by Ji.ZhouModerator Wednesday, September 15, 2010 7:03 AM
    • Marked as answer by Bessie Zhao Monday, October 11, 2010 10:07 AM
    Saturday, September 11, 2010 6:35 PM
  • Mark and David

    Thank you both for your speedy replies. I ran the query from within Access and each field accepted input when I got to the last field - "Comments" -after the first input was accepted it asked for @Comments again, as if were in the query twice, and it is not. My first inclination was to look for a mis-spelling, but there is none, I also re wrote the query and changed its name, same result.

    I'm really at a loss, the same basic query construction for an update query works fine in several other placed.

    Milt

    • Marked as answer by sirMilt Saturday, September 11, 2010 8:22 PM
    • Unmarked as answer by sirMilt Saturday, September 11, 2010 8:22 PM
    • Marked as answer by sirMilt Saturday, September 11, 2010 8:23 PM
    • Unmarked as answer by sirMilt Saturday, September 11, 2010 8:23 PM
    • Proposed as answer by Ji.ZhouModerator Wednesday, September 15, 2010 7:03 AM
    • Marked as answer by Bessie Zhao Monday, October 11, 2010 10:07 AM
    Saturday, September 11, 2010 8:09 PM
  • Mark and David

    Thank you both for your speedy replies. I ran the query from within Access and each field accepted input when I got to the last field - "Comments" -after the first input was accepted it asked for @Comments again, as if were in the query twice, and it is not. My first inclination was to look for a mis-spelling, but there is none, I also re wrote the query and changed its name, same result.

    I'm really at a loss, the same basic query construction for an update query works fine in several other placed.

    Milt


    Are you using VB.NET or Visual Basic for Application? Would you please post the full code surrounding the SQL Statement - the statements leading up to it from the start of the module or class that its in?

    David H
    Saturday, September 11, 2010 10:51 PM
  • Thank you all for trying to help.  I found the answer, it was a simple mispelling of the field name. I corrected it and the error vanished.

    I think the wording of the error message could have been improved. It could simply say "No such field as 'Coments'

    Thursday, September 16, 2010 12:46 PM
  • Thank you all for trying to help.  I found the answer, it was a simple mispelling of the field name. I corrected it and the error vanished.

    I think the wording of the error message could have been improved. It could simply say "No such field as 'Coments'


    Well, no it couldn't actually.  The way a parameter works in standard SQL is to put a prompt in brackets.  If the query analyzer runs into something in brackets that is not a field it recognizes, it assumes it's a parameter and asks for a value.  The error was not that there is no such field as Coments, it's that VB didn't know how to handle the parameter, which is what it told you.
    -- Roger Carlson
    MS Access MVP 2006-2010
    www.rogersaccesslibrary.com
    Thursday, September 16, 2010 1:59 PM
  • sirMilt,

    Yup - misspellings _somewhere_ are almost always at the heart of these sort of issues.  That;s why I said to "look very carefully for spelling errors" in my first message.  :-)

     

    • Marked as answer by sirMilt Monday, October 11, 2010 5:40 PM
    Thursday, September 16, 2010 5:29 PM