update statement Java sql exception: Too few parameters expected one more

Question

So I have a method for updating some fields in a MS Access 2007 table, and whenever I try to update I get the following exception: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 11. It always shows that one more is expected than there are in the query.

The code of the method is:

try {
        String upit = "UPDATE Izlagac SET NazivIzlagaca=?, PIB=?, Telefon=?, "
                + " KontaktOsoba=?, Email=?, TipIzlagaca=?, Ulica=?, Broj=?, Mesto=?, Drzava=? WHERE "
                + " SifraIzlagaca = " + i.getSifraIzlagaca() + ";";
        PreparedStatement pstmt = con.prepareStatement(upit);
        pstmt.setString(1, i.getNazivIzlagaca());
        pstmt.setString(2, i.getPIB());
        pstmt.setString(3, i.getTelefon());
        pstmt.setString(4, i.getKontaktOsoba());
        pstmt.setString(5, i.getEmail());
        pstmt.setString(6, i.getTipIzlagaca());
        pstmt.setString(7, i.getUlica());
        pstmt.setString(8, i.getBroj());
        pstmt.setString(9, i.getMesto());
        pstmt.setString(10, i.getDrzava());
        System.out.println(pstmt.toString());
        pstmt.executeUpdate();
        pstmt.close();
        return true;
    } catch (SQLException ex) {
        Logger.getLogger(DBBroker.class.getName()).log(Level.SEVERE, null, ex);
        return false;
    }

I am not using aliases so that doesn't seem to be the reason why the driver would expect additional parameter. All the column names are correctly spelled, triple checked it.

i.getSifraIzlagaca() does not happen to return a question mark, right? — yankee
– yankee, Commented May 17, 2014 at 12:39
Why not use ... WHERE SifraIzlagaca = ? and pstmt.setString(11, i.getSifraIzlagaca()); ? — Gord Thompson
– Gord Thompson, Commented May 17, 2014 at 12:51
I don't see i.getSifraIzlagaca() is actually wrapped within quotes! Never used MS Access, but aren't string parameters supposed to be quoted in standard SQL ? — Anirban Basak
– Anirban Basak, Commented May 17, 2014 at 12:55
Anirban Basak, yes it isn't but when you do wrap it, it throws a data mistype exception. Gord, thank you, it works now, it is a simple and elegant solution, I'll do that from now on.. :) Although it should work this way too, but that's the evil Microsoft driver :) — Matija
– Matija, Commented May 17, 2014 at 13:18

Gord Thompson · Accepted Answer · 2014-05-17 13:30:24Z

There is no need to use string concatenation to build the WHERE clause; parameters are just as valid there as they are in the other parts of the PreparedStatement. So, just use

String upit = "UPDATE Izlagac SET NazivIzlagaca=?, PIB=?, Telefon=?, "
        + " KontaktOsoba=?, Email=?, TipIzlagaca=?, Ulica=?, Broj=?, Mesto=?, Drzava=? WHERE "
        + " SifraIzlagaca=?";
PreparedStatement pstmt = con.prepareStatement(upit);
pstmt.setString(1, i.getNazivIzlagaca());
pstmt.setString(2, i.getPIB());
pstmt.setString(3, i.getTelefon());
pstmt.setString(4, i.getKontaktOsoba());
pstmt.setString(5, i.getEmail());
pstmt.setString(6, i.getTipIzlagaca());
pstmt.setString(7, i.getUlica());
pstmt.setString(8, i.getBroj());
pstmt.setString(9, i.getMesto());
pstmt.setString(10, i.getDrzava());
pstmt.setString(11, i.getSifraIzlagaca());

yes, I realized that, after the comments above, and corrected it, it works fine now, thank you :)

Collectives™ on Stack Overflow

update statement Java sql exception: Too few parameters expected one more

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related