Java, mysql error:Column count doesn't match value count at row 1

Question

So, this is my piece of code that enters details into a database named vendor_db.(using java and mysql) and I am getting an error every time even though the code seems correct. I have added the whole servlet code for reference.

@WebServlet("/DBHandler")
public class DBHandler extends HttpServlet {
private static final long serialVersionUID = 1L; 
/**
 * @see HttpServlet#HttpServlet()
 */
public DBHandler() {
    super();
}
/**
 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
 */
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter pw = response.getWriter();
        FormBean f = (FormBean)request.getAttribute("formHandler");
        boolean userExists = false;
        final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        final String DB_URL = "jdbc:mysql://localhost:3306/ioc?autoReconnect=true&useSSL=false";
        final String USER = "root";
        final String PASS = "1234";
        try( Connection conn=DriverManager.getConnection(DB_URL,USER,PASS)){
        Class.forName(JDBC_DRIVER);
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery("select vcode from vendor_db");
        while(rs.next()) {
            if(f.getCode().equals(rs.getString(1)))
            {
                userExists=true;
            }
        }   
        if(userExists) {
            f.setErrors("code","Duplicate user: Try a different vendor code");
            getServletConfig().getServletContext().getRequestDispatcher("/forms/retry.jsp").forward(request, response);
        }
        else {
            String sql= "insert into vendor_db(vcode,vname,vmob,vemail,vpass,gst_no)values(?,?,?,?,?,?)";
            try(PreparedStatement prep = conn.prepareStatement(sql)){
            prep.setString(1,f.getCode());
            prep.setString(2,f.getName());
            prep.setString(3,f.getPhone());
            prep.setString(4,f.getEmail());
            prep.setString(5,f.getPass());
            prep.setString(6,f.getGst_no());
            int i = prep.executeUpdate();
            prep.clearParameters();
            if(i!=0) {
                getServletConfig().getServletContext().getRequestDispatcher("/forms/login.jsp").forward(request, response);
                }
                else {
                    pw.println("Not entered in database");
                }
                prep.close();
            }
            catch(Exception e) {
                System.out.println(e);
            }       
        }   
    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
}

}

But, i keep getting the same error:

java.sql.SQLException: Column count doesn't match value count at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
at DBHandler.doPost(DBHandler.java:56)

It's basically showing an error in the executeUpdate() line.Please help. Thanks!

Indeed the code looks fine to me. Can it be that the table definition requires more columns to be inserted, like an ID column without AUTOINCREMENT, and hence needed to be given. Or such. One tip: try-with-resources fits very nice to JDBC. — Joop Eggen, May 28 '18 at 15:09
`try (PreparedStatement prep = conn.prepareStatement(sql)) { ... }` with automatic `close()` on `}` even with return/break/exception. Same for Connection and ResultSet. — Joop Eggen, May 28 '18 at 15:25

Joop Eggen · Answer 1 · 2018-05-29T13:41:57.377

As the code looks fine, it might be a multiuser issue.

First I would check that line 56 of DBHandler is indeed that executeUpdate.

DBHandler.doPost(DBHandler.java:56)

Somehow I doubt it. Maybe a deployment went wrong or whatever.

If the DBHandler, or other data would erroneously be a field of a servlet, then the servlet is not stateless, and two doPosts/doGets could "share" the same field, overwrite it and such.

It could also happen, when the code shown is split in the source, and the control flow allows to overwrite the field, say in doGet or service.

No fields.

After the whole code was added

Unfortunately only minor points:

// Not needed nowadays: Class.forName(JDBC_DRIVER);
try (Connection conn = DriverManager.getConnection(DB_URL,USER,PASS);
        PreparedStatement st = conn.prepareStatement(
                "select vcode from vendor_db where vcode = ?")) {
    st.setString(f.getCode());
    try (ResultSet rs = st.executeQuery()) {
        if (rs.next()) {
            userExists = true;
        }
    }   
    if (userExists) {
        f.setErrors("code",
            "Duplicate user: Try a different vendor code");
        getServletConfig().getServletContext()
            .getRequestDispatcher("/forms/retry.jsp")
            .forward(request, response);
    }
    else {
        String sql= "insert into vendor_db"
            + "(vcode,vname,vmob,vemail,vpass,gst_no)"
            + "values(?,?,?,?,?,?)";
        try (PreparedStatement prep = conn.prepareStatement(sql)) {
            prep.setString(1, f.getCode());
            prep.setString(2, f.getName());
            prep.setString(3, f.getPhone());
            prep.setString(4, f.getEmail());
            prep.setString(5, f.getPass());
            prep.setString(6, f.getGst_no());
            int i = prep.executeUpdate();
            // Not needed here: prep.clearParameters();
            if (i != 0) {
                getServletConfig().getServletContext()
                    .getRequestDispatcher("/forms/login.jsp")
                    .forward(request, response);
            }
            else {
                pw.println("Not entered in database");
            }
        }
    }   
}
catch (Exception e)
{
    log("An error happened", e);
    e.printStackTrace();
}

See java.sql.SQLException: Column count doesn't match value count at row 1 for the same error.

My guess would have been that the application somehow is not redeployed in the web server.

at DBHandler.doPost(DBHandler.java:56)

This should be int i = prep.executeUpdate();

Points to line 56 of DBHandler.java. Add a couple of empty lines to tee whether the number changes.

6 Question marks (no quotes - fine), 6 column names, 6 parameters set.

I added the whole code. Could you please check? It's still showing the error at the executeUpdate line. — Sonal Bihani, May 28 '18 at 15:56

Java, mysql error:Column count doesn't match value count at row 1

1 Answers1