Error in query syntax in PrepareStatement

Question

Error in query syntax in PrepareStatement

Navigation

#1 by (3 votes)

1

I'm getting this error message and can not find why:

You have an error in your SQL syntax; check the manual that correspond 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 table schema is this:

mysql java

asked by anonymous 07.09.2016 / 15:08

1 answer

Variable as Java method argument Doubt over TFS version control terms

score 3 · Accepted Answer

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 an integer, and Data_abertura expects Date , and you are passing everything as a string. What I did in that code was to convert lblId.getText() to int and get the current date in the format where PrepareStatement waits (the database waits for a type sql.Date and not a util.Date ). And since it is an insert , you can call pst.executeUpdate(); , if you do not need any return.

Note: Note that the lblId.getText () field must have a value that can be converted to integer, any value other than that (eg blank string, null or mixed digit with character) will burst a NumberFormatException .