java.sql.SQLException: Syntax error or access violation: You have an error
Hi, i'm just started to learn MySql query together with JSP...
I always get this error when i using 2 prepared Stament in one if else statement... Any one kind to help .. i'll be really appreaciate!
[****
500 Servlet Exception
java.sql.SQLException: Syntax error or access violation: You have an error
in your SQL syntax near 'VALUES('CAAD10001','ADVANCE ',200.0,'LS','') '
at line 1
at com.mysql.jdbc.MysqlIO.sendCommand(Unknown Source)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(Unknown Source)
at com.mysql.jdbc.Connection.execSQL(Unknown Source)
at com.mysql.jdbc.PreparedStatement.executeUpdate(Unknown Source)
at com.mysql.jdbc.PreparedStatement.executeUpdate(Unknown Source)
at _do._do_0detail__jsp._jspService(/do/do_detail.jsp:116)
at com.caucho.jsp.JavaPage.service(JavaPage.java:75)
at com.caucho.jsp.Page.subservice(Page.java:497)
at com.caucho.server.http.FilterChainPage.doFilter(FilterChainPage.java:182)
at com.caucho.server.http.Invocation.service(Invocation.java:312)
at com.caucho.server.http.CacheInvocation.service(CacheInvocation.java:135)
at com.caucho.server.http.HttpRequest.handleRequest(HttpRequest.java:244)
at com.caucho.server.http.HttpRequest.handleConnection(HttpRequest.java:163)
at com.caucho.server.TcpConnection.run(TcpConnection.java:137)
at java.lang.Thread.run(Thread.java:536)
****]
The following is the program coding, and the error was indicated by [*** ***], could any one can help to give some comment on it , thanks a lot!
<%
sql_query1 = "INSERT INTO temp_2(temp2_co_no, temp2_proj_cde, temp2_dept_cde ,temp2_tran_typ, temp2_reason_cde) "+
"VALUES(?,?,?,?,?) ";
sql_query2 = "INSERT INTO temp_3(temp3_in_num,temp3_mat_desc,temp3_qty_rec,temp3_pur_um,temp3_rmk "+
"VALUES(?,?,?,?,?) ";
%>
<%
check_query = "SELECT temp2_seq_no FROM temp_2 ";
try {
check_rset = db.execSQL(check_query);
}
catch(SQLException e){
System.err.println("Error in Retrieve Temp Table 2 Query ");
System.err.println("line 51- do_detail.jsp!");
}
if(!check_rset.next()){
//if there is no record at the very beginning
PreparedStatement pstmt = conn.prepareStatement(sql_query1);
pstmt.setInt(1,conv_co_no);
pstmt.setInt(2,conv_proj_cde);
pstmt.setInt(3,conv_dept_cde);
pstmt.setString(4,tran_typ);
pstmt.setString(5,rea_cde);
pstmt.executeUpdate();
}
else{
//delete table first, then insert new data into the table again
delete_query = "DELETE FROM temp_2 ";
try{
rset = db.execSQL(delete_query);
}
catch(SQLException e){
System.err.println("Error in delete query query");
System.err.println("line 93 -- do_detail.jsp");
}
PreparedStatement pstmt = conn.prepareStatement(sql_query1);
pstmt.setInt(1,conv_co_no);
pstmt.setInt(2,conv_proj_cde);
pstmt.setInt(3,conv_dept_cde);
pstmt.setString(4,tran_typ);
pstmt.setString(5,rea_cde);
pstmt.executeUpdate();
}
%>
<%
check_query = "SELECT temp3_seq_no FROM temp_3 WHERE temp3_seq_no = 1";
try {
check_rset = db.execSQL(check_query);
}
catch(SQLException e){
System.err.println("Error in Retrieve Temp Table 3 Query ");
System.err.println("line 51- do_detail.jsp!");
}
if(!check_rset.next()){
for(int index=1; index<= Integer.parseInt(total_material_type); index++){
String temp3_in_num = request.getParameter("hid_matCode_in_num"+index);
String temp3_mat_desc = request.getParameter("item_no"+index);
String temp3_qty_rec = request.getParameter("rec_qty"+index);
String temp3_pur_um = request.getParameter("in_pur_um"+index);
String temp3_rmk = request.getParameter("rmk"+index);
double conv_qty_rec = Double.parseDouble(temp3_qty_rec);
PreparedStatement pstmt = conn.prepareStatement(sql_query2);
pstmt.setString(1,temp3_in_num);
pstmt.setString(2,temp3_mat_desc);
pstmt.setDouble(3,conv_qty_rec);
pstmt.setString(4,temp3_pur_um);
pstmt.setString(5,temp3_rmk);
[****** pstmt.executeUpdate();**********]
/*I have to remove this line then it will be no more error but then i could not update the information of this part!!!*/
}
}
else{
delete_query = "DELETE FROM temp_3 ";
try{
rset = db.execSQL(delete_query);
}
catch(SQLException e){
System.err.println("Error in delete query query");
System.err.println("line 93 -- do_detail.jsp");
}
for(int index=1; index<= Integer.parseInt(total_material_type); index++){
String temp3_in_num = request.getParameter("hid_matCode_in_num"+index);
String temp3_mat_desc = request.getParameter("item_no"+index);
String temp3_qty_rec = request.getParameter("rec_qty"+index);
String temp3_pur_um = request.getParameter("in_pur_um"+index);
String temp3_rmk = request.getParameter("rmk"+index);
double conv_qty_rec = Double.parseDouble(temp3_qty_rec);
PreparedStatement pstmt = conn.prepareStatement(sql_query2);
System.out.println("Add in Table 3 --- bottom");
pstmt.setString(1,temp3_in_num);
pstmt.setString(2,temp3_mat_desc);
pstmt.setDouble(3,conv_qty_rec);
pstmt.setString(4,temp3_pur_um);
pstmt.setString(5,temp3_rmk);
pstmt.executeUpdate();
}
}