File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Too few parameters error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Too few parameters error" Watch "Too few parameters error" New topic
Author

Too few parameters error

Paras Ahuja
Ranch Hand

Joined: May 22, 2012
Posts: 62
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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Oct 21, 2008
Posts: 1107

and you have 76 lines going
ps.setType[x] = value;
Paras Ahuja
Ranch Hand

Joined: May 22, 2012
Posts: 62
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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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.)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Too few parameters error