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.
IDin your table