CODEWITHSUNDEEP
javasqlprepared-statement
FatmaTurk
FatmaTurk

Reputation: 83

update statement in Java

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");      
}

Upvotes: 3

Views: 53651

Answers (5)

user1907542
user1907542

Reputation: 1

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);

Upvotes: 0

Hugues
Hugues

Reputation: 365

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

Upvotes: 0

Anthony Grist
Anthony Grist

Reputation: 38345

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

Upvotes: 12

summea
summea

Reputation: 7593

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.)

Upvotes: 4

JUG
JUG

Reputation: 703

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();

Upvotes: 0

Related Questions