Solved

Whats wrong with my Update SQL statement

Posted on 2010-09-07
12
367 Views
Last Modified: 2012-05-10
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.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 '(NAME,PASSWORD,CELL,ADMINISTRATOR,Message,ENGINEER,CONTROL,SHOWOWNER)VALUES('Der' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
      at com.mysql.jdbc.Util.getInstance(Util.java:384)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2409)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2327)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2312)
      at com.google.project.DBManager.UpdateUser(DBManager.java:621)
      at com.google.project.UpdateUser.doPost(UpdateUser.java:72)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
      at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
      at java.lang.Thread.run(Thread.java:619)

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

	}

	

}

	

}

Open in new window

0
Comment
Question by:bhession
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 92

Expert Comment

by:objects
ID: 33615647
needs to be:
... set col1=value1, col2=value2 ...
0
 
LVL 5

Expert Comment

by:SimonDard
ID: 33615656
Two spaces are missing: between SET and the first parenthesis and between the next parenthesis and VALUES.
0
 

Author Comment

by:bhession
ID: 33615729
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.
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);

	}

Open in new window

0
 
LVL 40

Expert Comment

by:gurvinder372
ID: 33615788
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()+"");
0
 
LVL 26

Expert Comment

by:ksivananth
ID: 33616039
>>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("UPDATE Users SET(NAME,PASSWORD,CELL,"                                    + "ADMINISTRATOR,Message,ENGINEER,CONTROL,SHOWOWNER)"
                                    +" VALUES(?,?,?,?,?,?,?,?) WHERE idusers =\""+user.getID()+"\"");
0
 
LVL 40

Expert Comment

by:gurvinder372
ID: 33616057
the original code should be

ps = con.prepareStatement("UPDATE Users SET NAME = ?, PASSWORD = ?, CELL = ?, ADMINISTRATOR = ?, Message = ?, ENGINEER = ?, CONTROL = ?, SHOWOWNER = ? WHERE idusers = ?");
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:bhession
ID: 33616245
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.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 '(NAME,PASSWORD,CELL,ADMINISTRATOR,Message,ENGINEER,CONTROL,SHOWOWNER) VALUES('Ba' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
      at com.mysql.jdbc.Util.getInstance(Util.java:384)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2409)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2327)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2312)
      at com.google.project.DBManager.UpdateUser(DBManager.java:646)
      at com.google.project.UpdateUser.doPost(UpdateUser.java:72)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
      at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
      at java.lang.Thread.run(Thread.java:619)

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

	}



}

Open in new window

0
 
LVL 40

Accepted Solution

by:
gurvinder372 earned 250 total points
ID: 33616274
update your line 26-27 with the update statement i posted in my previous reply
0
 

Expert Comment

by:justinsahara
ID: 33616507
how we can get id throgh jsp/jstl in spring 3..??
0
 

Expert Comment

by:justinsahara
ID: 33616539
justin.sahara@gmail.com
0
 

Author Closing Comment

by:bhession
ID: 33617113
Yeah this worked ok, don't understand why it didn't work the other way. Thanks.
0
 
LVL 40

Expert Comment

by:gurvinder372
ID: 33617196
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
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now