Results 1 to 8 of 8
  1. #1
    mathidioticz is offline Member
    Join Date
    Jan 2012
    Posts
    48
    Rep Power
    0

    Default Syntax error in UPDATE statement Need Help!

    Hello, I am new to java and i need help.
    I am having this "Syntax error in UPDATE statement." when i run my program.
    Here is the part that is facing this error.

    Java Code:
    	 public boolean updateGameQuestion() {
    			
    			// declare local variables
    			boolean success = false;
    			DBController db = new DBController();
    			db.setUp("CFDatabase");
    			
    			String dbQuery = "UPDATE GameQuestion SET ";
    			dbQuery = dbQuery +	" question = '" + g_question + "'," ; 
    			dbQuery = dbQuery + " Answer1 = '" + g_image1 + "'," ;
    			dbQuery = dbQuery + " Answer2 = '" + g_image2 + "'," ;
    			dbQuery = dbQuery + " Answer3 = '" + g_image3 + "'," ;
    			dbQuery = dbQuery + " Description1 = '" + g_imageDescription1 + "'," ;
    			dbQuery = dbQuery + " Description2 = '" + g_imageDescription2 + "'," ;
    			dbQuery = dbQuery + " Description3 = '" + g_imageDescription3 + "'," ;
    			dbQuery = dbQuery + " WHERE question = '" + g_question + "'";
    						
    			// step 2 of using DBController, use updateRequest method 
    			if (db.updateRequest(dbQuery) == 1){
    			        success = true;
    			}
    			// step 3 of using DBController 
    			db.terminate();
    			return success;
    		}
    And here is the message of the error.
    DB Query: UPDATE GameQuestion SET question = '0', Answer1 = '/HealthOK/ui/images/wine1.png', Answer2 = '/HealthOK/ui/images/wine2.png', Answer3 = 'null', Description1 = '1 large wine (250ml)', Description2 = '1 bottle of wine', Description3 = '1 bottle of whisky', WHERE question = '0'
    java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unkn own Source)
    at OOPJ.database.DBController.updateRequest(DBControl ler.java:64)
    at HealthOK.entity.gameQuestion.updateGameQuestion(ga meQuestion.java:215)
    at HealthOK.entity.gameQuestion.main(gameQuestion.jav a:26)

  2. #2
    Join Date
    Jan 2010
    Posts
    19
    Rep Power
    0

    Default Re: Syntax error in UPDATE statement Need Help!

    Before the "WHERE" clause, there is a "," which you should remove it.

    Thatīs it:
    Java Code:
     dbQuery = dbQuery + " Description3 = '" + g_imageDescription3;
    instead of
    Java Code:
     dbQuery = dbQuery + " Description3 = '" + g_imageDescription3 + "'," ;
    Try this...

  3. #3
    mathidioticz is offline Member
    Join Date
    Jan 2012
    Posts
    48
    Rep Power
    0

    Default Re: Syntax error in UPDATE statement Need Help!

    After changing it to your code, my error became this " Syntax error (missing operator) in query expression ''1 bottle of whisky WHERE question = '0''."

  4. #4
    Join Date
    Jan 2010
    Posts
    19
    Rep Power
    0

    Default Re: Syntax error in UPDATE statement Need Help!

    I gave a wrong instruction for you. Sorry.

    Try this:

    Java Code:
    dbQuery = dbQuery + " Description3 = '" + g_imageDescription3 + "'" ;
    If the command still donīt work, please, tell us the value of the variable dbQuery.

  5. #5
    mathidioticz is offline Member
    Join Date
    Jan 2012
    Posts
    48
    Rep Power
    0

    Default Re: Syntax error in UPDATE statement Need Help!

    Now it says "Too few parameters. Expected 7." What does that mean? And on top of that, it generated one more error which is " General error". My value of variable is String.

    Here is what the error shows:

    connection is jdbc:odbc:CFDatabase
    DB Query: INSERT INTO GameQuestion (g_question, g_image1,g_image2, g_image3, g_imageDescription1, g_imageDescription2, g_imageDescription3)VALUES ('null', '/HealthOK/ui/images/wine1.png', '/HealthOK/ui/images/wine2.png', '/HealthOK/ui/images/wine3.png', '1 large wine (250ml)', '1 bottle of wine', '1 bottle of whisky')
    java.sql.SQLException: General error
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unkn own Source)
    at OOPJ.database.DBController.updateRequestKey(DBCont roller.java:84)
    at HealthOK.entity.gameQuestion.createGameQuestion(ga meQuestion.java:183)
    at HealthOK.entity.gameQuestion.main(gameQuestion.jav a:22)
    java.lang.NullPointerException
    at HealthOK.entity.gameQuestion.createGameQuestion(ga meQuestion.java:185)
    at HealthOK.entity.gameQuestion.main(gameQuestion.jav a:22)
    Connection is closed
    false
    connection is jdbc:odbc:CFDatabase
    DB Query: UPDATE GameQuestion SET question = 'null', Answer1 = '/HealthOK/ui/images/wine1.png', Answer2 = '/HealthOK/ui/images/wine2.png', Answer3 = 'null', Description1 = '1 large wine (250ml)', Description2 = '1 bottle of wine', Description3 = '1 bottle of whisky' WHERE question = 'null'
    java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 7.
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unkn own Source)
    at OOPJ.database.DBController.updateRequest(DBControl ler.java:64)
    at HealthOK.entity.gameQuestion.updateGameQuestion(ga meQuestion.java:215)
    at HealthOK.entity.gameQuestion.main(gameQuestion.jav a:26)

  6. #6
    Join Date
    Jan 2010
    Posts
    19
    Rep Power
    0

    Default Re: Syntax error in UPDATE statement Need Help!

    In your last output there are 2 errors. The first one is about a Insert statement. You didnīt gave us the code which executes this statement.
    The second errors is about an Update statement. Are you sure the column names are correct? Because the insert statement used the column g_question but the update statement is using the column "question". The same happen with other columns of this update statement.

  7. #7
    mathidioticz is offline Member
    Join Date
    Jan 2012
    Posts
    48
    Rep Power
    0

    Default Re: Syntax error in UPDATE statement Need Help!

    I solved that error already. Thankyou so much. However, the first error which is the "General error" is still there. But the strange thing is, when i run the first time, there is totally no error but when i run the second time, the "general error" appears. So is it alternating. Why is this so?

    Here is the code that has the error:
    Java Code:
    	public boolean createGameQuestion() {
    		 boolean success = false;
    		 ResultSet rs = null;
    		 DBController db = new DBController();
    		 String dbQuery;
    		 db.setUp("CFDatabase");
    		 
    		 if ( g_question == " ") {
    			 dbQuery = "INSERT INTO GameQuestion (g_image1,";
    			 dbQuery = dbQuery + "g_image2, g_image3, g_imageDescription1, g_imageDescription2, g_imageDescription3)"; 
    			 dbQuery = dbQuery + "VALUES(\'" + g_image1 + "','" + g_image2 + "', '" + g_image3 + "', '";
    			 dbQuery = dbQuery + g_imageDescription1 + "', '" + g_imageDescription2 + "', '" + g_imageDescription3 + "')";
    		 }
    		 else {
    			 dbQuery = "INSERT INTO GameQuestion (g_question, g_image1,";
    			 dbQuery = dbQuery + "g_image2, g_image3, g_imageDescription1, g_imageDescription2, g_imageDescription3)";
    			 dbQuery = dbQuery + "VALUES (\'" + g_question + "', '";
    			 dbQuery = dbQuery + g_image1 + "', '" + g_image2 + "', '" + g_image3 + "', '";
    			 dbQuery = dbQuery + g_imageDescription1 + "', '" + g_imageDescription2 + "', '" + g_imageDescription3 + "')";
    			 
    		 }
    		 rs = db.updateRequestKey(dbQuery);
    		 try{
    			    if (rs.next()){
    			        // this will return the ID which is a auto number primary key 
    			        g_question = rs.getString(1);
    			        success = true;     
    			    }
    			}
    			catch (Exception e) {
    			   e.printStackTrace();
    			}
    			db.terminate();
    			return success;
    		}

  8. #8
    mathidioticz is offline Member
    Join Date
    Jan 2012
    Posts
    48
    Rep Power
    0

    Default Re: Syntax error in UPDATE statement Need Help!

    The error message shows:
    connection is jdbc:odbc:CFDatabase
    DB Query: INSERT INTO GameQuestion (g_question, g_image1,g_image2, g_image3, g_imageDescription1, g_imageDescription2, g_imageDescription3)VALUES ('null', '/HealthOK/ui/images/wine1.png', '/HealthOK/ui/images/wine2.png', '/HealthOK/ui/images/wine3.png', '1 large wine (250ml)', '1 bottle of wine', '1 bottle of whisky')
    java.sql.SQLException: General error
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unkn own Source)
    at OOPJ.database.DBController.updateRequestKey(DBCont roller.java:84)
    at HealthOK.entity.gameQuestion.createGameQuestion(ga meQuestion.java:183)
    at HealthOK.entity.gameQuestion.main(gameQuestion.jav a:22)
    java.lang.NullPointerException
    at HealthOK.entity.gameQuestion.createGameQuestion(ga meQuestion.java:185)
    at HealthOK.entity.gameQuestion.main(gameQuestion.jav a:22)

Similar Threads

  1. Update statement gone HeWire
    By _-Blackhawk-_ in forum JDBC
    Replies: 8
    Last Post: 10-06-2011, 12:56 PM
  2. error while using sql update statement
    By jttslg in forum Advanced Java
    Replies: 14
    Last Post: 07-03-2011, 11:38 PM
  3. Syntax of Switch statement in j2me
    By jprgmr75 in forum CLDC and MIDP
    Replies: 0
    Last Post: 01-03-2011, 06:49 PM
  4. Java Syntax If statement
    By Blasz in forum New To Java
    Replies: 9
    Last Post: 09-01-2010, 01:21 PM
  5. Update statement not working.
    By OMFGITSROHIT in forum JDBC
    Replies: 5
    Last Post: 04-08-2010, 02:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •