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?

Can you execute that query directly on your database, using mysql client? What does (each) and (available) mean in that insert statement? — Praba
– Praba, Commented Sep 18, 2014 at 8:56
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) — user330315
– user330315, Commented Sep 18, 2014 at 8:59
"Price (each)" and "Quantity (available)" are column names in the table — impulse
– impulse, Commented Sep 18, 2014 at 9:01
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. — nablex
– nablex, Commented Sep 18, 2014 at 9:05
Thought there are no restrictions with column names, i had no background with mysql. Thanks for the hint! — impulse
– impulse, Commented Sep 18, 2014 at 9:09

Prabhakaran Ramaswamy · Accepted Answer · 2014-09-18 08:58:00Z

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.

answered Sep 18, 2014 at 8:58

Prabhakaran Ramaswamy

26.1k10 gold badges61 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

Error in SQL syntax while connecting to database (JAVA)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related