SQL Exception error

Question

i am trying to Update my MS Access database. I want to know why do i get Syntax error

[java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in 
UPDATE statement.]

even when the syntax is correct

class modify implements ActionListener
{
    public void actionPerformed(ActionEvent ae)
    {
        try{

            Integer num1=Integer.parseInt(tfdid.getText());
            if(num1.equals(null))
            {
                System.out.println("num");
                throw new BlankException();
            }


            String name1=tfname.getText();
            int a;
             a=name1.charAt(0);
            if(name1.equals("") || a==32)
                {
                    throw new BlankException();
                }
            else
            {
                for(int i=0; i<name1.length(); i++)
                {
                    boolean check = Character.isLetter(name1.charAt(i));
                    a=name1.charAt(i);
                    System.out.print("  "+a);
                    if(!((a>=65 && a<=90) || (a>=97 && a<=122) || (a==32) ||(a==46)))
                    {
                      throw new NameEx();
                    }

                }
            }


            String addr1=taadd.getText();
            if(addr1.equals(null))
            {
                System.out.println("addr");
                throw new BlankException();
            }

            String contact1=tftel.getText();
            String fax=tfax.getText();
            String dateorder=tfdateadd.getText();
            String dod=tfdod.getText();
            String order=taorder.getText();
            String rate = tfrate.getText();
            String quantity = tfquantity.getText();
            String paytype=chrt.getSelectedItem();
            String itemcode=tfitemcode.getText();

            String discount=tdiscount.getText();
            if(discount.equals(null))
            {
                System.out.println("discount");
                throw new BlankException();
            }

                System.out.println("NUm="+num1);            
                System.out.println("name="+name1);          
                System.out.println("addr="+addr1);      
                System.out.println("contact="+contact1);        
                System.out.println("fax="+fax); 
                System.out.println("orderdate="+dateorder); 
                System.out.println("dod="+dod);     
                System.out.println("order="+order);             
                System.out.println("rate="+rate);               
                System.out.println("quantity="+quantity);               
                System.out.println("pay="+paytype);
                System.out.println("itemcode="+itemcode);
                System.out.println("discount="+discount);

            //Statement st1=cn.createStatement();   

            String str="UPDATE DOC SET Name=?,Address=?,Contact=?,Fax=?,Order_date=?,Delivery_date=?,Order=?,Rate=?,Quantity=?,
            Payment_method=?,Item_code=?,Discount=? WHERE Vendorid=?";
            Statement st1= cn.createStatement();

            PreparedStatement psmt=cn.prepareStatement(str);

            psmt.setString(1,name1);
            psmt.setString(2,addr1);
            psmt.setString(3,contact1);
            psmt.setString(4,fax);
            psmt.setString(5,dateorder);
            psmt.setString(6,dod);
            psmt.setString(7,order);
            psmt.setString(8,rate);
            psmt.setString(9,quantity);
            psmt.setString(10,paytype);
            psmt.setString(11,itemcode);
            psmt.setString(12,discount);
                psmt.setInt(13,num1);


            psmt.executeUpdate();

            new SuccessDialog2();


            }catch(SQLException sq)
        {
            String message = "Enter Valid Vendor ID and Contact.";
            JOptionPane.showMessageDialog(new JFrame(), message, "ERROR!",
            JOptionPane.ERROR_MESSAGE);
            System.out.println(sq);
        }
        catch(BlankException be)
        {
            new ErrorDialog2();
        }
        catch(NumberFormatException nfe)
        {
            new ErrorDialog();
        }
        catch(NameEx ne)
        {
            new ErrorDialog1();
        }
        catch(Exception e)
        {
            System.out.println(e);
            new EDt();
        }

    }
}   
}

yunzen · Accepted Answer · 2014-04-16 05:12:48Z

3

NAME is a reserved word in Access SQL, so if it is being used as a column name then it must be enclosed in square brackets, e.g.,

String str="UPDATE DOC SET [Name]=?, ...

This is the case for ORDER and RATE AS well. So bracket those two, too.

edited Apr 16, 2014 at 5:12

yunzen

33.5k13 gold badges79 silver badges114 bronze badges

answered Apr 12, 2014 at 13:24

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

2 Comments

HansUp Over a year ago

Two of the other columns are named Order and Rate. Bracket those names, too. Or qualify them with the table name or alias.

yunzen Over a year ago

@TejalN mark this answer as correct, as I inserted the working comment into the answer.

An SO User · Accepted Answer · 2014-04-12 13:20:01Z

0

Are you sure all are String values in your database? If not, you need to use setInt(), setLong(), etc :)

Your quantity has a String value which seems a little odd. :)

answered Apr 12, 2014 at 13:20

An SO User

25.1k38 gold badges138 silver badges230 bronze badges

Collectives™ on Stack Overflow

SQL Exception error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related