Syntax error in INSERT INTO statement

[eddy556]

I have a JSP which inserts date into a MS Access database. But I keep receiving the error:

24: statement.executeUpdate("INSERT INTO Questions WHERE ID ='"+ID+"'(\"Question\", \"Answer\", \"Timestamp\", \"Author\", \"Customer_Useful\", \"Customer_NotUseful\") VALUES ('"+Question+"','"+Answer+"','this has been updated','"+Author+"','0','0')");

I have checked and checked all the values are there and correct and that the database is expecting to receive the correct types. I'm sure its just a comma or something. Thanks for your help!

[javaAddict]

I think that the concept of the query is wrong. If you want to perform an INSERT then you don't need a
WHERE ID= id
because you are inserting something that does not exist.
Perhaps you should try an UPDATE

[masijade]

I have a JSP which inserts date into a MS Access database. But I keep receiving the error:

24: statement.executeUpdate("INSERT INTO Questions WHERE ID ='"+ID+"'(\"Question\", \"Answer\", \"Timestamp\", \"Author\", \"Customer_Useful\", \"Customer_NotUseful\") VALUES ('"+Question+"','"+Answer+"','this has been updated','"+Author+"','0','0')");

I have checked and checked all the values are there and correct and that the database is expecting to receive the correct types. I'm sure its just a comma or something. Thanks for your help!

Use a PreparedStatement, rather than a cobbled together Statement (which could contain bad values in the variables), and this probably usually won't happen.

statement.executeUpdate("INSERT INTO Questions WHERE ID ='"+ID+"'(\"Question\", \"Answer\", \"Timestamp\", \"Author\", \"Customer_Useful\", \"Customer_NotUseful\") VALUES ('"+Question+"','"+Answer+"','this has been updated','"+Author+"','0','0')");

Should be (as you have it)

statement.executeUpdate("INSERT INTO Questions (ID, Question, Answer, \"Timestamp\", Author, Customer_Useful, Customer_NotUseful) VALUES ('"+ID+"', '"+Question+"','"+Answer+"','this has been updated','"+Author+"',0,0)");

Assuming that the last two fields are number fields. The "TimeStamp" column needs quotes because that is a reserved word in nearly all DBs. You should change that column name. Also, if either question, or answer, has "special" characters (such as an apostrophy), the insert will fail (unless you do some leg work to prevent it that PreparedStatement does for you).

Better, your statement should be like this:

PreparedStatement ps = conn.prepareStatement("INSERT INTO Questions (ID, Question, Answer, \"Timestamp\", Author, Customer_Useful, Customer_NotUseful) VALUES (?, ?, ?,'this has been updated',?,0,0)");
ps.setString(1, ID);
ps.setString(2, Question);
ps.setString(3, Answer);
ps.setString(4, Author);
ps.executeUpdate();

This prevents SQLinjection attacks and it automatically properly escapes all special characters in the values used. You should still change the name of tht Timestamp column, though.

[eddy556]

Okay, having taken into consideration everything on this post I have created a new statement:

PreparedStatement ps = conn.prepareStatement("UPDATE Questions WHERE ID ="+ID+" (Question, Answer, Date, Author, Customer_Useful, Customer_NotUseful) VALUES ('"+Question+"', '"Answer"','this has been updated','"+Author+"',0,0)");
ps.setString(1, ID);
ps.setString(2, Question);
ps.setString(3, Answer);
ps.setString(4, Author);
ps.executeUpdate();

but now I am getting the error:

org.apache.jasper.JasperException: Unable to compile class for JSP:

An error occurred at line: 24 in the jsp file: /confirm_update.jsp
The operator * is undefined for the argument type(s) java.lang.String, java.lang.String
21: {
22: out.print("Saving changes....");
23:
24: PreparedStatement ps = conn.prepareStatement("UPDATE Questions WHERE ID ="+ID+" (Question, Answer, Date, Author, Customer_Useful, Customer_NotUseful) VALUES ('"+Question+"', '"Answer"','this has been updated','"+Author+"',0,0)");
25: ps.setString(1, ID);
26: ps.setString(2, Question);
27: ps.setString(3, Answer);

An error occurred at line: 24 in the jsp file: /confirm_update.jsp
Syntax error on token "Answer", * expected
21: {
22: out.print("Saving changes....");
23:
24: PreparedStatement ps = conn.prepareStatement("UPDATE Questions WHERE ID ="+ID+" (Question, Answer, Date, Author, Customer_Useful, Customer_NotUseful) VALUES ('"+Question+"', '"Answer"','this has been updated','"+Author+"',0,0)");
25: ps.setString(1, ID);
26: ps.setString(2, Question);
27: ps.setString(3, Answer);

Thank you very much

[masijade]

Date is also a reserved word, and you need to look at the "edited" version of the post, as that Where clause is not something that belongs in an insert statement.

Edit: And take a careful look at the PreparedStatement as it appears in my post, and as it appears in yours. You haven't not excahnged any of those cobbled areas of the statements (i.e. the '" + var + "' areas) with the question mark placeholders.

[eddy556]

Oh them ? were intentional? I replaced them LOL. Okay so here it is now...I seem to be getting somewhere. I've taken the WHERE out, but I don't understand how it knows which record to update. I've been on the W3Schools website and what they have is completely different :-S. Anyway I'm getting this error now after much fiddling around:

org.apache.jasper.JasperException: An exception occurred processing JSP page /confirm_update.jsp at line 28

25: ps.setString(1, ID);
26: ps.setString(2, Question);
27: ps.setString(3, Answer);
28: ps.setString(4, Author);
29: ps.executeUpdate();
30:
31:

It doesn't seem to like the author field all though it is definitely there and of the correct type.

Thanks very much

[javaAddict]

If you remove the WHERE from the query, it won't know which record to update. And the PreparedStatement needs '?' to work in the query. As far the author field, try a new approach.
Write a class that has methods that update or do other staff with the database, and after you have tested it, use it in your .jsp. You shouldn't have too much logic in jsp files. Just html and calling methods. You shouldn't write preparedStatements and other long code calculations.
A small example of updating the db from a .jsp. (I am not saying that this will work for you)

<%
//get the parameters from the request and store them in variables
String someVariable = request.getParameter("someVariable");
//probably check the values for errors
SomeClass sc = new SomeClass();
sc.updateMethod(.......);
%>

Inside the SomeClass you should implement and TEST your queries.

[masijade]

Well, is this an update or an insert? Your query was a bit mixed last time, and I created an insert out of it (moving ID into the column and value list). If you simply removed ID, then there are only three fields, and that it why it complains on #4. If you want an update then your query is all wrong, and should look like this:

PreparedStatement ps = conn.prepareStatement("Update Questions Set Question = ?, Answer = ?, WhateverYouCallItNow = 'this has been updated', Author = ?, Customer_Useful = 0, Customer_NotUseful = 0 Where ID = ?");
ps.setString(1, Question);
ps.setString(2, Answer);
ps.setString(3, Author);
ps.setString(4, ID);

You really need to read through both the JDBC tutorial, and an SQL tutorial before continuing.

[stultuske]

agree with JavaAddict. ID is mostly the name given to the unique key. you can not insert something with a key that allready exists. You also don't get to say 'where' the data is being putted, it is just added to the database, so a where clausule is a very weird thing to see in a insert statement

either you add completely new data, which implies you have no use for any 'where', since it is possible a new value for the field is given (and you sure can not use a where concerning the (primary) key, since otherwise you would (in no case) be capable of adding data to the database.
or, you want to change data, for the record with ID = id, but you can not use Add here, since (even if it would work) you would get two records with identical primary (and thus should-be) unique keys.

basicly, drop the where, or use update, that'll save you some head aches :)

[eddy556]

Okay, I'm starting to think theres something wrong with my computer because this is beyond a joke. This is what I have now:

if (submit != null)

{

try {
String sql = "Update Questions Set Question = ?, Answer = ?, Date = 'this has been updated', Author = ?, Customer_Useful = 5, Customer_NotUseful = 3 Where ID = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, Question);
ps.setString(2, Answer);
ps.setString(3, Author);
ps.setString(4, ID);


ps.executeUpdate();

}catch(SQLException ex) {
         System.err.println("SQLException: " + ex.getMessage()) ;}

out.print("Saving changes....");

%>

I'm trying to catch the error and read what it is because I keep getting: "javax.servlet.ServletException: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement." but how do I view the statement as the database is receiving it??

The try catch just ignores the problem and does not print the value at all. From what I can see there is nothing wrong at all.

Your help is much appreciated

[javaAddict]

I would suggest copying the query in an sql editor and try to run it by replacing only the '?' with values.

[eddy556]

After all that! All I had to do was put square brackets around date because it is a reserved word! I was ready to hit the computer ;-)

Thanks for your help guys

[masijade]

Read reply #5. I had already told you to change the name of the field, again (after having changed it from timestamp). You are much, much, much better off not using reserved words in table or column names. Even if there is a "workaround" to allow it. It is, at the very least, a very bad practice with nasty side-effects (as you have just seen).