problem in the update

[pradeep84]

Hi to all... in the following code... i hav problem in the update.. it is updating upto qty and it is not updating value.. it shows error like syntax error in the update statement.... help me to solve this problem

Regards
Pradeep

 try
  {
   String query = "insert into Gis values('"+s1+"','"+s2+"','"+s3+"','"+s4+"','"+s5+"','"+s6+"','"+s7+"','"+s8+"','"+s10+"')";
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   con=DriverManager.getConnection("jdbc:odbc:prism");
   stmt=con.createStatement();
   stmt1=con.createStatement();
 
   stmt1.executeUpdate(query);
 
   rs=stmt.executeQuery("select * from Stores");
 
  while(rs.next())
  {
 if((rs.getString("ITEMNAME").equals(s5))||(rs.getString("DEPT").equals(s2)))
 {
 
 int n=(rs.getInt(5));
 int n1=n-s6;
 int n2=(rs.getInt(6));
int n3=n2-s10;
 
 String str="UPDATE Stores SET QTY='"+n1+"',VALUE='"+n3+"' WHERE DEPT='"+s2+"'AND ITEMNAME='"+s5+"'"; 
 stmt2=con.createStatement();
 stmt2.executeUpdate(str);
JOptionPane.showMessageDialog(this,new String("GOODS ISSUED"));
 
 }
 }  
 }
 catch(ClassNotFoundException e)
{
  System.out.println(e);
}
catch(SQLException e)
{
  System.out.println(e);
} 
 }

[JosAH]

Line 23 looks suspicious to me, especially because n1 and n3 are ints which you
don't have to quote like '42' etc. in SQL.

kind regards,

Jos

[pradeep84]

i think thats not a problem... when i gave a single column to update.. it gets updated.. when i tried to update two columns... its showed an error message..

Thanks

Regards
Pradeep

[JosAH]

i think thats not a problem... when i gave a single column to update.. it gets updated.. when i tried to update two columns... its showed an error message..

Thanks

Regards
Pradeep

And what is the exact text of the error message?

kind regards,

Jos

[pradeep84]

This was the error message......

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


Regards
Pradeep

[JosAH]

This was the error message......

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


Regards
Pradeep

That one should be easy; print out that 'str' String containing your SQL query and
check what is wrong with it. I still suspect those quotes around those numbers.
Also check out the SQL dialect for Microsft Access and see what the syntax
for the update statement is supposed to be.

kind regards,

Jos

[pradeep84]

Thank you,.. i ll check it out....


kind regards,
Pradeep