"You have an error in your SQL syntax;check the manual that corresponds to your MySQL server version......"

Question

This is the error I get:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Name='Sanchez',Middle Name='Stark',First Name='Ned',Birthday='1993-11' at line 1"

 private void UpdateButton3ActionPerformed(java.awt.event.ActionEvent evt) {                                              
        // TODO add your handling code here:
      try {
        String WorkID = WorkIDField3A.getText();
        String LastName = LastNameField3.getText();
        String MiddleName = MidNameField3.getText();
        String FirstName = FirstNameField3.getText();
        String Birthday = BDayField3.getText();
        String Age = AgeField3.getText();
        String Gender = (String) GendCombo3.getSelectedItem();
        String CStatus = (String) CStatCombo3.getSelectedItem();
        String Dept = (String) DeptCombo3.getSelectedItem();
        String Pos = PosField3.getText();
        String WorkHrs3A = WorkHoursField3A.getText();
        String WorkHrs3B = WorkHoursField3B.getText();
        String WorkDaysFrom = (String) WorkDaysFromCombo.getSelectedItem();
        String WorkDaysTo = (String) WorkDaysToCombo.getSelectedItem();
        String HireDate = HireDateField3.getText();

        String SQL = "UPDATE employees SET Last Name =?, Middle Name =?, First Name =?, Birthday =?, Age=?, Gender =?, Civil Status =?, Department =?, Position =?, Work Hours From(AM) =?, Work Hours To(PM) =?, Work Days From =?, Work Days To =?, Hire Date =? WHERE WorkID=?";

        stmt = conn.prepareStatement(SQL);

        stmt.setString(1, LastName);
        stmt.setString(2, MiddleName);
        stmt.setString(3, FirstName);
        stmt.setString(4, Birthday);
        stmt.setString(5, Age);
        stmt.setString(6, Gender);
        stmt.setString(7, CStatus);
        stmt.setString(8, Dept);
        stmt.setString(9, Pos);
        stmt.setString(10, WorkHrs3A);
        stmt.setString(11, WorkHrs3B);
        stmt.setString(12, WorkDaysFrom);
        stmt.setString(13, WorkDaysTo);       
        stmt.setString(14, HireDate);
        stmt.setString(15, WorkID);
        //Execute the SQL statement and update the table
        stmt.executeUpdate();
        JOptionPaneUpdate.showMessageDialog(this, "Update Succesful!");
  }catch(Exception e)
  {
      JOptionPaneUpdateFail.showMessageDialog(this, e.getMessage());
  }

You cannot have spaces in your column names my friend, unless you surround them with a delimiter (for MySQL the delimiter is the backtick `). — Perception, Mar 07 '13 at 12:55
See http://stackoverflow.com/questions/6055505/blank-spaces-in-column-names-with-mysql — JoDev, Mar 07 '13 at 12:57
Is SET Last Name =? correct ? Is `Last Name` really your column name ? — Brian Agnew, Mar 07 '13 at 12:54

BobTheBuilder · Accepted Answer · 2013-03-07T13:21:31.447

5

Your columns are "LAST_NAME" or "Last Name"???

This is your error. Dont use space in your column name.

If you really really need to, you can escape the column name using:'Last Name'

edited Mar 07 '13 at 13:21

answered Mar 07 '13 at 12:55

BobTheBuilder

18,858
6
40
61

score 3 · Answer 2 · answered Mar 07 '13 at 12:55

Escape these names that has spaces on them. Like this

UPDATE employees 
SET `Last Name` =?, 
    `Middle Name` =?,
    `First Name` =?, 
     Birthday =?, 
     Age=?, 
     Gender =?, 
     `Civil Status` =?, 
      ...
WHERE WorkID=?

And please try to avoid these names.

"You have an error in your SQL syntax;check the manual that corresponds to your MySQL server version......"

2 Answers2