SQLSyntaxError Java when running an INSERT statement

Question

CREATE TABLE "INVOICE" (
"INVOICENO" VARCHAR(200) NOT NULL PRIMARY KEY,
"DATE" DATE,
"COMPANY" VARCHAR(500),
"PRICE" DOUBLE,
"TYPE" VARCHAR(200),
"INVOICETYPE" VARCHAR(200),
"GENERATEDDOCID" INTEGER  GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) 


);

This is my database table code, when i want to enter values into the table with INSERT INTO ( ) VALUES (?,?,?) it is giving SQLSyntax Error.

I use the code below to insert the values:

      public void addData(addData addData) {
      private String sqlInsertStr = "INSERT INTO 
       NBUSER.INVOICE(INVOICENO,DATE,COMPANY,PRICE,TYPE,INVOICETYPE) VALUES(?,?,?,?,?,?);";
    try {

        stmt = conn.prepareStatement(sqlInsertStr);

        stmt.setString(1, addData.getInvoiceNo());
        stmt.setString(2, addData.getDate());
        stmt.setString(3, addData.getCompany());
        stmt.setDouble(4, addData.getPrice());
        stmt.setString(5, addData.getType());
        stmt.setString(6, addData.getInvoiceType());
        stmt = conn.prepareStatement(sqlInsertStr);
        stmt.executeUpdate();
    } catch (SQLException ex) {
        ex.getMessage();
    }
}

It doesn't help that we don't know anything about the kind of database you're using. Additionally, where is the error occurring? Is there any more information? And why are you calling prepareStatement twice? (The second assignment may well be the root of the problem.) — Jon Skeet
– Jon Skeet, Commented Jan 3, 2018 at 17:58
I accidentally add the prepared statement twice, i have removed the bottom one, but the problem still exists. I am using Netbeans Derby SQL. Thanks for helping — Aaron Lee Herng Yue
– Aaron Lee Herng Yue, Commented Jan 3, 2018 at 18:02

Mark Rotteveel · Accepted Answer · 2018-01-03 21:00:35Z

1

There are few probable source of errors (based on the code review):

The query should not be terminated by a semi-colon. Currently, the sql query in your code is being terminated by a semi-colon within the double-quote.

private String sqlInsertStr = "INSERT INTO NBUSER.INVOICE
(INVOICENO,DATE,COMPANY,PRICE,TYPE,INVOICETYPE)
 VALUES(?,?,?,?,?,?)";  // removed semi-colon at the end of query.

You shouldn't declare a column in table bearing the name of standard datatypes like DATE. You should rename it to something like INVOICEDATE, etc while declaring the table.
Your second parameter expects a parameter of Date type, whereas you're supplying a string value to the query. It will totally result in exception.
```
stmt.setDate(2, new java.sql.Date(addData.getDate());// changed the type.
```

edited Jan 3, 2018 at 21:00

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered Jan 3, 2018 at 18:08

Am_I_Helpful

19.2k7 gold badges53 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aaron Lee Herng Yue Over a year ago

Thank you! The semi colon was the one affecting the codes

Am_I_Helpful Over a year ago

@AaronLeeHerngYue - You're welcome. But, please make sure that you follow the recommendations mentioned in my answer so that you have a better code quality. Good Luck!

Collectives™ on Stack Overflow

SQLSyntaxError Java when running an INSERT statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related