MySQL Query Through Java Syntax Error

Question

I have this piece of code which inserts records into the accounts table:

String accNumber = jTextField5.getText();
String accName = jTextField4.getText();
String accAddress1 = jTextField3.getText();
String accAddress2 = jTextField2.getText();
String accCity = jTextField6.getText();
String accCounty = jTextField7.getText();
String accPostCode = jTextField9.getText();
String accContact = jTextField8.getText();

String query = "Insert into accounts (AccNo, name, address, address2, address3, City, County, PostCode, contact) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";

try{

    connect.pst = connect.con.prepareStatement(query);
    connect.pst.setString(1, accNumber);
    connect.pst.setString(2, accName);
    connect.pst.setString(3, accAddress1);
    connect.pst.setString(4, accAddress2);
    connect.pst.setString(5, null);
    connect.pst.setString(6, accCity);
    connect.pst.setString(7,accCounty);
    connect.pst.setString(8, accPostCode);
    connect.pst.setString(9, accContact);
    connect.pst.execute();
    JOptionPane.showMessageDialog(null, "Saved");

}catch(Exception e){
    JOptionPane.showMessageDialog(null, e);
}

This works just fine and inserts records into the accounts table. Next I have this piece of code which is meant to update the orderstable table which contains 6 fields; Order Number, AccNo, Incvoice Number, Description, Amount, VAT. The Order Number field has been set to auto increment The code:

String accNumber = jTextField29.getText();
String invNo = jTextField20.getText();
String description = jTextField21.getText();
String vat = jTextField22.getText();
String amount = jTextField23.getText();

String query = "Insert into orderstable (Order Number, AccNo, Invoice Number, Description, Amount, VAT) values (?, ?, ?, ?, ?, ?)";
try{

    connect.pst = connect.con.prepareStatement(query);
    connect.pst.setString(1, "3");
    connect.pst.setString(2, accNumber);
    connect.pst.setString(3, invNo);
    connect.pst.setString(4, description);
    connect.pst.setString(5, amount);
    connect.pst.setString(6, vat);
    connect.pst.execute();
    JOptionPane.showMessageDialog(null, "Saved");

}catch(Exception e){
    JOptionPane.showMessageDialog(null, e);
}

This code above returns a SQL syntax error. I am not sure why. What could the reason be for this SQL syntax error?

as you said order number is on auto increment so you can insert a row with the specified order number( you shouldn't include that at all) — nafas
– nafas, Commented Nov 10, 2014 at 11:41
problem is with your field names Order Number, AccNo, Invoice Number you cannot hv spaces in names — tarzanbappa
– tarzanbappa, Commented Nov 10, 2014 at 11:47

Abhik Chakraborty · Accepted Answer · 2014-11-10 11:39:55Z

3

You can not have the column names with space and if you do so you need to have then in backticks

Insert into orderstable 
(`Order Number`, AccNo, `Invoice Number`, Description, Amount, VAT)

Check here for more details http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

answered Nov 10, 2014 at 11:39

Abhik Chakraborty

44.9k6 gold badges55 silver badges63 bronze badges

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

Comments

GameDroids · Accepted Answer · 2014-11-10 13:26:32Z

0

The auto increment field order number should not be include in the attribute section.

Your query should look like this

String query = "Insert into orderstable (AccNo, Invoice Number, Description, Amount, VAT) values (?, ?, ?, ?, ?)";

order number column should not be include in field list, because it is auto increment

edited Nov 10, 2014 at 13:26

GameDroids

5,6626 gold badges44 silver badges62 bronze badges

answered Nov 10, 2014 at 11:49

ayaz321

13 bronze badges

2 Comments

Joe Taras Over a year ago

Please explain better your answer

ayaz321 Over a year ago

your query should be like this String query = "Insert into orderstable (AccNo, Invoice Number, Description, Amount, VAT) values (?, ?, ?, ?, ?)"; order number colm should not include in field list because it is auto increment

Collectives™ on Stack Overflow

MySQL Query Through Java Syntax Error

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related