Java

Java RSS

Syntax error in INSERT INTO statement

Thread Solved

•

Join Date: Jan 2008

Posts: 32

Reputation: eddy556 is an unknown quantity at this point

Solved Threads: 0

eddy556

Offline

Light Poster

Syntax error in INSERT INTO statement

Feb 18th, 2008

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!

•

Join Date: Dec 2007

Posts: 1,525

Reputation: javaAddict is a glorious beacon of light

Solved Threads: 209

javaAddict javaAddict is offline

Offline

Posting Virtuoso

Re: Syntax error in INSERT INTO statement

Feb 18th, 2008

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

Check out my New Bike at my Public Profile at the "About Me" tab

•

Join Date: Feb 2006

Posts: 2,281

Reputation: masijade has much to be proud of

Solved Threads: 243

masijade

Offline

Nearly a Posting Maven

Re: Syntax error in INSERT INTO statement

Feb 18th, 2008

•

Originally Posted by 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!

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

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
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')");
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)

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
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)");
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:

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
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();
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.

Last edited by masijade; Feb 18th, 2008 at 9:47 am. Reason: Oops on the where clause

Java Programmer and Sun Systems Administrator

----------------------------------------------

Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.
--Brian Kernighan

•

Join Date: Jan 2008

Posts: 32

Reputation: eddy556 is an unknown quantity at this point

Solved Threads: 0

eddy556

Offline

Light Poster

Re: Syntax error in INSERT INTO statement

Feb 18th, 2008

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

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
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();
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

•

Join Date: Feb 2006

Posts: 2,281

Reputation: masijade has much to be proud of

Solved Threads: 243

masijade

Offline

Nearly a Posting Maven

Re: Syntax error in INSERT INTO statement

Feb 18th, 2008

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.

Last edited by masijade; Feb 18th, 2008 at 10:16 am.

•

Join Date: Jan 2008

Posts: 32

Reputation: eddy556 is an unknown quantity at this point

Solved Threads: 0

eddy556

Offline

Light Poster

Re: Syntax error in INSERT INTO statement

Feb 18th, 2008

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

•

Join Date: Dec 2007

Posts: 1,525

Reputation: javaAddict is a glorious beacon of light

Solved Threads: 209

javaAddict javaAddict is offline

Offline

Posting Virtuoso

Re: Syntax error in INSERT INTO statement

Feb 18th, 2008

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)

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
<%
//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(.......);
%>
<%
//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.

Check out my New Bike at my Public Profile at the "About Me" tab

•

Join Date: Feb 2006

Posts: 2,281

Reputation: masijade has much to be proud of

Solved Threads: 243

masijade

Offline

Nearly a Posting Maven

Re: Syntax error in INSERT INTO statement

Feb 18th, 2008

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:

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
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);
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.

•

Join Date: Jan 2007

Posts: 706

Reputation: stultuske is a jewel in the rough

Solved Threads: 84

stultuske

Offline

Master Poster

Re: Syntax error in INSERT INTO statement

Feb 18th, 2008

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

•

Join Date: Jan 2008

Posts: 32

Reputation: eddy556 is an unknown quantity at this point

Solved Threads: 0

eddy556

Offline

Light Poster

Re: Syntax error in INSERT INTO statement

#10

Feb 18th, 2008

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

 Help with Code Tags 
 java Syntax (Toggle Plain Text) 
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....");
 
%>
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