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:
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.
Paras Ahuja wrote:in some case i am using ps.setType() command going in if-else clause i.e
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.)