SQL Exception: the index is out of range

Question

When I run my program, it show the error the column is out of range. Connection is ok

For exmaple

updateRecoredtoStudent(15, "Annies","Bot"," Ionia", "1/1/2013","firstname","Anny")

 private static void updateRecordToStudent(int studentid, String firstname, String lastname,String address, String dateofbirth, String cond_col, String cond_val) throws SQLException {

    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;

    String updateTableSQL = "UPDATE student SET 'studentid' = ? 'firstname' = ? 'lastname' = ? 'address' = ? dateofbirth' = ? WHERE ? = ?";
    try {
        dbConnection = getDBConnection();
        preparedStatement = dbConnection.prepareStatement(updateTableSQL);

                    preparedStatement.setInt(1, studentid); 
        preparedStatement.setString(2, firstname); 
                    preparedStatement.setString(3, lastname);
                    preparedStatement.setString(4, address); 
                    preparedStatement.setString(5, dateofbirth);
                    preparedStatement.setString(6, cond_col);
                    preparedStatement.setString(7, cond_val);


        // execute update SQL stetement
        preparedStatement.executeUpdate();

        System.out.println("Record is updated to STUDENT table!");

    } catch (SQLException e) {

        System.out.println(e.getMessage());

    } finally {

        if (preparedStatement != null) {
            preparedStatement.close();
        }

        if (dbConnection != null) {
            dbConnection.close();
        }

    }

}

 Student table contains
-studentid(int-PK)
-Firstname(String)
-lastname(String)
-Address(String)
-Dateofbirth(String)

You have typos (missing quotes). You also have wrong indices (multiple times 5) — cghislai, Sep 27 '15 at 11:33
You can not have column names as a parameter in a prepared statement - http://stackoverflow.com/questions/20008336/having-a-column-name-as-input-parameter-of-a-preparedstatement — Sam Segers, Sep 27 '15 at 11:35
`SET 'studentid' = ?` is invalid SQL. Column names need to be enclosed in double quotes, not single quotes (or no quotes at all) — , Sep 27 '15 at 11:46

SatyaTNV · Answer 1 · 2015-09-27T11:45:42.263

1

String updateTableSQL = "UPDATE student SET studentid = ?, firstname = ?, lastname = ?, address = ?, dateofbirth = ? WHERE "+cond_col+" = ?";
preparedStatement.setInt(1, studentid); 
preparedStatement.setString(2, firstname); 
preparedStatement.setString(3, lastname);
preparedStatement.setString(4, address); 
preparedStatement.setString(5, dateofbirth);
preparedStatement.setString(6, cond_val);

Only column values can be set. If you want to pass dynamic column name you should concat it with query. One more thing missing , between column name.

edited Sep 27 '15 at 11:45

answered Sep 27 '15 at 11:42

SatyaTNV

4,137
3
15
31

1

You also need to remove the `'`s around column names. Otherwise you're trying to assign values to strings, which won't work. – Luke Woodward Sep 27 '15 at 11:44
Ok I will try it .Thanks for yor help – ʚïɞ Gemini ʚïɞ Sep 27 '15 at 11:47

score 0 · Accepted Answer · edited Sep 27 '15 at 12:06

0

I think you wrong code statement. Please try the following.

String updateTableSQL = "UPDATE student SET 'studentid' = ? 'firstname' = ? 'lastname' = ? 'address' = ? dateofbirth' = ?  WHERE " + cond_col + "= ?";
try {
    dbConnection = getDBConnection();
    preparedStatement = dbConnection.prepareStatement(updateTableSQL);

    preparedStatement.setInt(1, studentid); 
    preparedStatement.setString(2, firstname); 
    preparedStatement.setString(3, lastname);
    preparedStatement.setString(4, address); 
    preparedStatement.setString(5, dateofbirth);
    preparedStatement.setString(6, cond_val);

edited Sep 27 '15 at 12:06

Tom

16,842
17
45
54

answered Sep 27 '15 at 11:39

T8Z

691
7
17

I chek that but all input is correct with correspoding limiit in table – ʚïɞ Gemini ʚïɞ Sep 27 '15 at 11:41
I think the wrong thing in the sql query, maybe in preparedstatement – ʚïɞ Gemini ʚïɞ Sep 27 '15 at 11:43

score 0 · Answer 3 · answered Sep 27 '15 at 11:47

Change your SQL syntax to be like this:

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

So your SQL UPDATE query will be:

String updateTableSQL = "UPDATE student SET studentid=?, firstname=?, lastname=?, address=?, dateofbirth=? WHERE " + fieldName + " = ?";

SQL Exception: the index is out of range

3 Answers3