syntax error in insert into command. The error could be while entering date value

90 views Asked by At
private void jButton2MouseClicked(java.awt.event.MouseEvent evt) {                                      
        conn = Connect.ConnectDB();
        Object selected1 = jComboBox1.getSelectedItem();
        Object selected2 = jComboBox1.getSelectedItem();
        selected1.toString();
        selected2.toString();
        String sql= "insert into Table1 ("
        +"ID,"
        +"Bill_No,"
        +"[Vendor_Name],"
        +"[Expense],"
        +"Amount,"
        +"TDS%,"

        +"Status,"
        +"Cheque_Stages,"
        +"Cno1,"+"Cno2,"+"Cno3,"+"Cno4,"
        +"[Cdate1],"+"[Cdate2],"+"[Cdate3],"+"[Cdate4],"
        +"[CAmount1],"+"[CAmount2],"+"[CAmount3],"+"[CAmount4],"+"[Purpose],"
        +"TDSamount,"+"Appamount)"
        +"values("+jTextField1.getText()+"','"+jTextField2.getText()+"','"+jTextField3.getText()
        +"','"+jTextField4.getText()
        +"','"+jTextField5.getText()+"','"+jTextField6.getText()+"','"//+"','"+selected1+"','"+selected2
        +"','"+jTextField9.getText()+"','"+jTextField10.getText()+"','"+jTextField11.getText()
        +"','"+jTextField12.getText()
        +"','"+jTextField15.getText()+"','"+jTextField16.getText()+"','"+jTextField17.getText()
        +"','"+jTextField18.getText()
        +"','"+jTextField7.getText()+"','"+jTextField19.getText()+"','"+jTextField20.getText()
        +"','"+jTextField21.getText()+"')"+jTextField8.getText()
        +"','"+jTextField13.getText()+"','"+jTextField14.getText()+"')";
        try{
            pst = conn.prepareStatement(sql);
            pst.executeQuery();
            JOptionPane.showMessageDialog(null, "SAVED");
            conn.close();
            UpdateJTable();
        }
        catch(Exception e){
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, e);
        }
    }      

i get error saying syntax error in insert statement along with this-

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
    at sun.jdbc.odbc.JdbcOdbc.SQLPrepare(JdbcOdbc.java:4837)
    at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:475)
    at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:443)
    at application.NewMDIApplication.jButton2MouseClicked(NewMDIApplication.java:1049)
    at application.NewMDIApplication.access$1900(NewMDIApplication.java:17)
    at application.NewMDIApplication$20.mouseClicked(NewMDIApplication.java:466)
    at java.awt.AWTEventMulticaster.mouseClicked(AWTEventMulticaster.java:270)
    at java.awt.Component.processMouseEvent(Component.java:6519)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    at java.awt.Component.processEvent(Component.java:6281)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4872)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4698)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4501)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2719)
    at java.awt.Component.dispatchEvent(Component.java:4698)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:747)
    at java.awt.EventQueue.access$300(EventQueue.java:103)
    at java.awt.EventQueue$3.run(EventQueue.java:706)
    at java.awt.EventQueue$3.run(EventQueue.java:704)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:720)
    at java.awt.EventQueue$4.run(EventQueue.java:718)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:717)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)
2

There are 2 answers

18
luk2302 On BEST ANSWER

Just by replacing all jTextFieldX.getText() with "" and printing the string you will get:

insert into Table1 (ID,Bill_No,[Vendor_Name],[Expense],Amount,TDS%,Status,Cheque_Stages,Cno1,Cno2,Cno3,Cno4,[Cdate1],[Cdate2],[Cdate3],[Cdate4],[CAmount1],[CAmount2],[CAmount3],[CAmount4],[Purpose],TDSamount,Appamount)values(','','','','','','','','','','','','','','','','','','')','','')

See here https://ideone.com/lik4SR

There are multiple things wrong here:

  • I dont know if TDS% is a valid column name or if it just needs to be enclosed in []
  • 23 parameters vs. 21? supplied values (varying depending on how you treat the fourth mistake)
  • a missing ' in the beginning of the values-part
  • a incorrectly placed ) in the middle of the values
  • everything is enclosed as string, nothing is representing a date or a number as the field names would suggest
  • you are escaping not a sinlge input -> sql injection

You therefore need to

  • fix the query syntax
  • fix the number of values
  • fix the value types, remove the ' for numbers and dates, add # for dates
6
Gustav On

For the CDateN fields, you will have to supply formatted string expressions of your date values. I'm not familiar with Java, but the resulting string for the date of today should be #2015/06/24# so those parts of the SQL will look like:

... "sometext",#2015/06/24#,#2015/06/01#,"someothertext" ...