CODEWITHSUNDEEP
javams-accessproject
Anmol Khanna
Anmol Khanna

Reputation: 95

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

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)

Upvotes: 0

Views: 93

Answers (2)

luk2302
luk2302

Reputation: 57164

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

Upvotes: 1

Gustav
Gustav

Reputation: 55921

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" ...

Upvotes: 0

Related Questions