Error Parameter index out of range (1> number of Parameters which is 0)

Asked

Viewed 114 times

0

I’m studying JDBC and learning how the resultset and statement, did first the method of adding and changing data with the statement that worked perfectly

private void jBtnGravarActionPerformed(java.awt.event.ActionEvent evt) {
    String Produto = jTextNomeProduto.getText();
    String Marca = jTextMarca.getText();
    String Estoque = jTextEstoque.getText();
    String Preco = jTextPreço.getText();
    String Fornecedor = jTextFornecedor.getText();
    String command;
    String msg;
            
    try {
        if(jLblCodl.getText().equals("")){
            command = "insert into produtos (NProduto, Marca , Estoque , Preco, Fornecedor) values ('" + Produto + "','" + Marca + "','" + Estoque + "','" + Preco + "','" + Fornecedor + "')";
            msg = "Gravação de novo registro realizada com sucesso";
        } else { 
            command = "update produtos set NProduto ='" + Produto + "'," + "Marca = '" + Marca + "'," + "Estoque  = '" + Estoque + "'," + "Preco = '" + Preco + "'," + "Fornecedor = '" + Fornecedor + "' where Cod = " + jLblCodl.getText();
            msg = "Alteração realizada com sucesso";
        }
        con_produtos.statement.executeUpdate(command);
        JOptionPane.showMessageDialog(null, msg );
    } catch(SQLException err) {
        JOptionPane.showMessageDialog(null, "\nErro na gravação!\nErro: " + err);
    }
    position();
}

After that I tried to use the Preparedstatement and without using concatenation in sql commands:

private void jBtnGravarActionPerformed(java.awt.event.ActionEvent evt) {
    String sql;
    String msg;
    try {
        if(jLblCodl.getText().equals("")){
            sql = "insert into produtos (NProduto, Marca , Estoque , Preco, Fornecedor) values (?,?,?,?,?);";
            msg = "Gravação de novo registro realizada com sucesso";
        } else { 
            sql = "update produtos set NProduto = ?, Marca = ?, Estoque = ?, Preco = ?, Fornecedor = ? Where Cod = ? ;";
            msg = "Alteração realizada com sucesso";
        }
        con_produtos.statement.setString(1, jTextNomeProduto.getText());
        con_produtos.statement.setString(2,  jTextMarca.getText());
        con_produtos.statement.setInt(3, Integer.parseInt(jTextEstoque.getText()) );
        con_produtos.statement.setDouble(4, Double.valueOf(jTextPreço.getText()));
        con_produtos.statement.setString(5, jTextFornecedor.getText());
        System.out.println();
        con_produtos.statement.executeUpdate(sql);
        JOptionPane.showMessageDialog(null, msg );
    } catch(SQLException err) {
        JOptionPane.showMessageDialog(null, "\nErro na gravação!\nErro: " + err);
    }
    con_produtos.execSQL("select * from produtos order by Cod");
    position();
}

But if I run it returns the error Parameter index out of range (1> number of Parameters which is 0)

public void execSQL(String sql) {
    try {
        statement = conec.prepareStatement(sql);
        rs = statement.executeQuery();
    } catch (SQLException excecao) {
        JOptionPane.showMessageDialog(null, "\nErro no comando SQL!\nErro: " + excecao + "\nComando SQL passado: " + sql);
    }    
}

This is the method where you run the query On the missing part of the statement I did as follows About missing the statement statement I declared as follows

publicJFrameIndex() {
        initComponents();
        con_produtos = new Conectar();
        con_produtos.getConnection();
        con_produtos.execSQL("select * from produtos order by Cod"); 
}
  • 1

    In his Listener you’re making Binding of parameters in con_produtos.statement, but your code is not showing where this statement is being created. What is most likely happening is that the PreparedStatement is not being initialized correctly. That is, it is missing a con_produtos.statement = conec.prepareStatement(sql) before the first con_produtos.statement.setString.

  • 1

    Another important point that is beyond the scope of the question is that tying UI code with the bank access code is bad practice. To decouple the code is worth entering a Repository or DAO.

  • the statement startup you refer to is the last block I added above? I will also study on DAO, I am using the code with ui only for testing and study

  • I’ve done several tests and I don’t see the error anywhere, that’s the problem of being beginner in hehe programming

  • Hi Gabriel. No, what I meant is the code that initializes the statement. The problem is that the code is incomplete (read more about creating a MVCE). Have you ever tried to include con_produtos.statement = conec.prepareStatement(sql) on the line before con_produtos.statement.setString(1, jTextNomeProduto.getText()); as my comment above?

  • I understood now what I meant, tried el it returns me the following error: 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

  • I added simple quotes between the values getting like this values ('?','?','?','?','?') and he returns to me the error Parameter index out of range (1> number of Parameters which is 0)

  • Again, the best line of action to debug is to build an MVCE that I can run to reproduce the problem (otherwise the process of Troubleshooting becomes a guessing game without much benefit to the community as a whole). The first error message means that the parameters Binding is not working. I believe the problem is the call overloaded with String sql in con_produtos.statement.executeUpdate(sql). Instead, run the call without parameters con_produtos.statement.executeUpdate(), so it will use the query prepared with the parameters loaded.

  • I started from scratch and with fewer entries, only 2 fields and gave the same error so I removed the string sql of con_produtos.statement.executeUpdate(), solved the problem, looked for why this happened and found that when using the string with the SQL command in prepareStatement just use the executeUpdate no parameters that will already work the register. Thank you so much for your help. Now I will seek to know about DAO as you suggested

Show 4 more comments
No answers

Browser other questions tagged

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