Error: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range

Question

I have a function to update the user's information as follows:

public void updateAccount(String username, String name, String address, String aboutMe, String 
id) {
    String sql = "update Account set username = '?', \n"
            + "                [Full_Name] = '?',\n"
            + "                [Address] = '?',\n"
            + "                [about_me] = '?'\n"
            + "                where id = ?";
    try {
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, username);
        ps.setString(2, name);
        ps.setString(3, address);
        ps.setString(4, aboutMe);
        ps.setString(5, id);
        ps.executeUpdate();

    } catch (Exception ex) {
        Logger.getLogger(AccountDao.class.getName()).log(Level.SEVERE, null, ex);
    }
}

and this code is giving me an error like this: 

Severe: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:933) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:948) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setString(SQLServerPreparedStatement.java:1578) at dao.AccountDao.updateAccount(AccountDao.java:117) at controller.UserProfileController.doPost(UserProfileController.java:91)

I don't understand why it gives me the error "The index 2 is out of range" and is there any way to fix it?

You only have one parameter, the rest are string literals with a question mark in them. — Mark Rotteveel
– Mark Rotteveel, Commented Jun 14, 2022 at 18:02

Dan Guzman · Accepted Answer · 2022-06-14 14:39:51Z

1

Don't enclose ? parameter markers in quotes. These are typed by the appropriate setter:

String sql = "update Account set username = ?, \n"
        + "                [Full_Name] = ?,\n"
        + "                [Address] = ?,\n"
        + "                [about_me] = ?\n"
        + "                where id = ?";

answered Jun 14, 2022 at 14:39

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

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

2 Comments

Bích Mai Vũ Over a year ago

Your answer is really helpful, but I have 1 more problem after fixing it. That is, I still cannot update the user's information with the following error: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'username', table 'FERA_ONL_LEARNING.dbo.Account'; column does not allow nulls. UPDATE fails. I'm sure I changed their values and it can't be NULL. Do you know how to fix it?

Dan Guzman Over a year ago

I would expect ps.setString(1, username); to set the parameter value accordingly. If you see a non-NULL value when debugging, you could run a server-side trace of the RPC call to capture the actual values sent.

Collectives™ on Stack Overflow

Error: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related