Java JDBC Sql error index out of range

Question

Before you ask why there is an object using a setter and getter to add and retrieve data in the database , our instructor told us to this, we should learn JDBC by its root, now I am wondering how come my query won't work? it gives me an index out of range specifically on line 63 and 62

import java.awt.*;
    import java.sql.*;
    import javax.swing.*;
    import java.awt.event.ActionEvent;
    import java.awt.event.ActionListener;

public class Login extends JFrame {

    private JLabel label1, label2;
    private JButton submit;
    private JTextField textfield1;
    private JPasswordField passfield;
    private JPanel panel;

    public Login() {

        super("Log in");
        setSize(300, 100);
        setLocationRelativeTo(null);
        setVisible(true);
        setDefaultCloseOperation(EXIT_ON_CLOSE);

        label1 = new JLabel("User ID:");
        textfield1 = new JTextField(15);
        label2 = new JLabel("Password:");
        passfield = new JPasswordField(15);
        submit = new JButton("Submit");
        panel = new JPanel(new GridLayout(3, 1));

        panel.add(label1);
        panel.add(textfield1);
        panel.add(label2);
        panel.add(passfield);
        panel.add(submit);
        add(panel, BorderLayout.CENTER);

        ButtonHandler handler = new ButtonHandler();
        submit.addActionListener(handler);
    }// end login constructor

    private class ButtonHandler implements ActionListener {

        public void actionPerformed(ActionEvent event) {

            String user = textfield1.getText();
            char[] passChars = passfield.getPassword();
            Connection conn = Jdbc.dbConn();
            PreparedStatement ps = null;
            ResultSet rs = null;
            String pass = new String(passChars);

            User finance  = new Finance();

            finance.setEmpID(user);
            finance.setPassword(pass);

            if (passChars != null) {
                String sql = "SELECT finance_ID, finance_pass FROM  finance_accounts WHERE finance_ID ='"+finance.getEmpID()+"' " +
                        "AND finance_pass = '"+finance.getPassword()+"'";

                try {
                    ps = conn.prepareStatement(sql);
                    ps.setString(1,user);
                    ps.setString(2, pass);
                    rs = ps.executeQuery();
                    if (rs.next()) {
                        JOptionPane.showMessageDialog(null,"Welcome! "+user);
                        dispose();
                    } else {
                        JOptionPane.showMessageDialog(null, "User ID or password is incorrect");
                    }
                } catch (Exception e) {
                        e.printStackTrace();
                } finally {
                    try {
                        rs.close();
                        ps.close();
                        conn.close();
                    } catch (Exception ee) {
                            ee.printStackTrace();
                    }
                }
            }// end actionPerformed
        }// End ButtonHandler
    }// End of class
}

heres my errors

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
    at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3729)
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3713)
    at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4553)
    at Login$ButtonHandler.actionPerformed(Login.java:63)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
    at java.awt.Component.processMouseEvent(Component.java:6288)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
    at java.awt.Component.processEvent(Component.java:6053)
    at java.awt.Container.processEvent(Container.java:2041)
    at java.awt.Component.dispatchEventImpl(Component.java:4651)
    at java.awt.Container.dispatchEventImpl(Container.java:2099)
    at java.awt.Component.dispatchEvent(Component.java:4481)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4577)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
    at java.awt.Container.dispatchEventImpl(Container.java:2085)
    at java.awt.Window.dispatchEventImpl(Window.java:2478)
    at java.awt.Component.dispatchEvent(Component.java:4481)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:643)
    at java.awt.EventQueue.access$000(EventQueue.java:84)
    at java.awt.EventQueue$1.run(EventQueue.java:602)
    at java.awt.EventQueue$1.run(EventQueue.java:600)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
    at java.awt.EventQueue$2.run(EventQueue.java:616)
    at java.awt.EventQueue$2.run(EventQueue.java:614)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:613)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
java.lang.NullPointerException
    at Login$ButtonHandler.actionPerformed(Login.java:76)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
    at java.awt.Component.processMouseEvent(Component.java:6288)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
    at java.awt.Component.processEvent(Component.java:6053)
    at java.awt.Container.processEvent(Container.java:2041)
    at java.awt.Component.dispatchEventImpl(Component.java:4651)
    at java.awt.Container.dispatchEventImpl(Container.java:2099)
    at java.awt.Component.dispatchEvent(Component.java:4481)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4577)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
    at java.awt.Container.dispatchEventImpl(Container.java:2085)
    at java.awt.Window.dispatchEventImpl(Window.java:2478)
    at java.awt.Component.dispatchEvent(Component.java:4481)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:643)
    at java.awt.EventQueue.access$000(EventQueue.java:84)
    at java.awt.EventQueue$1.run(EventQueue.java:602)
    at java.awt.EventQueue$1.run(EventQueue.java:600)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
    at java.awt.EventQueue$2.run(EventQueue.java:616)
    at java.awt.EventQueue$2.run(EventQueue.java:614)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:613)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

Zim-Zam O'Pootertoot · Accepted Answer · 2016-07-26 17:09:43Z

3

The way you use preparedStatement is wrong

The select SQL string should be like this:

String sql = "SELECT finance_ID, finance_pass FROM  finance_accounts WHERE finance_ID = ? AND finance_pass = ?";

When set the prepared statement, you should use ps.setInt(1, user); to set your use id.

edited Jul 26, 2016 at 17:09

Zim-Zam O'Pootertoot

18.2k4 gold badges45 silver badges71 bronze badges

answered Dec 9, 2011 at 3:03

Frank Fang

1,1198 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KyelJmD Over a year ago

so does that mean I won't use my getters??

Frank Fang Over a year ago

You can use it, but not necessary.

KV Prajapati · Accepted Answer · 2011-12-09 03:27:46Z

1

Invalid parameter syntax. Use ? (question mark) as a place holder.

String sql = "SELECT finance_ID, finance_pass FROM  finance_accounts 
            WHERE finance_ID=? and finance_pass=?";

 try {
     ps = conn.prepareStatement(sql);
     ps.setString(1,finance.getEmpID());
     ps.setString(2,finance.getPassword());
     rs = ps.executeQuery();
     ...

edited Dec 9, 2011 at 3:27

answered Dec 9, 2011 at 2:56

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

2 Comments

KyelJmD Over a year ago

so does that mean I won't use my getters?? or I should really use a ? can't I concatenate the variables??

KV Prajapati Over a year ago

@KyelJmD - It depends upon you. You have already used them. Have a look at edited post.

Collectives™ on Stack Overflow

Java JDBC Sql error index out of range

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related