java.sql.SQLException: Syntax error or access violation: You have an error
Posted by: Siyu Kim Ong
Date: March 18, 2005 04:07AM

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();
}
}

Options: ReplyQuote


Subject
Written By
Posted
java.sql.SQLException: Syntax error or access violation: You have an error
March 18, 2005 04:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.