Error at MySQL server

Question

I am getting the following error:

...check the syntax that corresponds to your MySQL server version for the right syntax to use near "Item1" at line 1

Here is the relevant part of the code:

String e = e_id.getSelectedItem().toString();
String value1 = e;
String o = o_code.getSelectedItem().toString(); 
String value2 = o;
String value3 = o_credit.getText();
// String value4 = session.getText();
// String value5 = designation.getText();
// String value6 = phd_com_id.getText();
String sql = "update passes_optional set o_code='"+value2+"', o_credit='"+value3+"' where e_id='"+value1+"'";
pst = conn.prepareStatement(sql);
pst.execute();

e_id is a drop down list with the values "Item1", "Item2", "Item3", and "Item4". The table contains info on Item1.

• JonK: Please don't dump unsanitised values directly into your SQL string. The PreparedStatement interface provides a set of setX(X value) methods for safely adding parameters to your queries.
• seenukarthi: Dont concat the value in the SQL instead is prepareStatement.setXXX() to set the value.

Answer 1

That's because with prepareStatement you have to use PreparedStatements.

String sql="update passes_optional set o_code=?, o_credit=? where e_id=?";
PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1, value2);
pst.setString(2, value3);
pst.setString(3, value1);
pst.execute();

Oracle official Prepared Statements tutorial

UPDATE

As pointed out by JonK, you should also be using pst.executeUpdate() instead of pst.execute():

String sql="update passes_optional set o_code=?, o_credit=? where e_id=?";
PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1, value2);
pst.setString(2, value3);
pst.setString(3, value1);
pst.executeUpdate();

• JonK: Believe the OP also needs to be calling pst.executeUpdate() instead of just pst.execute()
• BackSlash: @JonK The OP should be calling executeUpdate, but both pst.executeUpdate() and pst.execute() should work. From the docs: boolean execute(): Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.
• user3598542: Not working!!! It is giving java.lang.Unsupported operational exception:not supported yet
• BackSlash: @user3598542 Did you add the mysql jdbc connector to your project? Can you post the exact exception, with the full code and the line which is causing the exception?