update statement in Java

Question

I have the following code to update a record. The code compiles however it skips everything in the try statement and displays the error message in the catch statement. I am not sure what it is that I am missing as it doesn't display any sort of syntax error.

try {           
    PreparedStatement st = db.con.prepareStatement("UPDATE item SET Name = ?, Size = ?, Price = ?, WHERE ItemCode = ?");
    st.setString(1, textArea_Code.getText());
    st.setString(2, textArea_name.getText());
    st.setString(3, textArea_size.getText());
    st.setString(4, textArea_price.getText());
    st.executeUpdate();

    JOptionPane.showMessageDialog(frame, "Updated");    

} catch (SQLException e ) {
    JOptionPane.showMessageDialog(frame, "update not successful");      
}

Anthony Grist · Accepted Answer · 2012-03-09 19:09:48Z

12

You're swallowing the exception, which is generally a bad idea. At the very least call e.printStackTrace() so that you have an output of the exception.

As it happens, you have a syntax error in your SQL statement: UPDATE item SET Name = ?, Size = ?, Price = ?, WHERE ItemCode = ? - remove the comma from after Price = ?.

To address the confusion about why the UPDATE statement still doesn't work, despite fixing the syntax error, allow me to explain in more detail (far easier to do this in the answer, rather than in comments).

The ? character in your SQL String is a placeholder for a value that you'll set with one of the various set_() methods (in your case, only ever setString(). Each placeholder is numbered with an index starting from 1 - the first ? that appears in your string represents index 1, the second represents index 2, etc.

Your SQL string looks like this:

UPDATE item SET Name = ?, // 1
Size = ?, // 2
Price = ? // 3
WHERE ItemCode = ? // 4

You're setting values for your placeholders like this:

st.setString(1, textArea_Code.getText()); // ItemCode is fourth in the SQL, should be 4
st.setString(2, textArea_name.getText()); // Name is first in the SQL, should be 1
st.setString(3, textArea_size.getText()); // Size is second in the SQL, should be 2
st.setString(4, textArea_price.getText()); // Price is third in the SQL, should be 3

edited Mar 9, 2012 at 19:09

answered Mar 9, 2012 at 18:21

Anthony Grist

38.4k8 gold badges64 silver badges76 bronze badges

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

9 Comments

FatmaTurk Over a year ago

its such a silly mistake I have made thanks for making me realize :) however now it does display the message box "updated" but the changes doesn't take affect in the database... Do you know why this could be?

Anthony Grist Over a year ago

@FatmaTurk You have four placeholders in your SQL string, numbered 1 to 4. 1 is the value for Name, 2 is the value for Size, 3 is the value for Price and 4 is the value for ItemCode. Now look at the values you're passing in your st.setString() calls.

prajeesh kumar Over a year ago

You can get the count of actual rows updated like this int updated=st.executeUpdate(); Check that count and see if anything is actually updated. If it returns >0 then check which database you are using, whether the automatic commit mode is on or not etc.

Patrick Over a year ago

executeUpdate returns an int. You need to check that value to determine if anything was updated. If you are seeing a zero, I would check the database.

FatmaTurk Over a year ago

@Patrick if the issue was with the database then I assume it wouldn't allow me to delete a record from the database as well right?

|

summea · Accepted Answer · 2012-03-09 19:05:38Z

4

Have you tried changing that UPDATE statement to something like this:

"UPDATE item SET Name = ?, Size = ?, Price = ? WHERE ItemCode = ?"

I took out the final , (comma) after the Price = ? because it may conflict with the rest of the SQL.

Edit:

Also, you may want to rearrange the textArea_Code.getText() line to be the last value for the prepared statement. Otherwise, the ItemCode value may not be matching up with the order of the ? placeholders (as the moment, it looks like textArea_price.getText() is getting used as the value for ItemCode... which might be dangerous for what gets updated!)

Instead, what about something like this:

PreparedStatement st = db.con.prepareStatement("UPDATE item SET Name = ?, Size = ?, Price = ? WHERE ItemCode = ?");
st.setString(1, textArea_name.getText());
st.setString(2, textArea_size.getText());
st.setString(3, textArea_price.getText());
st.setString(4, textArea_Code.getText());
st.executeUpdate();

In this way, all of the ? placeholders correctly match up with the values being set:

Name: st.setString(1, textArea_name.getText());
Size: st.setString(2, textArea_size.getText());
Price: st.setString(3, textArea_price.getText());
ItemCode: st.setString(4, textArea_Code.getText());

This is because parameters need to be in the same order as the placeholders (see this documentation for more information.)

edited Mar 9, 2012 at 19:05

answered Mar 9, 2012 at 18:22

summea

7,6634 gold badges35 silver badges49 bronze badges

4 Comments

FatmaTurk Over a year ago

I have made the change to my SQL statement but it still doesn't update the database..

summea Over a year ago

@FatmaTurk I updated this answer; you may want to try rearranging the order of your placeholder strings. Please see answer for more details.

FatmaTurk Over a year ago

it works now :) now I can see how the order can also make a difference..thank you so much..

summea Over a year ago

@FatmaTurk No problem; and... welcome to Stack Overflow! As you ask questions, don't forget to let the community know if an answer helps you by either upvoting an answer, or accepting the answer! Thanks~

JUG · Accepted Answer · 2012-03-09 18:57:28Z

0

What is the output of executeUpdate? Does it return any number of rows updated? If not, then obviously your update clause needs to be, well, updated!

If the executeUpdate does return a positive number, indicating that it updated number of rows, may be your commit policy is not set to auto commit. In that case you may want to commit after executeUpdate.

con.commit();

answered Mar 9, 2012 at 18:57

JUG

70310 silver badges27 bronze badges

1 Comment

FatmaTurk Over a year ago

its sorted now the issue was the order of the setString calls

Hugues · Accepted Answer · 2012-03-09 19:24:25Z

0

Could you try to add a e.printStackTrace() in your catch block ?
In your query remove the , before WHERE UPDATE item SET Name = ?, Size = ?, Price = ? WHERE ItemCode = ?
Maybe there is a problem with your DB ... is your item table in the default schema?... the printStackTrace() will help to find the answer

answered Mar 9, 2012 at 19:24

Hugues

3651 gold badge3 silver badges11 bronze badges

Comments

benka · Accepted Answer · 2015-08-11 09:43:08Z

0

After getting DB connection set auto commit to true so that DB will be updated without any errors.

connection = DBConnection.getInstance().getConnection();
connection.setAutoCommit(true);

edited Aug 11, 2015 at 9:43

benka

4,74235 gold badges50 silver badges58 bronze badges

answered Aug 11, 2015 at 9:29

user1907542

1

Collectives™ on Stack Overflow

update statement in Java

5 Answers 5

9 Comments

4 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

9 Comments

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related