Error in query syntax in Preparestatement

Asked

Viewed 100 times

1

I’m getting this error message and I can’t find why:

You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near '?,? ,? )' at line 1

the code that returns the error is this:

    public void adicionar() {
        String sql = "INSERT INTO genius.produtos_comissao_extra (Id_produto, Data_abertura, Valor) VALUES (?,?,?)";

        if (!txtValor.getText().equals("")) {

            try {
                pst = conexao.prepareStatement(sql);
                // passando o conteúdo dos calendarios para o "?"
                pst.setString(1, lblId.getText());
                Date data = new Date(System.currentTimeMillis()); // data atual
                String d = data.toString();
                pst.setString(2, d);
                pst.setString(3, txtValor.getText());
                pst.execute(sql);
                JOptionPane.showMessageDialog(null, "Comissão extra inserida no produto");

            } catch (Exception e) {
                JOptionPane.showMessageDialog(null, e);
            }  

        } else { 
            JOptionPane.showMessageDialog(null, "Informe o valor");

        }
    }

The schema of the table is this:

inserir a descrição da imagem aqui

java mysql

  • If the 3 fields in your Insert table are not string, you will get an error. You are sure that Id_produto and Data_abertura are strings and not, respectively, int and datetime?

  • they were yes int and datetime, but even changing the two to scan the continued problem, I tried also using pst.setInt and pst.setDate and the problem continued

  • So some field is misnamed, or the path to your table.

  • i tested first on Mysql and worked with the code: INSERT INTO Genius.products_commission_extra (Id_product, Open Data_value) VALUES (2,'2016-09-06',5.3); I couldn’t find the difference

  • Add the schema of your table(name, fields and their types) to the question.

  • this ai diegofm

Show 1 more comment

1 answer

3


Try the following:

public void adicionar() {
    String sql = "INSERT INTO genius.produtos_comissao_extra (Id_produto, Data_abertura, Valor) VALUES (?,?,?)";

    if (!txtValor.getText().equals("")) {

        try {
            pst = conexao.prepareStatement(sql);
            // passando o conteúdo dos calendarios para o "?"
            pst.setInt(1, Integer.parseInt(lblId.getText()));
            java.sql.Date data = new java.sql.Date(System.currentTimeMillis()); // data atual
            pst.setDate(2, data);
            pst.setString(3, txtValor.getText());
            pst.executeUpdate();
            JOptionPane.showMessageDialog(null, "Comissão extra inserida no produto");

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }  

    } else { 
        JOptionPane.showMessageDialog(null, "Informe o valor");

    }
}

The problem is that Id_produto expects a whole, and Data_abertura expecting a Date, and you are passing everything as string. What I did in this code was to convert the lblId.getText() for int and take the current date in the format in which the PrepareStatement wait (the bank is expecting a guy sql. Date and not a util. Date). And how it comes to a Insert, you can call pst.executeUpdate();, if no return is required.

Note: Note the fact that the field lblId.gettext() need to have a value that can be converted to integer, any value other than that(e.g.: blank string, null or digit mixed with character) will pop one NumberFormatException.

  • 1

    Man, it worked right, thank you very much...

Browser other questions tagged java mysql

You are not signed in. Login or sign up in order to post.