java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0) Java client

Question

Give that error but I don't understand why,I've used ? as values and there are 3 parameters. I have to add a new User to db,don't watch about password in a TextField I transform in a passwordField then.

private void caricaActionPerformed(java.awt.event.ActionEvent evt) {                                       
    String username = null,ruolo=null,password;
    username = usernameField.getText();
    password = passwordField.getText();
    ruolo = ruoloField.getText();
    try {
        aggiungiUtente(username,password,ruolo);
    } catch (SQLException ex) {
        Logger.getLogger(InserisciUtente.class.getName()).log(Level.SEVERE, null, ex);
    } 
}                                      
private void aggiungiUtente(String username,String password,String ruolo) throws SQLException {
    Connection conn;
    String url="jdbc:mysql://localhost:3306/gestionale",user="root",psw="141299Fm";
    conn = (Connection) DriverManager.getConnection(url,user,psw);
    conn.setAutoCommit(false);
    String sql= "insert into Utenti('username',password','ruolo') VALUES(?,?,?)";
    try{
    PreparedStatement st = conn.prepareStatement(sql);
    st.setString(1, username);
    st.setString(2, password);
    st.setString(3, ruolo);

    ResultSet rs = st.executeQuery();

    System.out.println(rs);
    }catch(SQLException e)
    {
        Logger.getLogger(InserisciUtente.class.getName()).log(Level.SEVERE, null, e);
    }


}

    run:
apr 09, 2017 10:38:51 PM InserisciUtente aggiungiUtente
GRAVE: null
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
    at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3813)
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3795)
    at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4616)
    at InserisciUtente.aggiungiUtente(InserisciUtente.java:136)
    at InserisciUtente.caricaActionPerformed(InserisciUtente.java:123)
    at InserisciUtente.access$000(InserisciUtente.java:20)
    at InserisciUtente$1.actionPerformed(InserisciUtente.java:63)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6533)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
    at java.awt.Component.processEvent(Component.java:6298)
    at java.awt.Container.processEvent(Container.java:2236)
    at java.awt.Component.dispatchEventImpl(Component.java:4889)
    at java.awt.Container.dispatchEventImpl(Container.java:2294)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
    at java.awt.Container.dispatchEventImpl(Container.java:2280)
    at java.awt.Window.dispatchEventImpl(Window.java:2746)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
    at java.awt.EventQueue.access$500(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:709)
    at java.awt.EventQueue$3.run(EventQueue.java:703)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:90)
    at java.awt.EventQueue$4.run(EventQueue.java:731)
    at java.awt.EventQueue$4.run(EventQueue.java:729)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:109)
    at java.awt.WaitDispatchSupport$2.run(WaitDispatchSupport.java:184)
    at java.awt.WaitDispatchSupport$4.run(WaitDispatchSupport.java:229)
    at java.awt.WaitDispatchSupport$4.run(WaitDispatchSupport.java:227)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.awt.WaitDispatchSupport.enter(WaitDispatchSupport.java:227)
    at java.awt.Dialog.show(Dialog.java:1084)
    at java.awt.Component.show(Component.java:1671)
    at java.awt.Component.setVisible(Component.java:1623)
    at java.awt.Window.setVisible(Window.java:1014)
    at java.awt.Dialog.setVisible(Dialog.java:1005)
    at FormUtenti.jButton1ActionPerformed(FormUtenti.java:104)
    at FormUtenti.access$000(FormUtenti.java:11)
    at FormUtenti$2.actionPerformed(FormUtenti.java:64)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6533)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
    at java.awt.Component.processEvent(Component.java:6298)
    at java.awt.Container.processEvent(Container.java:2236)
    at java.awt.Component.dispatchEventImpl(Component.java:4889)
    at java.awt.Container.dispatchEventImpl(Container.java:2294)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
    at java.awt.Container.dispatchEventImpl(Container.java:2280)
    at java.awt.Window.dispatchEventImpl(Window.java:2746)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
    at java.awt.EventQueue.access$500(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:709)
    at java.awt.EventQueue$3.run(EventQueue.java:703)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:90)
    at java.awt.EventQueue$4.run(EventQueue.java:731)
    at java.awt.EventQueue$4.run(EventQueue.java:729)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:109)
    at java.awt.WaitDispatchSupport$2.run(WaitDispatchSupport.java:184)
    at java.awt.WaitDispatchSupport$4.run(WaitDispatchSupport.java:229)
    at java.awt.WaitDispatchSupport$4.run(WaitDispatchSupport.java:227)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.awt.WaitDispatchSupport.enter(WaitDispatchSupport.java:227)
    at java.awt.Dialog.show(Dialog.java:1084)
    at java.awt.Component.show(Component.java:1671)
    at java.awt.Component.setVisible(Component.java:1623)
    at java.awt.Window.setVisible(Window.java:1014)
    at java.awt.Dialog.setVisible(Dialog.java:1005)
    at FrameMenu.pulsanteUtentiActionPerformed(FrameMenu.java:155)
    at FrameMenu.access$200(FrameMenu.java:11)
    at FrameMenu$3.actionPerformed(FrameMenu.java:65)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6533)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
    at java.awt.Component.processEvent(Component.java:6298)
    at java.awt.Container.processEvent(Container.java:2236)
    at java.awt.Component.dispatchEventImpl(Component.java:4889)
    at java.awt.Container.dispatchEventImpl(Container.java:2294)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
    at java.awt.Container.dispatchEventImpl(Container.java:2280)
    at java.awt.Window.dispatchEventImpl(Window.java:2746)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
    at java.awt.EventQueue.access$500(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:709)
    at java.awt.EventQueue$3.run(EventQueue.java:703)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:90)
    at java.awt.EventQueue$4.run(EventQueue.java:731)
    at java.awt.EventQueue$4.run(EventQueue.java:729)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

**Stack Trace **

You are missing a ' in the INSERT. And probably you don't need the ' anyway. — SantiBailors, Apr 09 '17 at 21:21

Pooja Arora · Answer 1 · 2017-04-10T06:16:16.027

1

The quotations in your Insert statement attributes are not properly closed. There is no starting quote for password.

Change the Insert Statement from

String sql= "insert into Utenti('username',password','ruolo') VALUES(?,?,?)";

to

String sql= "insert into Utenti(username,password,ruolo) VALUES(?,?,?)";

No need to give ' while specifying the attributes in Insert statement.

edited Apr 10 '17 at 06:16

answered Apr 09 '17 at 21:18

Pooja Arora

620
7
19

1

*"inverted commas"*? Do you mean the [apostrophes](http://www.fileformat.info/info/unicode/char/0027/index.htm), aka single quotation mark, i.e. `'`? – Andreas Apr 09 '17 at 21:33
It isn't just *"**No need** to give `'`"*. You **cannot** give `'`, because those are used for character literals, e.g. `'Hello'`, and the `INSERT` statement needs column *names*, not *character literals*. – Andreas Apr 09 '17 at 21:37
1

@Andreas That is what I am saying, attributes are not specified using quotes – Pooja Arora Apr 10 '17 at 06:17
"No need" means that it is optional, e.g. "you don't need to, but you can". That is not the case here, so saying "no need" is inaccurate phrasing. – Andreas Apr 10 '17 at 14:45
@Andreas You are being too assertive and insisting without quoting any source. What happens in your opinion when the column name has spaces? See [an example](http://stackoverflow.com/a/14190833/2814308). Saying _You cannot give ', because those are used for character literals_ is plain wrong. – SantiBailors Apr 11 '17 at 07:03
@SantiBailors If saying the *truth* is being "too assertive", then you must not like the truth a lot. The ANSI SQL standard uses `'` (single-quote) for character literals. and `"` (double-quote) for quoting names. Various RDBMS implementations use other characters for quoting names, such as `\`` (backtick) for MySQL, or `[]` (square-bracket) for MSSQL, but they all agree on single-quotes for character literals. Look at your example again, and notice those are **backticks**, not single-quotes. *Example* source: [MySQL](https://dev.mysql.com/doc/refman/5.7/en/string-literals.html) – Andreas Apr 11 '17 at 08:24
You weren't too assertive for someone who is wrong but for someone who kept not citing any source, as I wrote. So it becomes less important if what you say is true or not because people would have to take your word for it. That's why here people often cite sources. But you're right, my link is wrong for my case, and I was wrong about single quotes. – SantiBailors Apr 11 '17 at 09:37
@Andreas Apparently there are cases where some DBMS does allow single quotes around identifiers, according to the top comment to [this answer](http://stackoverflow.com/a/2901502/2814308). But I'm aware that those are corner cases, I'm pointing that out only because it's a good way to illustrate my "too assertive for no sources" point and to give to your expression _"the truth"_ a more relative tone than the one it has. Except for such quite irrelevant corner cases you're still right. – SantiBailors Apr 11 '17 at 10:07
@SantiBailors Wow, you're gonna get really disappointed if you think all comments must link to source. All major SQL languages (Oracle, MS SQL, PostgreSQL, MySQL) agree that single-quote is used for character literals. [Even SQLite](https://sqlite.org/lang_expr.html) ( – Andreas Apr 11 '17 at 14:44
@Andreas Who said that _**all** comments must link to source_ ? Me? My point was that if you are going to be so assertive and just repeat the same absolute statement it would help to link to a source. As you can see, your _truth_ is not true in 100% of cases. Nice to quote SQLlte doc and thanks for pointing out that it's a link to source, but it should be added that the same source also [says](http://www.sqlite.org/lang_keywords.html) that that's not always the case. Anyway I can tell that this is going to a wrong and useless direction, so I'll stop here. – SantiBailors Apr 11 '17 at 15:09

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0) Java client

1 Answers1