Problem with Update query in Mysql with JDBC

Question

Okay so I am building a project on java and mysql, I am stuck at this point that I have to update a data which is in MySql but from my java gui application, I've executed that update command from MySql command line client

update user set bldu = 50 where userid = 1001;

and it's working perfectly fine there but from my java application on clicking on assigned jbutton it says:

you have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'userid= 1001' at line 1

Please help me..!

Post code, errors, table descriptions etc as text and not images of text. — Joakim Danielson
– Joakim Danielson, Commented Apr 2, 2019 at 12:54
got it... you forgot space before your where clause in java query — Onkar Musale
– Onkar Musale, Commented Apr 2, 2019 at 12:59

Joe Taras · Accepted Answer · 2019-04-02 13:13:13Z

2

In your first screenshot you must add a space before WHERE clause:

String query = "UPDATE user SET bdlu = " + bldut + "WHERE userid = " + uid + ";";

So your query will be interpretated as:

UPDATE user SET bdlu = 50WHERE userid = 1001

So you'll raise a syntax error.

Then you'll have the following query:

String query = "UPDATE user SET bdlu = " + bldut + " WHERE userid = " + uid + ";";

answered Apr 2, 2019 at 13:13

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

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

Comments

Onkar Musale · Accepted Answer · 2019-04-02 13:15:46Z

2

String query = "update user SET bldu = " + bldut + " WHERE userid = " + uid + ";";

use this one instead of your old query may be it is helpful for you.

edited Apr 2, 2019 at 13:15

Onkar Musale

91910 silver badges26 bronze badges

answered Apr 2, 2019 at 13:08

Kamal Kumar

1941 silver badge13 bronze badges

3 Comments

Praneet Kumar Over a year ago

Thank you so much, i got it now i've just ran that program now it says "Can not issue data manipulation statements with executeQuery()"

Onkar Musale Over a year ago

Use executeUpdate() there

Praneet Kumar Over a year ago

i have used that in codes you can see java stmt.executeUpdate(query); ResultSet rs = stmt.executeQuery(query); but it's still giving that error..

Onkar Musale · Accepted Answer · 2019-04-02 13:29:48Z

0

Try this snippet in your code.

String query = "update user SET bldu = " + bldut + " WHERE userid = " + uid + ";";
Statement = con.prepareStatement(query);
Statement.executeUpdate();

by looking at your code you cannot store results of update query in resultSet the executeUpdate() only return 0 or 1 for success and failure of Update.

answered Apr 2, 2019 at 13:29

Onkar Musale

91910 silver badges26 bronze badges

1 Comment

Praneet Kumar Over a year ago

okay Onkar as soon as i replaced this snippet with mine, whole lot of errors poped up now, i have to use "rs.getString" too to get the updated results from db to show here in the jTable1 too..

Praneet Kumar · Accepted Answer · 2019-04-02 14:43:41Z

0

Okay i guys i have figured out something that it is working i mean this program is updating the data stored in mysql from netbeans via jdbc but it won't stop showing that error message like:

"Can not issue data manipulation statements with executeQuery()"

everytime i click one that assigned jButton..! but i checked the database the value i want to change is being changed but then why it is showing this error..?

answered Apr 2, 2019 at 14:43

Praneet Kumar

194 bronze badges

Comments

Kamal Kumar · Accepted Answer · 2019-04-03 04:27:06Z

Please use this code in your java file, do changes according to your file. your issue is you are using the same query in a result set that already uses for the update

Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/bdb", "root", "root");

try {

    String query = "update user SET bldu = " + bldut+ " WHERE userid = " + uid + ";";           
    // create the java mysql update preparedstatement
    Class.forName("com.mysql.jdbc.Driver").newInstance();

    PreparedStatement preparedStmt = conn.prepareStatement(query);
    preparedStmt.executeUpdate();

    query = "select * from user  WHERE userid = " + uid +";";
    ResultSet rs = stmt.executeQuery(query);
    // STEP 5: Extract data from result set
    while (rs.next()) {
        // Retrieve by column name
        String userid = rs.getString("userid");
        String userfname = rs.getString("userfname");
        // all your column
        // Display values
        System.out.print("userid: " + userid);
    }
    // STEP 6: Clean-up environment
    rs.close();
    stmt.close();
    conn.close();
} catch (Exception e) {
    System.err.println("Got an exception! ");
    System.err.println(e.getMessage());
} finally {
    // finally block used to close resources
    try {
        if (conn != null)
            conn.close();
    } catch (SQLException se) {
        se.printStackTrace();
    }// end finally try
}// end try

Collectives™ on Stack Overflow

Problem with Update query in Mysql with JDBC

5 Answers 5

Comments

3 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related