• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL syntax error

 
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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";
}
}
 
Greenhorn
Posts: 25
Netbeans IDE Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 2270
20
Android Java ME Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Try preparedStatement, it's a better approach and also avoids SQL Injection.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic