java.sql.SQLException: Column count doesn't match value count at row 1 with Java and MySQL [duplicate]

Question

Hi I'm trying to insert in a table the next values. This table has 32 values, the first one is autoincrement so it's no neccesary to put it, but when I execute it I have the next mistake:

java.sql.SQLException: Column count doesn't match value count at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2450)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2371)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2355)
at es.uniway.wscloud.WscloudImpl.sincronizarPedidos(WscloudImpl.java:1729)
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:601)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:164)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:562)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:395)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:250)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:188)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:166)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:302)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:722)

And this is the query:

SQL="INSERT INTO servidores_virtuales (id_pedido,nombre_servidor,duracion_contrato,cpu_cant,ram_cant,"+
                "hdd_cant,hdd_unidad,sv_pvp,cpu_pvp,ram_pvp,hdd_pvp,sv_pvp_dist,sv_pvp_dist,cpu_pvp_dist,ram_pvp_dist,hdd_pvp_dist,"+
                "ip_pvp,ip_pvp_dist,ip_polled_pvp,ip_polled_pvp_dist,ip_custom_pvp,ip_custom_pvp_dist,data_center,cluster,plantilla,"+
                "estado_aprobacion,f_aprobacion,f_provision,es_actualizacion,f_actualizacion,disco_almacenamiento,id_sv_ccis)VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                int keyServidorV=0;
                System.out.println("Key de pedido es:"+key);
                this.pstm = this.conexion.prepareStatement(SQL,PreparedStatement.RETURN_GENERATED_KEYS);


                this.pstm.setInt(1, key); //Last insert id de pedido
                this.pstm.setString(2, pedido.getDatos_SV()[i].getNombre_servidor());
                this.pstm.setString(3, pedido.getDatos_SV()[i].getDuracion_contrato());
                this.pstm.setInt(4, pedido.getDatos_SV()[i].getCpu_cant());
                this.pstm.setString(5, pedido.getDatos_SV()[i].getRam_cant());
                this.pstm.setString(6, pedido.getDatos_SV()[i].getHdd_cant());
                this.pstm.setString(7, pedido.getDatos_SV()[i].getHdd_unidad());
                this.pstm.setDouble(8, pedido.getDatos_SV()[i].getSv_pvp());
                this.pstm.setDouble(9, pedido.getDatos_SV()[i].getCpu_pvp());
                this.pstm.setDouble(10, pedido.getDatos_SV()[i].getRam_pvp());
                this.pstm.setDouble(11, pedido.getDatos_SV()[i].getHdd_pvp());
                this.pstm.setDouble(12, pedido.getDatos_SV()[i].getSv_pvp_dist());
                this.pstm.setDouble(13, pedido.getDatos_SV()[i].getCpu_pvp_dist());
                this.pstm.setDouble(14, pedido.getDatos_SV()[i].getRam_pvp_dist());
                this.pstm.setDouble(15, pedido.getDatos_SV()[i].getHdd_pvp_dist());
                this.pstm.setDouble(16, pedido.getDatos_SV()[i].getIp_pvp());
                this.pstm.setDouble(17, pedido.getDatos_SV()[i].getIp_pvp_dist());
                this.pstm.setDouble(18, pedido.getDatos_SV()[i].getIp_polled_pvp());
                this.pstm.setDouble(19, pedido.getDatos_SV()[i].getIp_polled_pvp_dist());
                this.pstm.setDouble(20, pedido.getDatos_SV()[i].getIp_custom_pvp());
                this.pstm.setDouble(21, pedido.getDatos_SV()[i].getIp_custom_pvp_dist());
                this.pstm.setString(22, pedido.getDatos_SV()[i].getData_center());
                this.pstm.setString(23, pedido.getDatos_SV()[i].getCluster());
                this.pstm.setString(24, pedido.getDatos_SV()[i].getPlantilla());
                this.pstm.setInt(25, pedido.getDatos_SV()[i].getEstado_aprobacion());
                java.sql.Date f_aprobacionAux = new java.sql.Date(pedido.getDatos_SV()[i].getF_aprobacion().getTime());  
                this.pstm.setDate(26, f_aprobacionAux);
                java.sql.Date f_provisionAux = new java.sql.Date(pedido.getDatos_SV()[i].getF_provision().getTime());
                this.pstm.setDate(27, f_provisionAux);
                this.pstm.setBoolean(28, pedido.getDatos_SV()[i].isEs_actualizacion());
                if(pedido.getDatos_SV()[i].getF_actualizacion()!=null){
                    java.sql.Date f_actualizacionAux =new java.sql.Date(pedido.getDatos_SV()[i].getF_actualizacion().getTime());
                    this.pstm.setDate(29, f_actualizacionAux);  
                }else{

                  this.pstm.setDate(29, null);

                }
                this.pstm.setString(30, pedido.getDatos_SV()[i].getDisco_almacenamiento());
                this.pstm.setInt(31,pedido.getDatos_SV()[i].getId_servidor_virtual_ccis());





                switch(pedido.getDatos_SV()[i].getEstado_aprobacion()){


                     case 0:{
                         switch(estado_pedido){
                             case 0:{
                                 estado_pedido =1;
                                 break;
                             }
                             case 1:{
                                 estado_pedido =1;
                                 break;
                             }
                             case 2:{
                                 estado_pedido = 1;
                                 break;
                             }

                         }
                         break;
                     }
                     case 1:{
                         switch(estado_pedido){

                            case 0:{
                                estado_pedido = 2;
                                break;
                            }
                            case 1:{
                                estado_pedido =1;
                                break;
                            }
                            case 2:{
                                estado_pedido=2;
                                break;
                            }
                         }
                         break;
                     }

                }
                if(estado_pedido==2){
                    if(f_aprobacion==null){
                        f_aprobacion = pedido.getDatos_SV()[i].getF_aprobacion();
                    }else{
                        if (f_aprobacion.compareTo(pedido.getDatos_SV()[i].getF_aprobacion())<0){

                            f_aprobacion = pedido.getDatos_SV()[i].getF_aprobacion();
                        }else{
                            f_aprobacion = f_aprobacion;
                        }

                    }

                    if(pedido.getDatos_SV()[i].getF_provision()==null){
                        pedido_provisionado = false;
                    }
                    if(f_provision == null){
                        pedido.getDatos_SV()[i].getF_provision();
                    }else{
                        if(pedido.getDatos_SV()[i].getF_provision().compareTo(f_provision)>0){
                            f_provision=pedido.getDatos_SV()[i].getF_provision();
                        }else{
                            f_provision=f_provision;
                        }
                    }

                }

                String [] duracion = pedido.getDatos_SV()[i].getDuracion_contrato().split(" ");
                int cant = Integer.parseInt(duracion[0].trim());
                String unidad = duracion[1].trim().toUpperCase();

                if(unidad_duracion_contrato == ""){
                    unidad_duracion_contrato = unidad;
                    duracion_contrato = cant;
                }else{
                    if (unidad_duracion_contrato != unidad || duracion_contrato != cant){
                        throw new Exception ("Pedido con diferentes duraciones de contrato: "+pedido.getId_pedido());
                    }
                }

                int ejecutadoServiVirtual =  this.pstm.executeUpdate();

                if(ejecutadoServiVirtual==0){   
                   try {
                    throw new Exception("Error al grabar Pedidos");
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }

Please, could you help me with this?. Thanks so much

Don't include the name of the first column into your query. If it's auto increment you don't need to specify it in the query. — robonerd
– robonerd, Commented Sep 26, 2012 at 15:41

Kenster · Accepted Answer · 2012-09-26 15:49:21Z

1

Your SQL query lists 32 columns ("id_pedido" and so on), but only 31 values (the '?'s). The list of columns must match the list of values. If you aren't going to specify a value for a column, then don't include that column in the column list.

answered Sep 26, 2012 at 15:49

Kenster

25.8k23 gold badges87 silver badges115 bronze badges

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

1 Comment

zoit Over a year ago

The id_pedido comes from another insert which I have done previously, the column which I have omitted is id_sv which it isn't there as you can see. With the autoincrement field I hava 32 columns but I have as you can check 31. Thanks

PermGenError · Accepted Answer · 2012-09-26 15:41:22Z

0

you are having one less '?' in the values. count it right. you have 32 columns but u only gave 31 '?' if the first field is an autoincrement, ignore the autoincrementted field

answered Sep 26, 2012 at 15:41

PermGenError

46.5k8 gold badges90 silver badges107 bronze badges

5 Comments

zoit Over a year ago

I have 32 yes cut counting the autoincrement value, if I don't count it, I have 31 which is what I have written on my query. I have ignored the autoincrement field.

PermGenError Over a year ago

@zoit java.sql.SQLException: Column count doesn't match value count at row 1 with would only occour when the columns dont match with '?'.

zoit Over a year ago

I know it, I know it, but as I say with the atoincrement field I have 32 and as you can check, here I have 31 because I have ommited it.

PermGenError Over a year ago

@zoit you have to remove the autoincrement column from your query.your columns and ? should be 31

zoit Over a year ago

It's removed and nothing

Lyn Headley · Accepted Answer · 2012-09-26 15:41:36Z

0

If you insert a null for the autoincrement field then mysql will generate the incremented value.

answered Sep 26, 2012 at 15:41

Lyn Headley

11.6k4 gold badges36 silver badges38 bronze badges

Comments

Anhsirk Reddy · Accepted Answer · 2012-09-26 15:49:28Z

0

Your column id's should match your '?'s. You can remove the 'id_pedido' column id from your list of column id's and that should fix it.

answered Sep 26, 2012 at 15:49

Anhsirk Reddy

715 bronze badges

Comments

Abhishek J · Accepted Answer · 2015-02-10 04:04:17Z

-1

Generally this exception occurs when the order of values modified is different than what is actually stored int the table .. Check column indexes.

answered Feb 10, 2015 at 4:04

Abhishek J

241 bronze badge

Collectives™ on Stack Overflow

java.sql.SQLException: Column count doesn't match value count at row 1 with Java and MySQL [duplicate]

5 Answers 5

1 Comment

5 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

5 Comments

Comments

Comments

Comments

Linked

Related