Registration problem (Column Count doesn’t match value Count at Row 1)

Question

I am having the error "Column Count doesn’t match value Count at Row 1" when I try to register. I already checked my comic book and everything seems to be in order but nothing to solve this mistake.

My registration code.

            public void actionPerformed(java.awt.event.ActionEvent e) {
            System.out.println("actionPerformed()"); 
            // captura valor de radiobuttons
            String consignacao;
            if (consig_sim.isSelected()) {
                consignacao = consig_sim.getText();
            } else {
                consignacao = consig_nao.getText();
            }
            // /fim da captura
            String Cadastro = ("insert into livros (ISBN, titulo_livro, autor_livro, editora_livro, consignacao, preco, quantidade) values ('"
                    + jTextField.getText()
                    + ", "
                    + jTextField1.getText()
                    + ","
                    + jTextField2.getText()
                    + ", "
                    + jTextField3.getText()
                    + ", "
                    + consignacao
                    + ", "
                    + jTextField4.getText()
                    + ", "
                    + jTextField5.getText() + "')");
            try {
                cadastro.Executar("select * from livros");
                cadastro.stm.executeUpdate(Cadastro); //de acordo com o eclipse o erro esta acontecendo aqui
                cadastro.stm.close();
                cadastro.Executar("select * from livros");
                JOptionPane
                        .showMessageDialog(null, "Cadastro efetuado");
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        }

query class.

package modulo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Query {
public static Object resultset;
public Statement stm;
public ResultSet Resultado;
private boolean conectado = false;

static Conexao data_base = new Conexao();

public ResultSet Executar(String Sql) {

    try {
        if (!conectado) {
            data_base.conectar();
            conectado = true;
        }
        stm = data_base.con.createStatement();
        Resultado = stm.executeQuery(Sql);
    } catch (SQLException ex) {
    }
    return Resultado;
}

}

Answer 1

You delimited all query variables with a single pair of single quotes.

String Cadastro = ("
    insert into livros (
        ISBN,
        titulo_livro,
        autor_livro,
        editora_livro,
        consignacao,
        preco,
        quantidade)
    values ('" +
        jTextField.getText() + ", " +
        jTextField1.getText() + "," +
        jTextField2.getText() + ", " +
        jTextField3.getText() + ", " +
        consignacao + ", " +
        jTextField4.getText() + ", " +
        jTextField5.getText() + "')");

To correct, leave so:

String Cadastro = ("
    insert into livros (
        ISBN,
        titulo_livro,
        autor_livro,
        editora_livro,
        consignacao,
        preco,
        quantidade)
    values ('" +
        jTextField.getText() + "', '" +
        jTextField1.getText() + "', '" +
        jTextField2.getText() + "', '" +
        jTextField3.getText() + "', '" +
        consignacao + "', '" +
        jTextField4.getText() + "', '" +
        jTextField5.getText() + "')");

Answer 2

To be correct the code needs to take into account the parameters, because sometimes comes special characters, as the apostrophe '
Take a look at Java SQL Parameters

ALL OVER: Mysql Parameters

To 'make it work', try changing your code to:

String Cadastro = ("INSERT INTO livros (ISBN, titulo_livro, autor_livro, editora_livro, consignacao, preco, quantidade) VALUES ('"
                + jTextField.getText().replace("'","''")
                + "', '"
                + jTextField1.getText().replace("'","''")
                + "','"
                + jTextField2.getText().replace("'","''")
                + "','"
                + jTextField3.getText().replace("'","''")
                + "','"
                + consignacao.replace("'","''")
                + "','"
                + jTextField4.getText().replace("'","''")
                + "','"
                + jTextField5.getText().replace("'","''") + "')");

Answer 3

The error says that the number of columns is different from the number of values passed in the clause VALUES. The way the code is there are 7 columns and only one value, because only a single quote was placed at the beginning of sql and another at the end.

INICIO--v
values ('"+ jTextField.getText()+ ", "+jTextField1.getText()+","+ jTextField2.getText()
          + ", "+ jTextField3.getText()+ ", "+ consignacao+ ", "+ jTextField4.getText()
          + ", "+ jTextField5.getText() + "')");
                                           ^------FIM 

To prevent your code against sql Injection attacks and not get lost in this sea of commas simple quotes and make sql simpler use prepares statements.

String Cadastro = "INSERT INTO livros (ISBN, titulo_livro, autor_livro, 
editora_livro,"+ "consignacao, preco, quantidade) VALUES (?,?,?,?,?,?,?)";

PreparedStatement stmt = cadastro.prepareStatement(Cadastro); 
stmt.setString(1, jTextField.getText());
stmt.setString(2, jTextField1.getText());
stmt.setString(3, jTextField2.getText());
stmt.setString(4, jTextField3.getText());
stmt.setString(5, consignacao);
stmt.setString(6, jTextField4.getText());
stmt.setString(7, jTextField5.getText());

stmt.execute()

Answer 4

Guys I managed to solve the problem here, my DAO class was sending inside the method add the following string "String sql = "Insert into contacts values (?,?,??)";" changed to "String sql = "Insert into contacts (name,email,address,dataNascimento)values (?,?,?)";", basically the problem was related to the object of the java.sql.Date class

package br.com.caelum.servlet.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import br.com.caelum.servlet.jdbc.ConnectionFactory;
import br.com.caelum.servlet.modelo.Contatos;

public class ContatosDAO {

    private Connection con;

    public ContatosDAO(){
        con = new ConnectionFactory().getConnection();
    }

    public void adicionar(Contatos contato) {

        String sql = "insert into contatos (nome,email,endereco,dataNascimento)values (?,?,?,?)";
        try {

            PreparedStatement ps = con.prepareStatement(sql);

            ps.setString(1, contato.getNome());
            ps.setString(2, contato.getEmail());
            ps.setString(3, contato.getEndereco());
            ps.setDate(4, new Date(contato.getDataNascimento().getTimeInMillis()));
            ps.execute();
            ps.close();

        } catch (SQLException e) {
            System.out.println(e);
            throw new RuntimeException();
        } 

    }
}

Answer 5

It happened the same to me and after many tests I discovered an error in Trigger (after Insert) of the table in question, after correcting the Trigger error this error no more happened.