bhession
asked on
Whats wrong with my Update SQL statement
I am trying to update a column in my database using the attached code. I am getting the below error coming up. I think its saying my SQL is incorrect. Anyone have any ideas?
Error: Error retrieving data!
com.mysql.jdbc.exceptions. jdbc4.MySQ LSyntaxErr orExceptio n: 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 '(NAME,PASSWORD,CELL,ADMIN ISTRATOR,M essage,ENG INEER,CONT ROL,SHOWOW NER)VALUES ('Der' at line 1
at sun.reflect.NativeConstruc torAccesso rImpl.newI nstance0(N ative Method)
at sun.reflect.NativeConstruc torAccesso rImpl.newI nstance(Na tiveConstr uctorAcces sorImpl.ja va:39)
at sun.reflect.DelegatingCons tructorAcc essorImpl. newInstanc e(Delegati ngConstruc torAccesso rImpl.java :27)
at java.lang.reflect.Construc tor.newIns tance(Cons tructor.ja va:513)
at com.mysql.jdbc.Util.handle NewInstanc e(Util.jav a:409)
at com.mysql.jdbc.Util.getIns tance(Util .java:384)
at com.mysql.jdbc.SQLError.cr eateSQLExc eption(SQL Error.java :1054)
at com.mysql.jdbc.MysqlIO.che ckErrorPac ket(MysqlI O.java:356 6)
at com.mysql.jdbc.MysqlIO.che ckErrorPac ket(MysqlI O.java:349 8)
at com.mysql.jdbc.MysqlIO.sen dCommand(M ysqlIO.jav a:1959)
at com.mysql.jdbc.MysqlIO.sql QueryDirec t(MysqlIO. java:2113)
at com.mysql.jdbc.ConnectionI mpl.execSQ L(Connecti onImpl.jav a:2568)
at com.mysql.jdbc.PreparedSta tement.exe cuteIntern al(Prepare dStatement .java:2113 )
at com.mysql.jdbc.PreparedSta tement.exe cuteUpdate (PreparedS tatement.j ava:2409)
at com.mysql.jdbc.PreparedSta tement.exe cuteUpdate (PreparedS tatement.j ava:2327)
at com.mysql.jdbc.PreparedSta tement.exe cuteUpdate (PreparedS tatement.j ava:2312)
at com.google.project.DBManag er.UpdateU ser(DBMana ger.java:6 21)
at com.google.project.UpdateU ser.doPost (UpdateUse r.java:72)
at javax.servlet.http.HttpSer vlet.servi ce(HttpSer vlet.java: 637)
at javax.servlet.http.HttpSer vlet.servi ce(HttpSer vlet.java: 717)
at org.apache.catalina.core.A pplication FilterChai n.internal DoFilter(A pplication FilterChai n.java:290 )
at org.apache.catalina.core.A pplication FilterChai n.doFilter (Applicati onFilterCh ain.java:2 06)
at org.apache.catalina.core.S tandardWra pperValve. invoke(Sta ndardWrapp erValve.ja va:233)
at org.apache.catalina.core.S tandardCon textValve. invoke(Sta ndardConte xtValve.ja va:191)
at org.apache.catalina.core.S tandardHos tValve.inv oke(Standa rdHostValv e.java:127 )
at org.apache.catalina.valves .ErrorRepo rtValve.in voke(Error ReportValv e.java:102 )
at org.apache.catalina.core.S tandardEng ineValve.i nvoke(Stan dardEngine Valve.java :109)
at org.apache.catalina.connec tor.Coyote Adapter.se rvice(Coyo teAdapter. java:298)
at org.apache.coyote.http11.H ttp11Proce ssor.proce ss(Http11P rocessor.j ava:857)
at org.apache.coyote.http11.H ttp11Proto col$Http11 Connection Handler.pr ocess(Http 11Protocol .java:588)
at org.apache.tomcat.util.net .JIoEndpoi nt$Worker. run(JIoEnd point.java :489)
at java.lang.Thread.run(Threa d.java:619 )
Error: Error retrieving data!
com.mysql.jdbc.exceptions.
at sun.reflect.NativeConstruc
at sun.reflect.NativeConstruc
at sun.reflect.DelegatingCons
at java.lang.reflect.Construc
at com.mysql.jdbc.Util.handle
at com.mysql.jdbc.Util.getIns
at com.mysql.jdbc.SQLError.cr
at com.mysql.jdbc.MysqlIO.che
at com.mysql.jdbc.MysqlIO.che
at com.mysql.jdbc.MysqlIO.sen
at com.mysql.jdbc.MysqlIO.sql
at com.mysql.jdbc.ConnectionI
at com.mysql.jdbc.PreparedSta
at com.mysql.jdbc.PreparedSta
at com.mysql.jdbc.PreparedSta
at com.mysql.jdbc.PreparedSta
at com.google.project.DBManag
at com.google.project.UpdateU
at javax.servlet.http.HttpSer
at javax.servlet.http.HttpSer
at org.apache.catalina.core.A
at org.apache.catalina.core.A
at org.apache.catalina.core.S
at org.apache.catalina.core.S
at org.apache.catalina.core.S
at org.apache.catalina.valves
at org.apache.catalina.core.S
at org.apache.catalina.connec
at org.apache.coyote.http11.H
at org.apache.coyote.http11.H
at org.apache.tomcat.util.net
at java.lang.Thread.run(Threa
public void UpdateUser(User user) {
try
{
System.out.println(user.getID());
ps = con.prepareStatement("UPDATE Users SET(NAME,PASSWORD,CELL,"
+ "ADMINISTRATOR,Message,ENGINEER,CONTROL,SHOWOWNER)"
+"VALUES(?,?,?,?,?,?,?,?) WHERE idusers ="+user.getID()+"");
}
catch(SQLException e)
{
System.out.println("Error: Cannot execute query!");
e.printStackTrace();
System.exit(1);
}
try
{
ps.clearParameters(); // clears previous parameters in there was any
ps.setString(1, user.getName());
ps.setString(2, user.getPassword());
ps.setString(3, user.getCell());
ps.setString(4, user.getAdministrator());
ps.setString(5, user.getMessage());
ps.setString(6, user.getEngineer());
ps.setString(7, user.getControl());
ps.setString(8, user.getShowOwner());
ps.executeUpdate();
}
catch(SQLException e)
{
System.out.println("Error: Error retrieving data!");
e.printStackTrace();
System.exit(1);
}
}
}
Two spaces are missing: between SET and the first parenthesis and between the next parenthesis and VALUES.
ASKER
Objects, I now have the below code (see attached I am still getting a SQL error
SimonDard, can you explain this I am not sure I understand what you mean.
SimonDard, can you explain this I am not sure I understand what you mean.
public void UpdateUser(User user) {
try
{
ps = con.prepareStatement("UPDATE users SET NAME ="+user.getName()+","
+"PASSWORD ="+user.getPassword()+","
+"CELL ="+user.getCell()+","
+"ADMINISTRATOR ="+user.getAdministrator()+","
+"Message ="+user.getMessage()+","
+"ENGINEER ="+user.getEngineer()+","
+"CONTROL ="+user.getControl()+","
+"SHOWOWNER ="+user.getShowOwner()+""
+"WHERE idusers ="+user.getID()+"");
ps.executeUpdate();
}
catch(SQLException e)
{
System.out.println("Error: Cannot execute query!");
e.printStackTrace();
System.exit(1);
}
give some spaces in between where clause
user.getShowOwner()+" "
+"WHERE idusers ="+user.getID()+"");
can you give out put string of this statement
System.out.println("UPDATE users SET NAME ="+user.getName()+","
+"PASSWORD ="+user.getPassword()+","
+"CELL ="+user.getCell()+","
+"ADMINISTRATOR ="+user.getAdministrator() +","
+"Message ="+user.getMessage()+","
+"ENGINEER ="+user.getEngineer()+","
+"CONTROL ="+user.getControl()+","
+"SHOWOWNER ="+user.getShowOwner()+""
+"WHERE idusers ="+user.getID()+"");
user.getShowOwner()+" "
+"WHERE idusers ="+user.getID()+"");
can you give out put string of this statement
System.out.println("UPDATE
+"PASSWORD ="+user.getPassword()+","
+"CELL ="+user.getCell()+","
+"ADMINISTRATOR ="+user.getAdministrator()
+"Message ="+user.getMessage()+","
+"ENGINEER ="+user.getEngineer()+","
+"CONTROL ="+user.getControl()+","
+"SHOWOWNER ="+user.getShowOwner()+""
+"WHERE idusers ="+user.getID()+"");
>>needs to be:
... set col1=value1, col2=value2 ...
>>
don't do that, it destroys the purpose of preparedstatement. use the way you have done, just have the space before values list,
ps = con.prepareStatement("UPDA TE Users SET(NAME,PASSWORD,CELL," + "ADMINISTRATOR,Message,ENG INEER,CONT ROL,SHOWOW NER)"
+" VALUES(?,?,?,?,?,?,?,?) WHERE idusers =\""+user.getID()+"\"");
... set col1=value1, col2=value2 ...
>>
don't do that, it destroys the purpose of preparedstatement. use the way you have done, just have the space before values list,
ps = con.prepareStatement("UPDA
+" VALUES(?,?,?,?,?,?,?,?) WHERE idusers =\""+user.getID()+"\"");
the original code should be
ps = con.prepareStatement("UPDA TE Users SET NAME = ?, PASSWORD = ?, CELL = ?, ADMINISTRATOR = ?, Message = ?, ENGINEER = ?, CONTROL = ?, SHOWOWNER = ? WHERE idusers = ?");
ps = con.prepareStatement("UPDA
ASKER
See the attached to what I am currently useding. I also provided a system out to show my update statement.
The below is the output from the console. It should work...
Connecting to the Database......
UPDATE users SET NAME =JoeBlogs,PASSWORD =joe1231234,CELL =123456789,ADMINISTRATOR =1,Message =1,ENGINEER =1,CONTROL =1,SHOWOWNER =1 WHERE idusers =54
Error: Error retrieving data!
com.mysql.jdbc.exceptions. jdbc4.MySQ LSyntaxErr orExceptio n: 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 '(NAME,PASSWORD,CELL,ADMIN ISTRATOR,M essage,ENG INEER,CONT ROL,SHOWOW NER) VALUES('Ba' at line 1
at sun.reflect.NativeConstruc torAccesso rImpl.newI nstance0(N ative Method)
at sun.reflect.NativeConstruc torAccesso rImpl.newI nstance(Na tiveConstr uctorAcces sorImpl.ja va:39)
at sun.reflect.DelegatingCons tructorAcc essorImpl. newInstanc e(Delegati ngConstruc torAccesso rImpl.java :27)
at java.lang.reflect.Construc tor.newIns tance(Cons tructor.ja va:513)
at com.mysql.jdbc.Util.handle NewInstanc e(Util.jav a:409)
at com.mysql.jdbc.Util.getIns tance(Util .java:384)
at com.mysql.jdbc.SQLError.cr eateSQLExc eption(SQL Error.java :1054)
at com.mysql.jdbc.MysqlIO.che ckErrorPac ket(MysqlI O.java:356 6)
at com.mysql.jdbc.MysqlIO.che ckErrorPac ket(MysqlI O.java:349 8)
at com.mysql.jdbc.MysqlIO.sen dCommand(M ysqlIO.jav a:1959)
at com.mysql.jdbc.MysqlIO.sql QueryDirec t(MysqlIO. java:2113)
at com.mysql.jdbc.ConnectionI mpl.execSQ L(Connecti onImpl.jav a:2568)
at com.mysql.jdbc.PreparedSta tement.exe cuteIntern al(Prepare dStatement .java:2113 )
at com.mysql.jdbc.PreparedSta tement.exe cuteUpdate (PreparedS tatement.j ava:2409)
at com.mysql.jdbc.PreparedSta tement.exe cuteUpdate (PreparedS tatement.j ava:2327)
at com.mysql.jdbc.PreparedSta tement.exe cuteUpdate (PreparedS tatement.j ava:2312)
at com.google.project.DBManag er.UpdateU ser(DBMana ger.java:6 46)
at com.google.project.UpdateU ser.doPost (UpdateUse r.java:72)
at javax.servlet.http.HttpSer vlet.servi ce(HttpSer vlet.java: 637)
at javax.servlet.http.HttpSer vlet.servi ce(HttpSer vlet.java: 717)
at org.apache.catalina.core.A pplication FilterChai n.internal DoFilter(A pplication FilterChai n.java:290 )
at org.apache.catalina.core.A pplication FilterChai n.doFilter (Applicati onFilterCh ain.java:2 06)
at org.apache.catalina.core.S tandardWra pperValve. invoke(Sta ndardWrapp erValve.ja va:233)
at org.apache.catalina.core.S tandardCon textValve. invoke(Sta ndardConte xtValve.ja va:191)
at org.apache.catalina.core.S tandardHos tValve.inv oke(Standa rdHostValv e.java:127 )
at org.apache.catalina.valves .ErrorRepo rtValve.in voke(Error ReportValv e.java:102 )
at org.apache.catalina.core.S tandardEng ineValve.i nvoke(Stan dardEngine Valve.java :109)
at org.apache.catalina.connec tor.Coyote Adapter.se rvice(Coyo teAdapter. java:298)
at org.apache.coyote.http11.H ttp11Proce ssor.proce ss(Http11P rocessor.j ava:857)
at org.apache.coyote.http11.H ttp11Proto col$Http11 Connection Handler.pr ocess(Http 11Protocol .java:588)
at org.apache.tomcat.util.net .JIoEndpoi nt$Worker. run(JIoEnd point.java :489)
at java.lang.Thread.run(Threa d.java:619 )
The below is the output from the console. It should work...
Connecting to the Database......
UPDATE users SET NAME =JoeBlogs,PASSWORD =joe1231234,CELL =123456789,ADMINISTRATOR =1,Message =1,ENGINEER =1,CONTROL =1,SHOWOWNER =1 WHERE idusers =54
Error: Error retrieving data!
com.mysql.jdbc.exceptions.
at sun.reflect.NativeConstruc
at sun.reflect.NativeConstruc
at sun.reflect.DelegatingCons
at java.lang.reflect.Construc
at com.mysql.jdbc.Util.handle
at com.mysql.jdbc.Util.getIns
at com.mysql.jdbc.SQLError.cr
at com.mysql.jdbc.MysqlIO.che
at com.mysql.jdbc.MysqlIO.che
at com.mysql.jdbc.MysqlIO.sen
at com.mysql.jdbc.MysqlIO.sql
at com.mysql.jdbc.ConnectionI
at com.mysql.jdbc.PreparedSta
at com.mysql.jdbc.PreparedSta
at com.mysql.jdbc.PreparedSta
at com.mysql.jdbc.PreparedSta
at com.google.project.DBManag
at com.google.project.UpdateU
at javax.servlet.http.HttpSer
at javax.servlet.http.HttpSer
at org.apache.catalina.core.A
at org.apache.catalina.core.A
at org.apache.catalina.core.S
at org.apache.catalina.core.S
at org.apache.catalina.core.S
at org.apache.catalina.valves
at org.apache.catalina.core.S
at org.apache.catalina.connec
at org.apache.coyote.http11.H
at org.apache.coyote.http11.H
at org.apache.tomcat.util.net
at java.lang.Thread.run(Threa
public void UpdateUser(User user) {
try
{
/*System.out.println("UPDATE users SET NAME ="+user.getName()+","
+"PASSWORD ="+user.getPassword()+" "
//+"CELL ="+user.getCell()+","
//+"ADMINISTRATOR ="+user.getAdministrator()+","
//+"Message ="+user.getMessage()+","
//+"ENGINEER ="+user.getEngineer()+","
//+"CONTROL ="+user.getControl()+","
//+"SHOWOWNER ="+user.getShowOwner()+""
+"WHERE idusers ="+user.getID()+"");
ps = con.prepareStatement("UPDATE users SET NAME ="+user.getName()+","
+"PASSWORD ="+user.getPassword()+" "
//+"CELL ="+user.getCell()+","
//+"ADMINISTRATOR ="+user.getAdministrator()+","
//+"Message ="+user.getMessage()+","
//+"ENGINEER ="+user.getEngineer()+","
//+"CONTROL ="+user.getControl()+","
//+"SHOWOWNER ="+user.getShowOwner()+" "
+"WHERE idusers ="+user.getID()+""); */
ps = con.prepareStatement("UPDATE Users SET(NAME,PASSWORD,CELL," + "ADMINISTRATOR,Message,ENGINEER,CONTROL,SHOWOWNER)"
+" VALUES(?,?,?,?,?,?,?,?) WHERE idusers =\""+user.getID()+"\"");
try
{
ps.clearParameters(); // clears previous parameters in there was any
ps.setString(1, user.getName());
ps.setString(2, user.getPassword());
ps.setString(3, user.getCell());
ps.setString(4, user.getAdministrator());
ps.setString(5, user.getMessage());
ps.setString(6, user.getEngineer());
ps.setString(7, user.getControl());
ps.setString(8, user.getShowOwner());
System.out.println("UPDATE users SET NAME ="+user.getName()+","
+"PASSWORD ="+user.getPassword()+","
+"CELL ="+user.getCell()+","
+"ADMINISTRATOR ="+user.getAdministrator()+","
+"Message ="+user.getMessage()+","
+"ENGINEER ="+user.getEngineer()+","
+"CONTROL ="+user.getControl()+","
+"SHOWOWNER ="+user.getShowOwner()+" "
+"WHERE idusers ="+user.getID()+"");
ps.executeUpdate();
}
catch(SQLException e)
{
System.out.println("Error: Error retrieving data!");
e.printStackTrace();
System.exit(1);
}
}
catch(SQLException e)
{
System.out.println("Error: Cannot execute query!");
e.printStackTrace();
System.exit(1);
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
how we can get id throgh jsp/jstl in spring 3..??
justin.sahara@gmail.com
ASKER
Yeah this worked ok, don't understand why it didn't work the other way. Thanks.
thanks for the points
<<Yeah this worked ok, don't understand why it didn't work the other way. Thanks.>>
My experience in mysql tells that update query syntax is different.
also check
http://dev.mysql.com/doc/refman/5.0/en/update.html
<<Yeah this worked ok, don't understand why it didn't work the other way. Thanks.>>
My experience in mysql tells that update query syntax is different.
also check
http://dev.mysql.com/doc/refman/5.0/en/update.html
... set col1=value1, col2=value2 ...