Ad:

Java Discussion Thread
Marked Solved
Views: 8867

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Feb 18th, 2008

Syntax error in INSERT INTO statement

Expand Post »

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!

Similar Threads

Problems on Insert and Update Statement in C#
Shopping Cart Not Working in ASP.NET
problem with quotes in SQL statement in Visual Basic 4 / 5 / 6
Problems on Insert and Update in C#
Insert and Update Errors in C#

eddy556

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Jan 2008

Permalink

Feb 18th, 2008

Re: Syntax error in INSERT INTO statement

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

javaAddict

Sponsor

Featured Poster

Reputation Points: 1014

Solved Threads: 446

Nearly a Senior Poster

Offline

3,258 posts
since Dec 2007

Permalink

Feb 18th, 2008

Re: Syntax error in INSERT INTO statement

Click to Expand / Collapse Quote 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.

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)

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:

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

masijade

Moderator

Reputation Points: 1471

Solved Threads: 490

Industrious Poster

Offline

4,043 posts
since Feb 2006

Permalink

Feb 18th, 2008

Re: Syntax error in INSERT INTO statement

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

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

eddy556

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Jan 2008

Permalink

Feb 18th, 2008

Re: Syntax error in INSERT INTO statement

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.

masijade

Moderator

Reputation Points: 1471

Solved Threads: 490

Industrious Poster

Offline

4,043 posts
since Feb 2006

Permalink

Feb 18th, 2008

Re: Syntax error in INSERT INTO statement

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

eddy556

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Jan 2008

Permalink

Feb 18th, 2008

Re: Syntax error in INSERT INTO statement

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)

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.

javaAddict

Sponsor

Featured Poster

Reputation Points: 1014

Solved Threads: 446

Nearly a Senior Poster

Offline

3,258 posts
since Dec 2007

Permalink

Feb 18th, 2008

Re: Syntax error in INSERT INTO statement

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:

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.

masijade

Moderator

Reputation Points: 1471

Solved Threads: 490

Industrious Poster

Offline

4,043 posts
since Feb 2006

Permalink

Feb 18th, 2008

Re: Syntax error in INSERT INTO statement

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

stultuske

Reputation Points: 919

Solved Threads: 354

Nearly a Posting Maven

Offline

2,487 posts
since Jan 2007

Permalink

Feb 18th, 2008

Re: Syntax error in INSERT INTO statement

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

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

eddy556

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Jan 2008

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Java Forum Timeline: Just need someone to review the code for me please.

Next Thread in Java Forum Timeline: I'm having problems

DaniWeb Message

Cancel Changes

User Name
Password