SQL error in java code "Column count doesn't match value count at row 1"

Question

I have a problem with my program. When I try to write to my database (in MySQL) I get this error "Column count doesn't match value count at row 1"

This is my code:

public void registreerNieuwSpelbord(String spelnaam, String mapcode) {
    try (Connection connectie = DriverManager.getConnection(Connectie.JDBC_URL)) {
        Statement stmt = connectie.createStatement();
        String schrijfSpelbordWeg = "INSERT INTO spelbord(Mapcode, spel_Spelnaam) values('" + mapcode + "," + spelnaam + "')";
        stmt.executeUpdate(schrijfSpelbordWeg);
    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

note: there is also a 3th column with an ID that automatically gives a number

You're missing the closing single-quotes (') in your query in that middle comma section (","): INSERT INTO spelbord(Mapcode, spel_Spelnaam) values('" + mapcode + "','" + spelnaam + "') — Der Kommissar
– Der Kommissar, Commented Apr 26, 2015 at 20:21
Note that you wouldn't have had this problem if you were using a PreparedStatement, which is what you should be using every time you're inputting parameters into SQL. This way you can avoid SQL injection problems and do not need to worry about (valid) parameters containing special characters like '. — Mick Mnemonic
– Mick Mnemonic, Commented Apr 26, 2015 at 20:34

Jason Byrne · Accepted Answer · 2015-04-26 20:16:16Z

1

You have two columns listed in the insert, but only one value.

Try this:

String schrijfSpelbordWeg = "INSERT INTO spelbord(Mapcode, spel_Spelnaam) values('" + mapcode + "','" + spelnaam + "')";

answered Apr 26, 2015 at 20:16

Jason Byrne

1,63110 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason Byrne Over a year ago

I didn't mention in my answer above that the reason is you missed two single quotes that should have been there, which you'll see in the code above.

Mick Mnemonic · Accepted Answer · 2015-04-26 22:50:57Z

You should always use a PreparedStatement and bind variables when dealing with SQL that takes input parameters. This way, you're eliminating the chance of SQL injection, allowing the DB to re-use/cache your query and sparing yourself from hunting down bugs that are caused by missing a quote around a parameter.

Here's a refactored version that uses parameterized SQL:

public void registreerNieuwSpelbord(String spelnaam, String mapcode) {

    String sql = "INSERT INTO spelbord(Mapcode, spel_Spelnaam) values(?, ?)";

    try (Connection connectie = DriverManager.getConnection(Connectie.JDBC_URL);
            PreparedStatement ps = connectie.prepareStatement(sql);) {

        ps.setString(1, mapcode);
        ps.setString(2, spelnaam);
        ps.executeUpdate();

    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    }
}

Collectives™ on Stack Overflow

SQL error in java code "Column count doesn't match value count at row 1"

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related