Post failed to insert data into MySQL table with column count doesn't match value count at row 1

Question

Unable to do insert data into MySql database using post method, getting the error java.sql.SQLException: Column count doesn't match value count at row 1 Below is my INSERT statement, it was working fine earlier, but after adding two new columns getting the above error. Following are the details about two new columns: PLAYER_STATUS, IsAdmin

PLAYER_STATUS varchar(250) NO Active - This column is not NULL with default value 'Active'
IsAdmin tinyint(1) NO 0 - This boolean column is not NULL with default value 0

PreparedStatement ps = conn.prepareStatement(
                                        "INSERT INTO mycoolmap.weekendsoccer_login values(default,?,?,?,?,?,?,?)",
                                        Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, p_Name);
                        ps.setString(2, p_Email);
                        ps.setString(3, p_Mobile);                      
                        ps.setString(4, encrptPass);                
                        ps.setString(5, p_Company);         
                        ps.setString(6, "Active");          // newly added column
                        ps.setInt(7, 0);                    // newly added column

                        x = ps.executeUpdate();

**//Error details below:**
java.sql.SQLException: Column count doesn't match value count at row 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
    at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)
    at com.myfirstjavatest.pkg.JSONService.createPlayerInJSON(JSONService.java:186)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161)
    at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:160)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)
    at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
    at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)
    at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)

a) it is best to specify the field names b) what is that default thing? If you mean it to be an auto-generated field then remove it — Scary Wombat
@Scary wombat: First 'default' value is an ID column which is autogenerated. As advised by you should I give values like this (default,?,?,?,?,?,"Active", 0). Here 'Active' and 0 are again default values — soccerway
No remove default and auto-generated values. I am suggesting that you specify the field names e.g. insert into myTable (name, address) values (?,?); — Scary Wombat

Liem Le Liem Le · Accepted Answer · 2018-02-06T03:54:31

As @Scary has pointed out. You should specify the field names e.g. insert into table_name(field_1, field_2) value (?, ?).
If you need set default value for a row and you have role to controller over MySQL. You should do that in SQL, that safer (but less flexible). e.g.

CREATE TABLE weekendsoccer_login(
   id INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
   ...
   status varchar(255) NOT NULL DEFAULT "Active",
   ...
   PRIMARY KEY (id)
);

For learning JDBC, I'm sure you're doing good. But if you really want to go for production, you should take a look at JPA (Hibernate or EclipseLink implementation) or jOOQ (I love this one, really handy and save a lot of time)

Post failed to insert data into MySQL table with column count doesn't match value count at row 1

1 Answers