sql server index out of range error

Question

i have this error . The index 2 is out of range. on pst.setString(2, textField.getText(); how can i deal with it.

this is my code

try{
    String sql="update inventory set Name=?,Category=?,Brand=?,Price=?,ExDate=?,Tags=?,Quantity=?,Barcode=? where Id=?"; 
    pst.setString(2, textField.getText());
    pst.setString(3, textField_1.getText());
    pst.setString(4, textField_2.getText());
    pst.setString(5, textField_3.getText());
    pst.setString(6, textField_4.getText());
    pst.setString(7, textField_5.getText());
    pst.setString(8, textField_9.getText());
    pst.setString(9, textField_6.getText());
    pst.setString(1, textField_8.getText());
    pst=con.prepareStatement(sql);
    pst.executeUpdate();

    JOptionPane.showMessageDialog(null,"Updating Item Successful","Updated",JOptionPane.PLAIN_MESSAGE);

    new server().setVisible(true);
    setVisible(false);
} catch(Exception e1){e1.printStackTrace();}

for what its worth, I'd move pst=con.prepareStatement(sql); right after my String declaration (the line String sql = "") and then setter methods. — Ashish
– Ashish, Commented Jan 12, 2014 at 15:56
I don't believe all those columns are really varchar columns. Why do you use setString() on all of them then? You should be using the appropriate setXXX() method to pass the values (e.g. setInt() or setDate()) instead of letting the driver implicitly convert everything. — user330315
– user330315, Commented Jan 12, 2014 at 16:22
@Ashish i just solve my problem. the textField aren't on the right places. thanks for the help. — Rohan21
– Rohan21, Commented Jan 12, 2014 at 16:39
@a_horse_with_no_name you have a point. i'm changing my code right now. :) — Rohan21
– Rohan21, Commented Jan 12, 2014 at 16:39
@Rohan21 , if you found my answer helpful, please do not forget to vote up or accept it. — Ashish
– Ashish, Commented Jan 12, 2014 at 16:41

Ashish · Accepted Answer · 2014-01-12 16:10:12Z

1

Based on the documentation, Couple of things to notice here.

your sql contains name att index 1. So when you try to update this column you should use the same index. something like this:

pst.setString(1, textField.getText());

You are always invoking setString methods. Although it looks like some of the columns might be of type int or else. for example your 'id' column should be set with this:

pst.setInt(9, Integer.parseInt(textField_8.getText()));    (notice that I have changed the index as well.)

And as I mentioned in my comment earlier, you should move your pst=con.prepareStatement(sql) to the top. (right after your string declaration.)

answered Jan 12, 2014 at 16:10

Ashish

1,1193 gold badges15 silver badges25 bronze badges

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

2 Comments

Rohan21 Over a year ago

i have tried it and now i have the error The conversion of the nvarchar value '3565547456644' overflowed an int column.

Ashish Over a year ago

so the idea is to use specific setter methods which corresponds to the data type of a column. you can check out this link to see what could be the best fit in your case. docs.oracle.com/javase/7/docs/api/java/sql/…

elbuild · Accepted Answer · 2014-01-12 16:08:21Z

A prepared statement should be created before being used to set the variables. As the commenter said you need to move the prepared statement creation before setting variables to it.

Refactor your code like this:

try{
    String sql="update inventory set Name=?,Category=?,Brand=?,Price=?,ExDate=?,Tags=?,Quantity=?,Barcode=? where Id=?"; 
    pst=con.prepareStatement(sql);
    pst.setString(2, textField.getText());
    pst.setString(3, textField_1.getText());
    pst.setString(4, textField_2.getText());
    pst.setString(5, textField_3.getText());
    pst.setString(6, textField_4.getText());
    pst.setString(7, textField_5.getText());
    pst.setString(8, textField_9.getText());
    pst.setString(9, textField_6.getText());
    pst.setString(1, textField_8.getText());

    pst.executeUpdate();

    JOptionPane.showMessageDialog(null,"Updating Item Successful","Updated",JOptionPane.PLAIN_MESSAGE);

    new server().setVisible(true);
    setVisible(false);
} 
catch(Exception e1)
{
    e1.printStackTrace();
}
finally 
{
    pst.close();
}

Collectives™ on Stack Overflow

sql server index out of range error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related