Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Too few parameters error

 
Paras Ahuja
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • 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
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • 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.
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • 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
  • 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 Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • 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.)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic