0

I get the following error:

SEVERE: null com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'ID' in 'field list'

I have created a form that takes user input including name, color, car registration and want it processed and inserted into a database with three columns..for carowner, carregistration and color..

The code:

public class DBConnector {

// JDBC driver name and database URL                              
 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";        
 static final String DATABASE_URL = "jdbc:mysql://localhost  /carregistration";
 Connection connection = null; // manages connection
 Statement statement = null; // query statement

public DBConnector() {

     try {
         Class.forName(JDBC_DRIVER); //Loading the java db driver
     } catch (ClassNotFoundException ex) {
         Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex);
     }

}




 public void createConnection(){
     try {
         connection = (Connection) DriverManager.getConnection(DATABASE_URL, "root", "");

     } catch (SQLException ex) {
         Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex);
     }
 }

 public void createStatement(){
     try {
         statement = (Statement) connection.createStatement();
     } catch (SQLException ex) {
         Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex);
     }
 }

 public void insertCars(Cars carToInsert){
     try {
         String queryInsert = "INSERT INTO `carregistration`.`cars` (\n" +
                 "`ID` ,\n" +
                 "`CarRegistration` ,\n" +
                 "`CarOwner` ,\n" +
                 "`Color`\n" +
                 ")\n" +
                 "VALUES (\n" +
                 "NULL , '"+carToInsert.getCarRegistration()+"', '"+carToInsert.getCarOwner()+"', '"+carToInsert.getCarColor()+"'\n" +
                 "); ";

         statement.executeUpdate(queryInsert);
     } catch (SQLException ex) {
         Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex);
     }

 }

 public void getCars(String query){

 }

 public void closeConnection(){

 }

}

public class Cars {
private String carName;
private String carOwner;
private String carColor;
private String carModel;
private String carMake;
private String carYom;
private String carRegistration;


public Cars(String carreg,String carowner,String color){
  carRegistration = carreg;
  carOwner = carowner;
  carColor = color;
}

public String getCarRegistration() {
    return carRegistration;
}

public void setCarRegistration(String carRegistration) {
    this.carRegistration = carRegistration;
}


public String getCarModel() {
    return carModel;
}

public void setCarModel(String carModel) {
    this.carModel = carModel;
}

public String getCarMake() {
    return carMake;
}

public void setCarMake(String carMake) {
    this.carMake = carMake;
}

public String getCarYom() {
    return carYom;
}

public void setCarYom(String carYom) {
    this.carYom = carYom;
}



public String getCarName() {
    return carName;
}

public void setCarName(String carName) {
    this.carName = carName;
}

public String getCarOwner() {
    return carOwner;
}

public void setCarOwner(String carOwner) {
    this.carOwner = carOwner;
}

public String getCarColor() {
    return carColor;
}

public void setCarColor(String carColor) {
    this.carColor = carColor;
}

}

and the file with the main method has the following code too:

private void saveButtonActionPerformed(java.awt.event.ActionEvent evt) {                                           
    String carMake = makeCombo.getSelectedItem().toString();
    String carModel = modelCombo.getSelectedItem().toString();
    String carYOM = yomComboBox.getSelectedItem().toString();
    String carColor = colorText.getText();
    String carReg = regText.getText();
    String carFirstName = firstNameText.getText();
    String carLastName = lastNameText.getText();
    String ownerTitle = titleCombo.getSelectedItem().toString();


    try {
           //validation goes here

        if (carMake.equalsIgnoreCase("Select")) {
            JOptionPane.showMessageDialog(colorText, "Please select make and model", "Input Error", JOptionPane.ERROR_MESSAGE);
        } else if (carYOM.equalsIgnoreCase("Select")) {
            JOptionPane.showMessageDialog(yomComboBox, "Please select year of manufacture", "Input Error", JOptionPane.ERROR_MESSAGE);

        } else if (carColor.trim().equalsIgnoreCase("")) {
            JOptionPane.showMessageDialog(colorText, "Please input car color", "Input Error", JOptionPane.ERROR_MESSAGE);

        } else if (!checkColor(carColor)) {

            JOptionPane.showMessageDialog(colorText, "Invalid Color", "Input Error", JOptionPane.ERROR_MESSAGE);
        } else if (carReg.length() != 8) {
            JOptionPane.showMessageDialog(regText, "Invalid Registration", "Input Error", JOptionPane.ERROR_MESSAGE);

        } //                else if (!match.find()) {
        //                    JOptionPane.showMessageDialog(regText, "Invalid Registration", "Input Error", JOptionPane.ERROR_MESSAGE);
        //                }
        else if (!checkRegFirst(carReg)) {
            System.out.println("First  wrong..");
            JOptionPane.showMessageDialog(regText, "Invalid Registration", "Input Error", JOptionPane.ERROR_MESSAGE);
        } else if (!checkRegFirstThree(carReg)) {
            System.out.println("First three wrong..");
            JOptionPane.showMessageDialog(regText, "Invalid Registration", "Input Error", JOptionPane.ERROR_MESSAGE);
        } //                else if(checkRegFourth(carReg)) {
        //                      System.out.println("fourth three wrong..");
        //                    JOptionPane.showMessageDialog(regText, "Invalid Registration", "Input Error", JOptionPane.ERROR_MESSAGE);
        //                }
        else if (!checkForDigits(carReg)) {
            System.out.println("digit wrong..");
            JOptionPane.showMessageDialog(regText, "Invalid Registration", "Input Error", JOptionPane.ERROR_MESSAGE);
        } else if (!checkForLastLetter(carReg)) {
            System.out.println("last letter wrong..");
            JOptionPane.showMessageDialog(regText, "Invalid Registration", "Input Error", JOptionPane.ERROR_MESSAGE);
        } else if (ownerTitle.equalsIgnoreCase("Select")) {
            JOptionPane.showMessageDialog(titleCombo, "Please Select a title", "Input Error", JOptionPane.ERROR_MESSAGE);
        } else if (carFirstName.trim().equalsIgnoreCase("")) {
            JOptionPane.showMessageDialog(firstNameText, "Please input the first name", "Input Error", JOptionPane.ERROR_MESSAGE);

        } else if (carLastName.trim().equalsIgnoreCase("")) {
            JOptionPane.showMessageDialog(lastNameText, "Please input the last name", "Input Error", JOptionPane.ERROR_MESSAGE);

        } else if (!checkFirstName(carFirstName)) {
            JOptionPane.showMessageDialog(firstNameText, "Enter a first name", "Input Error", JOptionPane.ERROR_MESSAGE);
        } else if (!checkLastName(carLastName)) {
            JOptionPane.showMessageDialog(lastNameText, "Enter a last name", "Input Error", JOptionPane.ERROR_MESSAGE);
        } else {
            String carOwner = carFirstName + " " + carLastName;
            Cars myCar = new Cars(carReg, carOwner, carColor);

            DBConnector myConnector = new DBConnector();
            myConnector.createConnection();
            myConnector.createStatement();
            myConnector.insertCars(myCar);
            JOptionPane.showMessageDialog(lastNameText, "Car inserted successfully", "Success", JOptionPane.INFORMATION_MESSAGE);
        }

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

That last code has some sections for validation of the user entry but that isn't core to my question. I appreciate that my question may not be very clear but i will try to be as specific as possible. Why am I getting an error of unknown column 'ID' in fields? The database i have created(carregistration) just has three fields - CarOwner, CarRegistration and Color so i don't understand where a column ID is coming from. kindly help. thanks.

3
  • Looks like there is no column named ID in your table Commented Nov 23, 2015 at 13:07
  • Please do not concatenate values into your query, use prepared statements, your current code is prone to SQL injection. Commented Nov 23, 2015 at 15:20
  • Mark please explain what you mean by prepared statements. Thank you Commented Nov 24, 2015 at 10:22

1 Answer 1

1

Your problem is here :-

String queryInsert = "INSERT INTO `carregistration`.`cars` (\n" +
                 "`ID` ,\n" +
                 "`CarRegistration` ,\n" +
                 "`CarOwner` ,\n" +
                 "`Color`\n" +
                 ")\n" +
                 "VALUES (\n'"+carToInsert.getCarRegistration()+"', '"+carToInsert.getCarOwner()+"', '"+carToInsert.getCarColor()+"'\n" +
                 "); ";

Just remove column ID and it's value from your query as your table carregistration doesn't contain column ID , change your query to :-

String queryInsert = "INSERT INTO `carregistration` (`CarRegistration`,`CarOwner`,`Color`) VALUES ('"+carToInsert.getCarRegistration()+"','"+carToInsert.getCarOwner()+"','"+carToInsert.getCarColor()+"')";
Sign up to request clarification or add additional context in comments.

3 Comments

thanks. funny how i didn't see that. however with the above changes i am getting another error: java.sql.SQLException: Column count doesn't match value count at row 1.
CarOwner is VARCHAR(20)
CarRegistration is CHAR(10) and Color is VARCHAR(20)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.