JDBC update database

Question

i have a form and onClick listener i want to update some data on my database exception says that i have a syntax error but when i execute query at mysql console it works here is my code all variables are checked

String temp =  itemList.getModel().getElementAt(itemList.getSelectedIndex()).toString();
PreparedStatement pt = supplies.con.prepareStatement("update prods set pquant = pquant + ? where pname = ?");
pt.setInt(1, Integer.parseInt(empsalary.getText()));
pt.setString(2, temp);
supplies.pst.executeQuery(temp);

Error

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 'DVD Verdatim' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3206)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1232)
at buy$1.actionPerformed(buy.java:62)

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 'DVD Verdatim' at line 1 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723) at com.mysql.jdbc.Connection.execSQL(Connection.java:3277) at com.mysql.jdbc.Connection.execSQL(Connection.java:3206) — , Nov 10 '14 at 16:30
Yeah I know what exactly is happening, your SQL statement is no good.. — ryekayo, Nov 10 '14 at 16:36
Please run your sql statement in DB before using with Java code. — Lokesh, Nov 10 '14 at 16:38
my statement runs in DB where why my statement its not good ? — , Nov 10 '14 at 16:42
Can you add `System.out.println(pt.toString());` before the `executeQuery` statement and provide the actual query that throws the error? — Slowcoder, Nov 10 '14 at 16:53
com.mysql.jdbc.PreparedStatement@55b5ff11: update prods set pquant = pquant + 21 where pname = 'DVD Verdatim' — , Nov 10 '14 at 16:56
i try to run update query without preparedStatement its ok but it dont update my DB when i try to delete a row it works — , Nov 10 '14 at 16:56
@Seekarakos, I have updated my answer. Do you have a variable for pquant and pname? — ryekayo, Nov 10 '14 at 16:58
yes now i run "update prods set pquant = pquant + 30 where pname = 'DVD Verdatim'" and it worked but again i cant updated with preparedStatement — , Nov 10 '14 at 17:01
I fount my error i declare a variable pt and i execute the query from the statement that is empty pst i change to pt.executeUpdate(); and its ok now — , Nov 10 '14 at 17:06
There is no need for you to apologize. I was talking about all of us not finding that error in the code :) — Slowcoder, Nov 10 '14 at 18:36

ryekayo · Answer 1 · 2014-11-10T17:04:32.363

0

As the compiler mentioned, there is indeed a Syntax error in your SQL query. Try:

String temp =  itemList.getModel().getElementAt(itemList.getSelectedIndex()).toString();
PreparedStatement pt = supplies.con.prepareStatement("update prods set pquant = @0 + 21" + "'variableForPquant'" "where pname = @1" + "'variableForPname'");
pt.setInt(1, Integer.parseInt(empsalary.getText()));
pt.setString(2, temp);
supplies.pst.executeQuery(temp);

You need to use quotes when you are using variables in your SQL query. I believe it is either

"'variableName'" or '"variableName'"

edited Nov 10 '14 at 17:04

answered Nov 10 '14 at 16:39

ryekayo

2,341
3
23
51

with "?" and pst.setString java add single quotes – Nov 10 '14 at 16:42
Generally the ? will be the variable name you set the input for your query. But from what I can see in your code, it looks like the quotes are not there for your query and that is probably what the compilers complaining about. – ryekayo Nov 10 '14 at 16:43
I fount my error i declare a variable pt and i execute the query from the statement that is empty pst i change to pt.executeUpdate(); and its ok now – Nov 10 '14 at 17:06

score 0 · Answer 2 · answered Nov 10 '14 at 17:07

0

I found my error i declare a variable pt and i execute the query from the statement that is empty pst i change to pt.executeUpdate(); and its ok now

answered Nov 10 '14 at 17:07

JDBC update database

Error

2 Answers2