Invalid argument in JDBC call: parameter index out of range (Using Ucanaccess)

Question

I am creating a game which contains a leveling system from java. So far I have been using Ucanaccess to read the user's current level and experience (0-100).

I am writing a code that uses an if statement, stating that when the user's experience bar is at 100 or over 100, it will select the user's current level and they will level up by one level, as well as deducting the extra experience points over 100 and transfer into the new level. (If exp = 129, new exp = 129-100).

Here is my code with the if statement:

if (exp >= 100)
{
    try
    {
        Connection conn = DriverManager.getConnection("jdbc:ucanaccess://C:\\Users\\evanc\\Documents\\NetBeansProjects\\IT PAT task\\IT PAT DataBase\\userDB.accdb");
        String query = "SELECT * FROM [tblUser] WHERE username = " + "'" + User.getInstance().username + "'";
        PreparedStatement pst = conn.prepareStatement(query);
        ResultSet rs = pst.executeQuery();
        rs.next();
        String newLevel = Integer.toString(User.getInstance().level + 1) ;
        String updateQuery = "UPDATE tblUser SET (level) = (?) WHERE username=" + "'" + rs.getString("username") + "'";

        PreparedStatement st = conn.prepareStatement(updateQuery);
        st.setString(5,newLevel);
        st.executeUpdate();
        lblLevel.setText("lvl: " + Integer.toString(User.getInstance().level));

        int newExp = exp - 100;
        exp = newExp;
        exp = User.getInstance().experience;
        expBar.setValue(User.getInstance().experience);    
    }
    catch(Exception e)
    {
        System.err.println("Exception occured: ");
        System.err.println(e.getMessage());   
    }

    JOptionPane.showMessageDialog(null, "Congratulations! You leveled up!");
}

And here is the error that it showed: "Exception occurred: UCAExc:::4.0.4 Invalid argument in JDBC call: parameter index out of range: 5"

I am trying to update the 5th column of my database, which is called "level".

I would be grateful for any help!

Answer 1

First: you had an extra } in your code, I edited your question and removed it.

Second, the real question.

Your query is:

String updateQuery = "UPDATE tblUser SET (level) = (?) WHERE username=" + "'" + rs.getString("username") + "'";

And then you prepare it:

PreparedStatement st = conn.prepareStatement(updateQuery);

So once prepared, object st has 1 argument available. The value that will replace the ? mark in the query. st does not know that the replacement for ? is actually the 5th element in the database. From it's point of view, there is only 1 argument it is waiting for.

When you want to set a value to the ? in the query, it is therefore the no.1 field. So your code should be:

st.setString(1,newLevel);

See this page: https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html It shows you an example of prepared statement with more than one ?. You will see two ? in the query. So they must set the value of 1 and 2.

---

Another point, I do not understand why you take the (level + 1) and convert it to a string. You can use st.setInt(1, newLevel) and keep it all numbers. Your call. Just ensure the data type of the field in the database is an integer also.