Integer won't save in SQL when inputting an integer

Question

I am a Java Programmer. I am very new in Java. I'm having problems in database saving. Here's my code:

JButton btnSave = new JButton("Save");
btnSave.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent arg0) {
    try{
        String query = "INSERT INTO roomlist (RoomNumber, RoomType, RoomCost, Limit, Description) values (?,?,?,?,?)";
        PreparedStatement pst = conn.prepareStatement(query);

        pst.setString(1, roomNoTextField.getText());
        pst.setString(2, roomTypeTextField.getText());
        pst.setInt(3, Integer.parseInt(roomCostTextField.getText())); //<--This is my problem
        pst.setInt(4, Integer.parseInt(limitTextField.getText()));//<--This is my problem
        pst.setString(5, descriptionTextArea.getText());

        pst.execute();

        JOptionPane.showMessageDialog(null, "Data Saved");

        }catch(Exception e){
            e.printStackTrace();
        }
    }
});
btnSave.setBounds(32, 300, 98, 26);
contentPane.add(btnSave);

And this is the error that I encountered:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Limit, Description) values ('101','Deluxe',350,2,'')' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192)
at hotelBillingAndReservation.addRoom$2.actionPerformed(addRoom.java:115)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

My problem is when I input an integer in a textbox, it won't work. What code should I use to save integer in a database?

Thank you so much for your help. :)

EDIT: The table structure is here.

RoomNumber - TEXT, RoomType - TEXT, RoomCost - INT, Limit - INT, Description - TEXT — jhenryj09
– jhenryj09, Commented Jul 30, 2016 at 6:25
Your problem is not where you think. Look at the error. It's in the SQL, not in your Java code. Limit is a reserved word, you have to escape it. — Sami Kuhmonen
– Sami Kuhmonen, Commented Jul 30, 2016 at 6:33

Stephen C · Accepted Answer · 2016-07-30 06:36:34Z

2

Your screen shot gives the answer (!)

Your column name "limit" is colliding with an SQL reserved word. Try this instead:

INSERT INTO roomlist (RoomNumber, RoomType, RoomCost, 
                      `Limit`, Description) values (?,?,?,?,?)

(Different SQL dialects have different ways of escaping keywords. That is the MySQL way ...)

Or better still, change the column name.

answered Jul 30, 2016 at 6:36

Stephen C

723k95 gold badges849 silver badges1.3k bronze badges

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

1 Comment

jhenryj09 Over a year ago

I get it. Thank you so much. :)

J.D.1731 · Accepted Answer · 2016-07-30 06:38:40Z

0

As you can see in your screenshot, column name 'Limit' is a keyword so it could be a problem. Please change your column name to different name.

edited Jul 30, 2016 at 6:38

answered Jul 30, 2016 at 6:23

J.D.1731

4155 silver badges14 bronze badges

2 Comments

jhenryj09 Over a year ago

This is it RoomNumber - TEXT, RoomType - TEXT, RoomCost - INT, Limit - INT, Description - TEXT

Stephen C Over a year ago

@jhenryj09 - How do you know that?

黄伟杰 · Accepted Answer · 2016-07-30 06:54:13Z

0

Hey you have an serious error,

INSERT INTO roomlist (RoomNumber, RoomType, RoomCost, Limit, Description) values (?,?,?,?,?)

As we all know,we can use the key word as a column,such as limit order.So when you insert roomlist the column Limit will be treat as the key world limit

The way to Solve this question is easy , change the column and remember don't use the key word to be column any more

answered Jul 30, 2016 at 6:54

黄伟杰

271 silver badge12 bronze badges

Collectives™ on Stack Overflow

Integer won't save in SQL when inputting an integer

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related