java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement

Question

Can anyone tell me why I received the following error for the syntax below? Thanks.

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

stmt = conn.prepareStatement("UPDATE STAFFDIR " +
                       "SET Last_Name = ?, " +
                       "SET First_Name = ?, " +
                       "SET Phone = ?, " +
                       "SET Dept_Code = ?, " +
                       "SET Email = ?, " +
                       "SET Title = ?, " +
                       "SET Loc_Code = ? " + 
                       "WHERE ID = ?;");

               stmt.setString(1, ID);
               stmt.setString(2, Last_Name);
               stmt.setString(3, First_Name);                   
               stmt.setString(4, Phone);
               stmt.setString(5, Dept_Code);
               stmt.setString(6, Email);
               stmt.setString(7, Title);
               stmt.setString(8, Loc_Code);
               stmt.executeUpdate();

You have the SET keyword on each line. Only keep the first one. — Sean Lange
– Sean Lange, Commented Aug 13, 2014 at 15:08

StuartLC · Accepted Answer · 2014-08-13 15:07:29Z

1

The update statement in Sql requires just one SET no matter how many fields are set, viz

UPDATE STAFFDIR SET Last_Name = ?,
                    First_Name = ?, -- No extra set
                    Phone = ?, ...

As an aside, are you using the correct driver (MS Access vs Sql Server)?

answered Aug 13, 2014 at 15:07

StuartLC

108k18 gold badges225 silver badges295 bronze badges

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

1 Comment

user3511659 Over a year ago

Yes, I am using Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); conn = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:/test.accdb");

Thusitha Thilina Dayaratne · Accepted Answer · 2014-08-13 15:18:13Z

0

There should be only one SET. Do it as follows

stmt = conn.prepareStatement("UPDATE STAFFDIR " +
                       "SET Last_Name = ?, " +
                       "First_Name = ?, " +
                       "Phone = ?, " +
                       "Dept_Code = ?, " +
                       "Email = ?, " +
                       "Title = ?, " +
                       "Loc_Code = ? " + 
                       "WHERE ID = ?;");

answered Aug 13, 2014 at 15:18

Thusitha Thilina Dayaratne

5,7204 gold badges45 silver badges71 bronze badges

Comments

Elliott Frisch · Accepted Answer · 2014-08-13 16:00:04Z

0

Your query only needs one SET and you're binding the parameters in the incorrect order -

stmt = conn.prepareStatement("UPDATE STAFFDIR " +
  "SET Last_Name = ?, First_Name = ?, Phone = ?, " +
  "Dept_Code = ?, Email = ?, Title = ?, Loc_Code = ? " + 
  "WHERE ID = ?");
int i = 1;
// stmt.setString(1, ID);
stmt.setString(i++, Last_Name);
stmt.setString(i++, First_Name);                   
stmt.setString(i++, Phone);
stmt.setString(i++, Dept_Code);
stmt.setString(i++, Email);
stmt.setString(i++, Title);
stmt.setString(i++, Loc_Code);
stmt.setString(i++, ID); // <-- the 8th field.

Also worth noting is that Java 8 removes the ODBC-JDBC bridge, so you might want to consider finding an alternative method of connecting to your DataSource.

answered Aug 13, 2014 at 16:00

Elliott Frisch

202k20 gold badges166 silver badges265 bronze badges

2 Comments

user3511659 Over a year ago

In retrospect it hasn't actually done an update, it does nothing, but the syntax error is gone. Why would you have me move ID to the 8th position?

Elliott Frisch Over a year ago

@user3511659 Because you must bind the parameters in the same order they are in your query.

Collectives™ on Stack Overflow

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related