java jdbc ms access "Syntax error in UPDATE statement"

Question

I'm trying to update my database but I'm getting some syntax error in my code.. Please help.. I try to change it to "st.execute(statement);" and "st.executeUpdate(statement);" but the error is still there. Here's my code...

public void editBook(String inputTitle, String[] newBookInfo)
{
    boolean result = false;
    try
    {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        con = DriverManager.getConnection("jdbc:odbc:database");
        st = con.createStatement();
        rs = st.executeQuery("select * from library");

        while(rs.next())
        {
            if(boyerMoore(rs.getString("Title").toLowerCase(), inputTitle.toLowerCase()))
            {
                isbn = rs.getString("ISBN");
                statement = String.format("UPDATE library SET ISBN = '%s', Title = '%s', Author = '%s', Publisher = '%s', Published Year = '%s', Available Copies = '%s', Total Copies = '%s', WHERE ISBN = '%s'", newBookInfo[0], newBookInfo[1], newBookInfo[2], newBookInfo[3], newBookInfo[4], newBookInfo[5], newBookInfo[6], isbn);
                st.executeQuery(statement);
                rs.close();
                st.close();
                con.close();

                JOptionPane.showMessageDialog(null, "Edit Succes", "Succes", JOptionPane.PLAIN_MESSAGE);
                result = true;
            }
        }

        if(!result)
            JOptionPane.showMessageDialog(null, "\"" + inputTitle + "\"  not Found in the Library", "Error", JOptionPane.ERROR_MESSAGE);
    }
    catch(Exception ex)
    {
        ex.printStackTrace();
    }
}

Here's the full stacktrace..

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.executeQuery(Unknown Source)
at dataBase.editBook(dataBase.java:161)
at mainFrame.actionPerformed(mainFrame.java:177)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$200(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Make use of PreparedStatement over String#format

MadProgrammer
– MadProgrammer

2013-09-28 06:59:55 +00:00
Commented Sep 28, 2013 at 6:59 — MadProgrammer
– MadProgrammer, Commented Sep 28, 2013 at 6:59

HansUp · Accepted Answer · 2013-09-28 07:16:52Z

1

Bracket the field names which include spaces: [Published Year]; [Available Copies]; [Total Copies]. Whether you continue to build the UPDATE statement as you have been or switch to a parameter query, you must still bracket those names.

And as has been mentioned, discard the comma before WHERE ...

[Total Copies] = '%s', WHERE ISBN
                     ^

If you get a "type mismatch" error after making those changes, check the data types of the destination fields. Currently your statement is supplying text values for those fields. If any are numeric instead of text, eliminate the single quotes surrounding the update values.

answered Sep 28, 2013 at 7:16

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

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

1 Comment

user2745681 Over a year ago

I'm getting some errors... "java.sql.SQLException: No ResultSet was produced"

bNd · Accepted Answer · 2013-09-28 06:57:53Z

1

Remove ',' before where

, WHERE

UPDATE library SET ISBN = '%s', Title = '%s', Author = '%s', Publisher = '%s', Published Year = '%s', Available Copies = '%s', Total Copies = '%s' WHERE ISBN = '%s'", newBookInfo[0], newBookInfo[1], newBookInfo[2], newBookInfo[3], newBookInfo[4], newBookInfo[5], newBookInfo[6], isbn

answered Sep 28, 2013 at 6:57

bNd

7,6507 gold badges43 silver badges73 bronze badges

Comments

Joop Eggen · Accepted Answer · 2013-09-28 07:31:29Z

A couple of things got mixed up.

        Connection con = DriverManager.getConnection("jdbc:odbc:database");
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT Title, ISBN FROM library");
        PreparedStatement statement = con
                .prepareStatement("UPDATE library "
                        + "SET ISBN = ?, "
                        + "Title = ?, "
                        + "Author = ?, "
                        + "Publisher = ?, "
                        + "[Published Year] = ?, "
                        + "[Available Copies] = ?, " // [...] needed.
                        + "[Total Copies] = ? " // No comma.
                        + "WHERE ISBN = ?");

        while (rs.next()) {
            if (boyerMoore(rs.getString("Title").toLowerCase(),
                    inputTitle.toLowerCase())) {
                isbn = rs.getString("ISBN");
                statement.setString(1, newBookInfo[0]);
                statement.setString(2, newBookInfo[1]);
                statement.setString(3, newBookInfo[2]);
                statement.setString(4, newBookInfo[3]);
                statement.setString(5, newBookInfo[4]);
                statement.setString(6, newBookInfo[5]);
                statement.setString(7, newBookInfo[6]);
                // statement.setInt(7, Integer.parseInt(newBookInfo[6]));
                statement.setString(8, isbn);
                statement.executeUpdate();

                JOptionPane.showMessageDialog(null, "Edit Succes",
                        "Succes", JOptionPane.PLAIN_MESSAGE);
                result = true;
            }
        }
        rs.close(); // Here.
        st.close();
        statement.close();
        con.close();

I'm getting some error.. java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(Unknown Source) at dataBase.editBook(dataBase.java:203) at mainFrame.actionPerformed(mainFrame.java:177)
Hard for me to say. You got statement.setString(8, isbn)? I forgot it in the first edit. You could try to comment out all statement lines first.
I have no intensive experience with Access, but for some DBMSs querying a table and then updating the same table cannot be done. In that case you could in the query loop collect record IDs and in a second loop update records.

SpringLearner · Accepted Answer · 2013-09-28 07:02:42Z

remove comma before where clause. Also try to use PreparedStatement instead of using Statement Use this way for PreparedStatement

public void editBook(String inputTitle, String[] newBookInfo)
{
    boolean result = false;
    try
    {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        con = DriverManager.getConnection("jdbc:odbc:database");
PreparedStatement pt=con.prepareStatement("select * from library")
        //st = con.createStatement();
        //rs = st.executeQuery("select * from library");

rs=pt.executeQuery();
        while(rs.next())
        {

//your codes
        }


    }
    catch(Exception ex)
    {
        ex.printStackTrace();
    }

Mahesh Modukuru · Accepted Answer · 2013-09-28 14:42:42Z

0

Use two different Statement objects one for Read another for update. Or close the existing statement and re-instantiate again.

answered Sep 28, 2013 at 14:42

Mahesh Modukuru

514 bronze badges

Collectives™ on Stack Overflow

java jdbc ms access "Syntax error in UPDATE statement"

5 Answers 5

1 Comment

Comments

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

3 Comments

Comments

Comments

Related