Error "Column count does not match value count at row 1" in java [closed]

Question

Error "Column count does not match value count at row 1" in java [closed]

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

Good morning,

When I insert or update data in MySQL from java I get the commented error. I do not know why, the number of columns is correct ... in fact, before I had the same code but with fewer columns, I do not understand where the problem may be.

The code is as follows:

public Conectate(ArrayList<Item> games, final Map<String, String> countries) {    

    try {
        Class.forName(driver);
        con = DriverManager.getConnection(cadenaConexion, usuario, pass);
        System.out.println("¡Conectado!");


        //CREAMOS LA TABLA
        Statement st = con.createStatement();

        st.executeUpdate("CREATE TABLE IF NOT EXISTS info_XboxOne (id INT AUTO_INCREMENT, PRIMARY KEY(id), "
                + "Juego_vinculado VARCHAR(500), Juego VARCHAR(500), Tipologia VARCHAR (500), Pertenece VARCHAR (500), "
                + "Nota VARCHAR (10), Descripcion_Ingles TEXT(4000), Descripcion_Castellano TEXT(4000), Pegi VARCHAR(10), Pegi_USA VARCHAR (30) , Descripcion_Pegi VARCHAR(500), Codigo_juego VARCHAR (100),"
                + "Lanzamiento VARCHAR (50), URL VARCHAR (400),"
                        + "Argentina VARCHAR (50), Argentina_Oferta VARCHAR (50), Argentina_Gold VARCHAR (50), Argentina_Access VARCHAR (50),"
                        + "Australia VARCHAR (50), Australia_Oferta VARCHAR (50), Australia_Gold VARCHAR (50), Australia_Access VARCHAR (50),"
                        + "Brasil VARCHAR (50), Brasil_Oferta VARCHAR (50), Brasil_Gold VARCHAR (50), Brasil_Access VARCHAR (50),"
                        + "Canada VARCHAR (50), Canada_Oferta VARCHAR (50), Canada_Gold VARCHAR (50), Canada_Access VARCHAR (50),"
                        + "Chile VARCHAR (50), Chile_Oferta VARCHAR (50), Chile_Gold VARCHAR (50), Chile_Access VARCHAR (50),"
                        + "China VARCHAR (50), China_Oferta VARCHAR (50), China_Gold VARCHAR (50), China_Access VARCHAR (50),"
                        + "Colombia VARCHAR (50), Colombia_Oferta VARCHAR (50), Colombia_Gold VARCHAR (50), Colombia_Access VARCHAR (50),"
                        + "Espanya VARCHAR (50), Espanya_Oferta VARCHAR (50), Espanya_Gold VARCHAR (50), Espanya_Access VARCHAR (50),"
                        + "USA VARCHAR (50), USA_Oferta VARCHAR (50), USA_Gold VARCHAR (50), USA_Access VARCHAR (50),"
                        + "Hong_Kong VARCHAR (50), Hong_Kong_Oferta VARCHAR (50), Hong_Kong_Gold VARCHAR (50), Hong_Kong_Access VARCHAR (50),"
                        + "Hungria VARCHAR (50), Hungria_Oferta VARCHAR (50), Hungria_Gold VARCHAR (50), Hungria_Access VARCHAR (50),"
                        + "India VARCHAR (50), India_Oferta VARCHAR (50), India_Gold VARCHAR (50), India_Access VARCHAR (50),"
                        + "Inglaterra VARCHAR (50), Inglaterra_Oferta VARCHAR (50), Inglaterra_Gold VARCHAR (50), Inglaterra_Access VARCHAR (50),"
                        + "Japon VARCHAR (50), Japon_Oferta VARCHAR (50), Japon_Gold VARCHAR (50), Japon_Access VARCHAR (50),"
                        + "Korea VARCHAR (50), Korea_Oferta VARCHAR (50), Korea_Gold VARCHAR (50), Korea_Access VARCHAR (50),"
                        + "Mexico VARCHAR (50), Mexico_Oferta VARCHAR (50), Mexico_Gold VARCHAR (50), Mexico_Access VARCHAR (50),"
                        + "Rusia VARCHAR (50), Rusia_Oferta VARCHAR (50), Rusia_Gold VARCHAR (50), Rusia_Access VARCHAR (50),"                 
                        + "Singapur VARCHAR (50), Singapur_Oferta VARCHAR (50), Singapur_Gold VARCHAR (50), Singapur_Access VARCHAR (50),"                            
                        + "Sudafrica VARCHAR (50), Sudafrica_Oferta VARCHAR (50), Sudafrica_Gold VARCHAR (50), Sudafrica_Access VARCHAR (50),"
                        + "Taiwan VARCHAR (50), Taiwan_Oferta VARCHAR (50), Taiwan_Gold VARCHAR (50), Taiwan_Access VARCHAR (50)"

                + ")");              

        System.out.println( "Tabla creada!");            

        for (Item game : games) {
            String titulo = game.getName();

            boolean isInsert;
            try (PreparedStatement ps = con.prepareStatement("SELECT * FROM info_XboxOne WHERE juego = ?")) {
                ps.setString(1, titulo);

                try (ResultSet rs = ps.executeQuery()) {
                    isInsert = !rs.next();
                }
            }

            if (isInsert) { //si se cumple esta condicción significa que el juego no está incluido, con lo que lo metemos
                try(PreparedStatement ps = con.prepareStatement("INSERT INTO info_XboxOne (Juego, Tipologia, Pertenece, "
                + "Nota, Descripcion_Ingles, Descripcion_Castellano, Pegi,Pegi_USA, Descripcion_Pegi, Codigo_juego, URL,"
                        + "Argentina, Argentina_Oferta, Argentina_Gold, Argentina_Access,"
                        + "Australia, Australia_Oferta, Australia_Gold, Australia_Access,"
                        + "Brasil, Brasil_Oferta, Brasil_Gold, Brasil_Access,"
                        + "Canada, Canada_Oferta, Canada_Gold, Canada_Access,"
                        + "Chile, Chile_Oferta, Chile_Gold, Chile_Access,"
                        + "China, China_Oferta, China_Gold, China_Access,"
                        + "Colombia, Colombia_Oferta, Colombia_Access,"
                        + "Espanya, Espanya_Oferta, Espanya_Gold, Colombia_Access,"
                        + "USA, USA_Oferta, USA_Gold, USA_Access,"
                        + "Hong_Kong, Hong_Kong_Oferta, Hong_Kong_Gold, Hong_Kong_Access,"
                        + "Hungria, Hungria_Oferta, Hungria_Gold, Hungria_Access,"
                        + "India, India_Oferta, India_Gold, India_Access,"
                        + "Inglaterra, Inglaterra_Oferta, Inglaterra_Gold, Inglaterra_Access,"
                        + "Japon, Japon_Oferta, Japon_Gold, Japon_Access,"
                        + "Korea, Korea_Oferta, Korea_Gold, Korea_Access,"
                        + "Mexico, Mexico_Oferta, Mexico_Gold, Mexico_Access,"
                        + "Rusia, Rusia_Oferta, Rusia_Gold, Rusia_Access,"                 
                        + "Singapur, Singapur_Oferta, Singapur_Gold, Singapur_Access,"                            
                        + "Sudafrica, Sudafrica_Oferta, Sudafrica_Gold, Sudafrica_Access,"
                        + "Taiwan, Taiwan_Oferta, Taiwan_Gold, Taiwan_Access"                           
                + ") VALUES (?,?,?,?,?,?,?,?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"                           
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ",?,?,?,?"
                        + ")")) {

                    ps.setString(1,titulo);
                    ps.setString(2,game.getValues().get(Constants.TIPOLOGIA));
                    ps.setString(3,game.getValues().get(Constants.PERTENECE));                                            
                    ps.setString(4,game.getValues().get(Constants.NOTA));
                    ps.setString(5,game.getValues().get(Constants.DESCRIPCION_INGLES));
                    ps.setString(6,game.getValues().get(Constants.DESCRIPCION_CASTELLANO));
                    ps.setString(7,game.getValues().get(Constants.PEGI));
                    ps.setString(8,game.getValues().get(Constants.PEGI_USA));
                    ps.setString(9,game.getValues().get(Constants.DESCRIPCION_PEGI));   
                    ps.setString(10,game.getValues().get(Constants.VALUE_MSSTORE_URL)); 
                    ps.setString(11,game.getValues().get(Constants.URL_JUEGO));
                    int contador = 12;
                    for (Map.Entry<String, String> e: countries.entrySet()) {   
                        ps.setString(contador,game.getValues().get(Constants.PRICE_NORMAL + " "+ e.getKey()));
                        contador++;
                        ps.setString(contador,game.getValues().get(Constants.PRICE_OFFER + " "+ e.getKey()));
                        contador++;
                        ps.setString(contador,game.getValues().get(Constants.PRICE_GOLD + " "+ e.getKey()));
                        contador++;
                        ps.setString(contador,game.getValues().get(Constants.PRICE_ACCESS + " "+ e.getKey()));
                        contador++;
                    }
                    ps.executeUpdate();
                }
            } else {
                String query = "UPDATE info_XboxOne SET Tipologia = ?, Pertenece = ?, "
                + "Nota = ?, Descripcion_Ingles = ?, Descripcion_Castellano = ?, "
                + "Pegi = ?, Pegi_USA = ?, Descripcion_Pegi = ?, juego = ?, URL = ?,"
                        + "Argentina = ?, Argentina_Oferta = ?, Argentina_Gold = ?, Argentina_Access = ?,"
                        + "Australia = ?, Australia_Oferta = ?, Australia_Gold = ?, Australia_Access = ?,"
                        + "Brasil = ?, Brasil_Oferta = ?, Brasil_Gold = ?, Brasil_Access = ?,"
                        + "Canada = ?, Canada_Oferta = ?, Canada_Gold = ?, Canada_Access = ?,"
                        + "Chile = ?, Chile_Oferta = ?, Chile_Gold = ?, Chile_Access = ?,"
                        + "China = ?, China_Oferta = ?, China_Gold = ?, China_Access = ?,"
                        + "Colombia = ?, Colombia_Oferta = ?, Colombia_Gold = ?, Colombia_Access = ?,"
                        + "Espanya = ?, Espanya_Oferta = ?, Espanya_Gold = ?, Espanya_Access = ?,"
                        + "USA = ?, USA_Oferta = ?, USA_Gold = ?, USA_Access = ?,"
                        + "Hong_Kong = ?, Hong_Kong_Oferta = ?, Hong_Kong_Gold = ?, Hong_Kong_Access = ?,"
                        + "Hungria = ?, Hungria_Oferta = ?, Hungria_Gold = ?, Hungria_Access = ?,"
                        + "India = ?, India_Oferta = ?, India_Gold = ?, India_Access = ?,"
                        + "Inglaterra = ?, Inglaterra_Oferta = ?, Inglaterra_Gold = ?, Inglaterra_Access = ?,"
                        + "Japon = ?, Japon_Oferta = ?, Japon_Gold = ?, Japon_Access = ?,"
                        + "Korea = ?, Korea_Oferta = ?, Korea_Gold = ?, Korea_Access = ?,"
                        + "Mexico = ?, Mexico_Oferta = ?, Mexico_Gold = ?, Mexico_Access = ?,"
                        + "Rusia = ?, Rusia_Oferta = ?, Rusia_Gold = ?, Rusia_Access = ?,"                 
                        + "Singapur = ?, Singapur_Oferta = ?, Singapur_Gold = ?, Singapur_Access = ?,"                            
                        + "Sudafrica = ?, Sudafrica_Oferta = ?, Sudafrica_Gold = ?, Sudafrica_Access = ?,"
                        + "Taiwan = ?, Taiwan_Oferta = ?, Taiwan_Gold = ?, Taiwan_Access = ?"                           
                        + " WHERE Codigo_juego = ?";

                try (PreparedStatement ps = con.prepareStatement(query)) {
                    ps.setString(1,game.getValues().get(Constants.TIPOLOGIA));
                    ps.setString(2,game.getValues().get(Constants.PERTENECE));                                            
                    ps.setString(3,game.getValues().get(Constants.NOTA));
                    ps.setString(4,game.getValues().get(Constants.DESCRIPCION_INGLES));
                    ps.setString(5,game.getValues().get(Constants.DESCRIPCION_CASTELLANO));
                    ps.setString(6,game.getValues().get(Constants.PEGI));
                    ps.setString(7,game.getValues().get(Constants.PEGI_USA));
                    ps.setString(8,game.getValues().get(Constants.DESCRIPCION_PEGI));
                    ps.setString(9,titulo);
                    ps.setString(10,game.getValues().get(Constants.URL_JUEGO));
                    ps.setString(91,game.getValues().get(Constants.VALUE_MSSTORE_URL));
                    int contador = 11;
                    for (Map.Entry<String, String> e: countries.entrySet()) {   
                        ps.setString(contador,game.getValues().get(Constants.PRICE_NORMAL + " "+ e.getKey()));
                        contador++;
                        ps.setString(contador,game.getValues().get(Constants.PRICE_OFFER + " "+ e.getKey()));
                        contador++;
                        ps.setString(contador,game.getValues().get(Constants.PRICE_GOLD + " "+ e.getKey()));
                        contador++;
                        ps.setString(contador,game.getValues().get(Constants.PRICE_ACCESS + " "+ e.getKey()));
                        contador++;
                    }
                    ps.executeUpdate();
                }
            }           
}


} catch (Exception e) {
            JOptionPane.showMessageDialog(null, "No se ha podido establecer la conexión con la DB" + e);
            e.printStackTrace();
        }

}

mysql java

asked by JetLagFox 18.03.2017 в 12:49

source

2 answers

0

You may be interpreting the indication you make of PRIMARY KEY as another column in the table:

Instead of:

st.executeUpdate ("CREATE TABLE IF NOT EXISTS info_XboxOne ( id INT AUTO_INCREMENT, PRIMARY KEY (id), Linked_Set VARCHAR (500), ...

Try something like this:

st.executeUpdate("CREATE TABLE IF NOT EXISTS info_XboxOne (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Juego_vinculado VARCHAR(500), ...

And, for debugging more things:

I would add a ; to the end of the SQL statement:

...   + "Taiwan VARCHAR (50), Taiwan_Oferta VARCHAR (50), Taiwan_Gold VARCHAR (50), Taiwan_Access VARCHAR (50)"
                + ");");  
                    ^  aquí

If it is not that, some over or under coma, or some + more or less or some ( or ) more or less.

answered by 18.03.2017 в 13:18

How to print in C # console a value extracted from sql server Create Sticky DIV at the bottom of the screen

score 2 · Accepted Answer

Well, from what I see, the amount of your data inserted is 91 and that of prepared is also 914, on that side there are no problems. But look at this part of your preparedStatement code for the Insert:

+ "China, China_Oferta, China_Gold, China_Access,"
+ "Colombia, Colombia_Oferta, Colombia_Access,"
+ "Espanya, Espanya_Oferta, Espanya_Gold, Colombia_Access,"

First, I see that you missed the Colombia_Gold column. Second, you have duplicated Colombia_Access, one of them must be Espanya_Access