• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL syntax error

 
Priya Prasad
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to modify the data of a form such that the modified data is not over-written on the previous data, but stored under different attribute so that when the user wants to view the data corresponding to a Scheme, he gets to see the original one and the last modified one. For that i have made a servlet which would modify my table. I think there is some error with the sql statement I am using. Can you please tell me the correct one/?


import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
*
* @author Priya
*/
public class ModifyData extends HttpServlet {
String AF_Scheme_Number, Modified_AF_Scheme_Type, Modified_GrantedBy, Modified_Af_Scheme_Details, Modified_Amount,
Modified_Approval_Date,Modified_Completion_Period, Modified_Original_Completion_Date, Modified_Remarks;
String query;
Connection conn;
Statement stmt;
ResultSet res;
DatabaseConnection dbconn;

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
try {

dbconn = new DatabaseConnection();
// UnitName = request.getParameter("UnitName");
AF_Scheme_Number = request.getParameter("Af_Scheme_Number");
Modified_AF_Scheme_Type = request.getParameter("Modified_AF_Scheme_Type");
Modified_GrantedBy = request.getParameter("Modified_GrantedBy");
Modified_Af_Scheme_Details = request.getParameter("Modified_Af_Scheme_Details");
Modified_Amount = request.getParameter("Modified_Amount");
Modified_Approval_Date = request.getParameter("Modified_Approval_Date");
Modified_Completion_Period = request.getParameter("Modified_Completion_Period");
Modified_Original_Completion_Date = request.getParameter("Modified_Original_Completion_Date");
Modified_Remarks = request.getParameter("Modified_Remarks");

conn = dbconn.setConnection();
stmt = conn.createStatement();
query = "insert into bkpl values Modified_AF_Scheme_Type='"+Modified_AF_Scheme_Type+"',Modified_GrantedBy='"+Modified_GrantedBy+"',Modified_Af_Scheme_Details='"+Modified_Af_Scheme_Details+"'"
+ " ,Modified_Amount='"+Modified_Amount+"',Modified_Approval_Date='"+Modified_Approval_Date+"', Modified_Completion_Period='"+Modified_Completion_Period+"'"
+ ", Modified_Original_Completion_Date='"+Modified_Original_Completion_Date+"', Modified_Remarks='"+Modified_Remarks+" where Af_Scheme_Number='"+AF_Scheme_Number+"';";
System.out.println(query);
int i = stmt.executeUpdate(query);
System.out.println(query);
}catch(Exception e){
System.out.println(e.getMessage());
request.setAttribute("Error", e);
RequestDispatcher rd = request.getRequestDispatcher("/error.jsp");
rd.forward(request, response);
}
finally {
RequestDispatcher rd = request.getRequestDispatcher("index.jsp");
rd.forward(request, response);
out.close();
}
}


@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}


@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}


@Override
public String getServletInfo() {
return "Short description";
}
}
 
bharat salunkhe
Greenhorn
Posts: 25
Eclipse IDE Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Priya Prasad wrote:I want to modify the data of a form such that the modified data is not over-written on the previous data, but stored under different attribute so that when the user wants to view the data corresponding to a Scheme, he gets to see the original one and the last modified one. For that i have made a servlet which would modify my table. I think there is some error with the sql statement I am using. Can you please tell me the correct one/?


import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
*
* @author Priya
*/
public class ModifyData extends HttpServlet {
String AF_Scheme_Number, Modified_AF_Scheme_Type, Modified_GrantedBy, Modified_Af_Scheme_Details, Modified_Amount,
Modified_Approval_Date,Modified_Completion_Period, Modified_Original_Completion_Date, Modified_Remarks;
String query;
Connection conn;
Statement stmt;
ResultSet res;
DatabaseConnection dbconn;

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
try {

dbconn = new DatabaseConnection();
// UnitName = request.getParameter("UnitName");
AF_Scheme_Number = request.getParameter("Af_Scheme_Number");
Modified_AF_Scheme_Type = request.getParameter("Modified_AF_Scheme_Type");
Modified_GrantedBy = request.getParameter("Modified_GrantedBy");
Modified_Af_Scheme_Details = request.getParameter("Modified_Af_Scheme_Details");
Modified_Amount = request.getParameter("Modified_Amount");
Modified_Approval_Date = request.getParameter("Modified_Approval_Date");
Modified_Completion_Period = request.getParameter("Modified_Completion_Period");
Modified_Original_Completion_Date = request.getParameter("Modified_Original_Completion_Date");
Modified_Remarks = request.getParameter("Modified_Remarks");

conn = dbconn.setConnection();
stmt = conn.createStatement();
query = "insert into bkpl values Modified_AF_Scheme_Type='"+Modified_AF_Scheme_Type+"',Modified_GrantedBy='"+Modified_GrantedBy+"',Modified_Af_Scheme_Details='"+Modified_Af_Scheme_Details+"'"
+ " ,Modified_Amount='"+Modified_Amount+"',Modified_Approval_Date='"+Modified_Approval_Date+"', Modified_Completion_Period='"+Modified_Completion_Period+"'"
+ ", Modified_Original_Completion_Date='"+Modified_Original_Completion_Date+"', Modified_Remarks='"+Modified_Remarks+" where Af_Scheme_Number='"+AF_Scheme_Number+"';";
System.out.println(query);
int i = stmt.executeUpdate(query);
System.out.println(query);
}catch(Exception e){
System.out.println(e.getMessage());
request.setAttribute("Error", e);
RequestDispatcher rd = request.getRequestDispatcher("/error.jsp");
rd.forward(request, response);
}
finally {
RequestDispatcher rd = request.getRequestDispatcher("index.jsp");
rd.forward(request, response);
out.close();
}
}


@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}


@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}


@Override
public String getServletInfo() {
return "Short description";
}
}

YOU NEED TO WRITE UPDATE QUERY NOT INSERT
EX.
UPDATE erpl.bkpl SET Af_Scheme_Type='9',GrantedBy='9', Af_Scheme_Details='9',Amount=5,Approval_Date='9',Completion_Period='9',Original_Completion_Date='9',Remarks='9' WHERE Af_Scheme_Number=4
 
Swastik Dey
Rancher
Posts: 1637
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try preparedStatement, it's a better approach and also avoids SQL Injection.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic