Error in SQL syntax while connecting to database (JAVA)

Question

I'm trying to create a java program that connects to MySQL database. But its giving me an error, "You have an error with your SQL syntax", and is referring to this line:

String sql = "INSERT INTO items_in_hand (Item Name, Price (each), Quantity (available)) VALUES (?,?,?)";

Here's the block:

DefaultTableModel model = (DefaultTableModel) itemTable.getModel();
 if(!txt_item.getText().trim().equals("")){
 try{
 String sql = "INSERT INTO items_in_hand (Item Name, Price (each), Quantity (available)) VALUES (?,?,?)";
 st = con.prepareStatement(sql);
 st.setString(1,txt_item.getText()); 
 st.setString(2,txt_price.getText()); 
 st.setString(3,txt_quantity.getText());
 st.execute();
 JOptionPane.showMessageDialog(null , "Saved.");
 //model.addRow(new Object[] {txt_item.getText(),txt_price.getText(),txt_quantity.getText()});
 }catch(Exception ex){
 JOptionPane.showMessageDialog(null , ex);
 }
 }

Can anyone help?

• Praba: Can you execute that query directly on your database, using mysql client? What does (each) and (available) mean in that insert statement?
• user330315: Price (each) is an invalid column name due to the space and parenthesis. You need to quote that, e.g. "Price (each)" (if ANSI mode is enabled)
• impulse: "Price (each)" and "Quantity (available)" are column names in the table
• nablex: Horrible column names but apart from that if you have non-standard column names (like this) you need to quote them. Otherwise it will be interpreted as part of the syntax which is obviously wrong.
• impulse: Thought there are no restrictions with column names, i had no background with mysql. Thanks for the hint!

Answer 1

Replace the query

String sql = "INSERT INTO items_in_hand (Item Name, Price (each), Quantity (available)) VALUES (?,?,?)";

to like below

 String sql = "INSERT INTO items_in_hand (Item, Price, Quantity) VALUES (?,?,?)";

Assuming column Item, Price and Quantity are present in the items_in_hand table.