Hi everone,
Currently i am working on an Pharmacy management project in MVC2 model i have written an servlet code to fetch,insert,update,delete MySQL table contents from the JSP page but my problem is i could insert the data successfully but i am failing at Update and delete here is my code someone kindly help me to resolve this problem

<%-- 
    Document   : contactus
    Created on : March 13, 2014, 10:36:53 AM
    Author     : Dinesh
--%>

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Pharmacy Description</title><script type="text/javascript" src="js/general.js"></script>
  <script language="JavaScript" src="js/security.js"></script>
<link rel="stylesheet" type="text/css" href="style.css" />
        <style type="text/css">
<!--
.style12 {
    color: #FFFFFF;
    font-weight: bold;
    font-size: 18px;
    font-style: italic;
}
.style13 {
    color: #000000;
    font-weight: bold;
}
.style14 {color: #000000}
.style18 {
    color: #336600;
    font-weight: bold;
    font-size: 24px;
}
-->
        </style>
</head>
    <body bgcolor="#CCCCCC"><form action="pharmacy" method="post"><%
   String ok=(String)request.getAttribute("ok");
   String msg="";
   String a="";
   String b="";
    if(ok!=null)
   {
   msg=(String)request.getAttribute("msg");
    a=(String)request.getAttribute("a");
   b=(String)request.getAttribute("b");
     }
    %>
    <%
String flag1=(String)request.getAttribute("ss1");
     String s1 = "";
        String s2 = "";
        String s3 = "";

 String s4 = "";
        String s5 = "";
        String s6 = "";                         
        String s7 = "";    


    if(flag1!=null)
    {       
        s1=(String)request.getAttribute("s1");
        s2=(String)request.getAttribute("s2");
        s3=(String)request.getAttribute("s3");
        s4=(String)request.getAttribute("s4");
        s5=(String)request.getAttribute("s5");
        s6=(String)request.getAttribute("s6");
                s7=(String)request.getAttribute("s7");



    }
 %>
    <table width="966" border="0" align="center" bgcolor="#FFFFFF">
      <tr>
        <td width="960"><table width="960" border="0">
          <tr>
            <td width="954"><table width="950" border="0">
              <tr>
                <td width="292"><img src="image/1_4.jpg" height="131"/></td>
                <td width="648"><img src="images/wellcare.jpg" height="131"/></td>
              </tr>
            </table></td>
          </tr>
          <tr>
            <td><table width="954"  border="0"  align="center" >
  <tr>
    <td width="99" height="28"><div align="center" class="style6"><a href="index.jsp" class="style5 style13">Home</a></div></td>
    <td width="139"><div align="center" class="style6 style14"><a href="patientinform.jsp" class="style5 style13"><strong>Patient Details </strong></a></div></td>
    <td width="141"><div align="center" class="style6 style14"><a href="masterscreen.jsp" class="style5 style13""><strong>Master Screen</strong></a></div></td>
    <td width="89"><div align="center" class="style6 style14"><a href="registration.jsp" class="style5 style13"><strong>Register</strong></a></div></td>
    <td width="147"><div align="center" class="style6 style14"><a href="billpayment.jsp" class="style5 style13"><strong>Bill Payment</strong></a></div></td>
    <td width="145"><div align="center" class="style6 style14"><a href="hospital.jsp" class="style5 style13"><strong>Hospital</strong></a></div></td>
    <td width="164"><div align="center" class="style6 style14"><a href="login.jsp" class="style5 style13"><strong>Log out </strong></a></div></td>
  </tr>
</table></td>
          </tr>
        </table></td>
      </tr>
      <tr>
        <td height="260" background="image/article5.png"><table width="960" border="0">
  <tr>
    <td width="356" height="248">&nbsp;</td>
    <td width="596"><p>&nbsp;</p>
      <p><span class="style12">The clinical summary is a valuable communication tool that supports continuity of patient care by providing relevant and actionable information.</span></p>
      <br><br>
      <br>
          <p>&nbsp;</p></td>
  </tr>
</table>
</td>
      </tr>
      <tr>
        <td height="31" ><table width="959" height="482" border="0">
          <tr>
            <td width="949" height="29"><table width="934" border="0" >
                <tr>
                  <td width="196" height="21"><a href="pharmacydescribtion.jsp"><strong>Add Pharmacy Information </strong></a></td>
                  <td width="197"><a href="testprescribed.jsp"><strong>Add Test Information </strong></a></td>
                  <td width="163"><a href="contactdetails.jsp"><strong>Add Contact Detail </strong></a></td>
                  <td width="177"><a href="department.jsp"><strong>Add Department Detail </strong></a></td>
                  <td width="179"><a href="ambulance.jsp"><strong>Add Ambulance Detail </strong></a></td>
                </tr>
            </table></td>
          </tr>
          <tr>
            <td><table width="634" border="0" align="center">
                    <tr>
                      <td width="628" height="32"><div align="center" class="style2"><strong>Add Pharmacy Detail </strong></div></td>
                    </tr>
                    <tr>
                      <td height="220"><table width="507" border="0" align="center">
             <tr>
              <td width="117" height="34"><span class="style6"><strong>Pharmacy ID </strong></span></td>  
               <%
 int pro=0;
 int pro1=0;
        try {
            int flag = 0;

            String url = "jdbc:mysql://localhost:3306/phr";
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, "root", "root");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select max(pid) from pharmacy");
  %>  <%

          if (rs!=null) {  while(rs.next()){
                pro=Integer.parseInt(rs.getString(1));
                //pro1=pro++;
                    s1=pro+"";
                                                                               }
                }else{
                pro1=1000;
                s1=pro1+"";
                }
           %>
              <td width="211"><label>
                <input type="text" name="a1" id="a1" class="displayValue" maxlength="10"  style="width:180px" onKeyPress="return numbersonly();" onChange="return val_zero('a1','err_a1');" 
                                            onblur="return val_mobile('a1','err_a1');" value="<%=s1%>"/>
                &nbsp;&nbsp;&nbsp;<span id="err_a1"></span>  
              </label></td><td>&nbsp;&nbsp;<input type="submit" name="search" value="Search" class="buttons" onClick="return validatePage('a1','err_a1','Should Not Be Blank');"  />  </td><%
        }
        catch(Exception e)
        {
        out.println(e);
        } %>            </tr>
            <tr>
              <td width="117" height="30"><span class="style6"><strong>Medicine Name</strong></span></td>
              <td width="211"><label>
                <input name="a2" type="text" id="a2" class="displayValue" maxlength="15" style="width:180px"  onKeyPress="return name_val(event);return nospecialcharacters();" onChange="return val_zero('a2','err_a2');"     onblur="return txt_empty('a2','err_a2','Name Should Not Be Blank');" value="<%=s2%>"/>
  &nbsp;&nbsp;&nbsp;<span id="err_a2"></span>
              </label></td><td>&nbsp;&nbsp;  </td>
            </tr>


            <tr>
              <td height="59"><span class="style6"><strong>Description</strong></span></td>
              <td><label>
                 <textarea name="a3" rows="3" class="displayValue" id="a3" style="width:180px"  onBlur="return txt_empty('a3','err_a3','Address Should Not Be Blank');" onChange="return val_zero('a3','err_a3');"><%=s3%></textarea>
  &nbsp;&nbsp;<span id="err_a3"></span>
              </label></td><td>&nbsp;&nbsp;  </td>
            </tr>
            <tr>
              <td height="32"><span class="style6"><strong>Amount</strong></span></td>
              <td><label>
                  <input type="text" name="a4" id="a4" class="displayValue" maxlength="10"  style="width:180px" onKeyPress="return numbersonly();" onChange="return val_zero('a4','err_a4');" 
                                            onblur="return val_mobile('a4','err_a4');" value="<%=s4%>"/>
                &nbsp;&nbsp;&nbsp;<span id="err_a4"></span>  
              </label></td><td>&nbsp;&nbsp;  </td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td><label></label></td><td>&nbsp;&nbsp;  </td>
            </tr>
            <tr>
              <td>&nbsp;</td>
              <td>&nbsp;</td><td>&nbsp;&nbsp;  </td>
            </tr>
          <tr>
                      <td class="style2">
                        <input type="submit" name="Add" value="Add Pharmacy" class="buttons" onClick="return validatePage('a1|a2|a3|a4|a5','err_a1|err_a2|err_a3|err_a4|err_a5','Should Not Be Blank|Should Not Be Blank|Should Not Be Blank|Should Not Be Blank|Should Not Be Blank');"  />&nbsp;&nbsp;                                          </td>
                      &nbsp;&nbsp;
                      <td class="style2">
                          <input type="submit" name="update" value="Update" class="buttons" onClick="return validatePage('a1|a2|a3|a4|a5','err_a1|err_a2|err_a3|err_a4|err_a5','Should Not Be Blank|Should Not Be Blank|Should Not Be Blank|Should Not Be Blank|Should Not Be Blank');"  />
                          &nbsp;&nbsp;  &nbsp;&nbsp;  <input type="submit" name="Delete" value="Delete" class="buttons" onClick="return validatePage('a1','err_a1','Should Not Be Blank|Should Not Be Blank|Should Not Be Blank|Should Not Be Blank|Should Not Be Blank');"  /></td>
                      <td width="165" class="style2"><label>

                      <input type="reset" name="cancel" value="Cancel" />
                </label></td>
              </tr>
          </table></td>
                    </tr>
                  </table>
            <%=msg%>
            </td>
          </tr>
        </table></td>
      </tr>
          <tr>
        <td>&nbsp;</td>
      </tr>
    </table>

</form>
    </body>
</html>

here is the corresponding servlet code:

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

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

/**
 *
 * @author Dinesh
 */
public class pharmacy extends HttpServlet {

    /**
     * Processes requests for both HTTP
     * <code>GET</code> and
     * <code>POST</code> methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
      try {
              Connection con=null;
              String url="jdbc:mysql://localhost:3306/phr";
          String driver="com.mysql.jdbc.Driver";
          Class.forName(driver);
           con=(Connection)DriverManager.getConnection(url, "root","root");
                 Statement smt=con.createStatement();
            String a1=request.getParameter("a1");
             String a2=request.getParameter("a2");
                         String a3=request.getParameter("a3");
                           String a4=request.getParameter("a4");



 String s1 = null;
        String s2 = null;
        String s3 = null;

 String s4 = null;
        String s5 = null;
        String s6 = null;                         
String s7 = null;    
        ArrayList al=new ArrayList();



             if(request.getParameter("Add")!=null){
               //   System.out.println("insert into pharmacy values('" + a1 + "','" + a2 + "','" + a3 + "','" + a4 + "')");
          int v=smt.executeUpdate("insert into pharmacy values('" +a1 + "','" + a2 + "','" + a3 + "','" + a4 + "')");
                if(v==1){
                    request.setAttribute("ok", "1");
                    request.setAttribute("msg", "Successfully stored");
                   RequestDispatcher rs=request.getRequestDispatcher("pharmacydescribtion.jsp");
                    rs.forward(request, response);
                }else{
                       request.setAttribute("ok", "1");
                    request.setAttribute("msg", "Please enter the value");
                   RequestDispatcher rs=request.getRequestDispatcher("pharmacydescribtion.jsp");
                    rs.forward(request, response);
                }
             }else if(request.getParameter("update")!=null){
                 int k = smt.executeUpdate("update pharmacy set mname='"+a2+"',desc='"+a3+"',amount='"+a4+"'where pid='"+a1+"'");
                       if(k==1){
                    request.setAttribute("ok", "1");
                    request.setAttribute("msg", "Updated Successfully ");
                   RequestDispatcher rs=request.getRequestDispatcher("pharmacydescribtion.jsp");
                    rs.forward(request, response);
                }else{
                       request.setAttribute("ok", "1");
                    request.setAttribute("msg", "Please enter the value");
                   RequestDispatcher rs=request.getRequestDispatcher("pharmacydescribtion.jsp");
                    rs.forward(request, response);
                }

                }else if(request.getParameter("delete")!=null){
                      int k1 = smt.executeUpdate("delete from pharmacy where pid='"+a1+"'");
                       if(k1==1){
                    request.setAttribute("ok", "1");
                    request.setAttribute("msg", "Deleted Successfully");
                   RequestDispatcher rs=request.getRequestDispatcher("pharmacydescribtion.jsp");
                    rs.forward(request, response);
                }else{
                       request.setAttribute("ok", "1");
                    request.setAttribute("msg", "Please enter the value");
                   RequestDispatcher rs=request.getRequestDispatcher("pharmacydescribtion.jsp");
                    rs.forward(request, response);
                }
                }                 
                else if(request.getParameter("search")!=null){
                      ResultSet rs=smt.executeQuery("select * from pharmacy where pid='"+a1+"' ");

            while(rs.next())
            {
                         s1 =rs.getString(1);
                    s2 = rs.getString(2);
                    s3 =rs.getString(3);
                    s4 = rs.getString(4);



            }

            request.setAttribute("ss", "1");
                request.setAttribute("ss1", "1");
                request.setAttribute("s1", s1);
                request.setAttribute("s2", s2);
                request.setAttribute("s3", s3);
            request.setAttribute("s4", s4);

            RequestDispatcher requestdispatcher = request.getRequestDispatcher("pharmacydescribtion.jsp");
            requestdispatcher.forward(request, response);



                }



                else{


                       request.setAttribute("ok", "1");
                    request.setAttribute("msg", "Please enter the value");
                   RequestDispatcher rs=request.getRequestDispatcher("pharmacydescribtion.jsp");
                    rs.forward(request, response);
                }

        }catch(Exception e){
            e.printStackTrace();
        } finally {            
            out.close();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}

PS:I dint get any error but my web pages are stopping without an action but its supposed to reload and show an message as Updated success or Delete success but its working well with Insert script.

int k = smt.executeUpdate("update pharmacy set mname='"+a2+"',desc='"+a3+"',amount='"+a4+"'where pid='"+a1+"'");

"mname" are you shure your field is called like that?

Yes i am having an field named "mname" in my table "pharmacy"

before exeuctiong query output the prepared query. copy it and run in database administration tool. check do you find any error there

<%
string query = "update pharmacy set mname='"+a2+"',desc='"+a3+"',amount='"+a4+"'where pid='"+a1+"'";
%>
<%=query%>

<%
int k = smt.executeUpdate(query);
%>

I would also suggest adding some "close();" statements here and there.
have you tried to debug the application? does that method in your servlet actually call what you think it does, and does it passes the if statements you think it does?

I tried it in mysql prompt and its working there but failing at JSP page couldn't figure out the reason and yes i will close the connections and try it again

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.