• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Too few parameters error

 
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi everyone,
I am using prepared statement to update the contents of my ms-access database.
It shows the following error:
Too few parameters. Expected 77.
Now my database has 76 records and i am updating all of them. I have checked my column names, they are exactly same.
Can anyone tell why this error is coming? If its because of syntax of sql statement, here is my statement:

Please see to it...
Thanks in advance
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There is a whole lot of parameters in that query!

Number of columns in the table is not that important. This error means you're trying to set parameter number 77, but it is not there, presumably there are only 76 question marks in the command text (I didn't count them ). In this situation, I'd probably try to build the command text dynamically, just so that I would not have to manually check and count the question marks.

But, even though you're using the PreparedStatement already, you stuff in literals nevertheless (and therefore potentially expose yourself to SQL injection). Use this occasion to convert the remaining two values (in the WHERE clause) to parameters too.
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
and you have 76 lines going
ps.setType[x] = value;
 
Paras Ahuja
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
in some case i am using ps.setType() command going in if-else clause i.e
if(this)
ps.setString(75,----);
else
ps.setString(75,-----);

Is this ok?
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paras Ahuja wrote:in some case i am using ps.setType() command going in if-else clause i.e
if(this)
ps.setString(75,----);
else
ps.setString(75,-----);

Is this ok?


Yes, it is. You can even set one parameter more than once or not at all (though some JDBC drivers might have some issues with either of these situations, the JDBC specs allow for it).

However, do you really use hard-coded numbers i your code? I usually do it like this:

That way, if I need to add a parameter somewhere, I do not need to go and renumber all the hardcoded numbers over there (probably skipping something and introducing subtle bugs into the code).

And I'll repeat myself: with 70+ parameters, I'd build the command text and parameter list dynamically. Matching the parameters to question marks in such a huge string must be very tedious.
(And once you write that code, it can be easily reused.)
 
reply
    Bookmark Topic Watch Topic
  • New Topic