Hi All,

I am having a tablespace named Incident which has eight columns . The first column is index which i kept as auto increment. The columns are

1.index(auto_increment)
2.ticket_no (String)
3.date(String)
4.description (String)
5.priority (int)
6.state (String)
7.follow_up (String)
8.status (String)

Now i tried to insert test row in my database, i am getting the error as below

"Unknown column 's1' in 'field list'" . My code and detailed error message is below. Please check it.

public void actionPerformed(ActionEvent e) {
    // TODO Auto-generated method stub

    String s1,s2,s3,s4,s5,s6,s7;
    int i;

    if(e.getActionCommand().equals("incident")){


        s1= "test";
        s2= "test";
        s3 = "test";
        i=2;
        s5= "test";
        s6 = "test";
        s7= "test";

        try {
            statement.executeUpdate("INSERT INTO incident" + "(ticket_no, date, description, priority,state,follow_up,status)" + "values" + "(s1,s2,s3,i,s5,s6,s7)");
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }





com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 's1' in 'field list'
    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:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1811)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1725)
    at MainWindow.actionPerformed(MainWindow.java:466)
    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.AWTEventMulticaster.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$200(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$1.doIntersectionPrivilege(Unknown Source)
    at java.security.ProtectionDomain$1.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$1.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)

Your sql string is improperly formatted. There should be spaces between all the commands and names and if you want to load the values of s1, s2, . . . into the database you need to treat them as variable concatenations to a string. Also surrounding each value with ' and each column with " allows the value and names to contain spaces.

Try something like this:

statement.executeUpdate("INSERT INTO incident "
+ "(ticket_no, date, description, priority,state,follow_up,status) "
+ "values " + "(\'" + s1 + "\', \'" + s2 + "\', \'" + s3 + "\', \'"
+ i + "\', \'"+ s5 + "\', \'" + s6 + "\', \'" + s7 + "\')");

Edited 3 Years Ago by DarkLightning7

Yes DarkLighting7 is right that between every commands there must be space.Add space before values.
But there is one error in your code.Integer must not be enclosed with quotes.Also i think so escape symbol is not required here.

statement.executeUpdate("INSERT INTO incident"
            + "(ticket_no, date, description, priority,state,follow_up,status) "
            + "values('" + s1 + "','" + s2 + "', '" + s3 + "', "
            + i + ", '"+ s5 + "', '" + s6 + "', '" + s7 + "')");
Comments
Thanks for pointing the integer out completely forgot about it. I wasn't sure about the escape characters so i added them just in case. Thanks for correcting me will save some time in the future. :)
This question has already been answered. Start a new discussion instead.