vishal modi
vishal modi

Reputation: 5

How to delete selected data from database using jsp?

jdbcData.jsp this shows the result of whole rows and columns of table reg with action link delete. I want to delete that only data row on when clicked on delete link. What should i do?

note: second scriptlet is named as jdbcDelete.jsp.

jdbcData.jsp

 <% 
     Statement st =null;
     ResultSet rs =null;
     String s1 = request.getParameter("fn");
     Class.forName("com.mysql.jdbc.Driver");
     Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/innoventa","root","root");

     st =con.createStatement();
     rs=st.executeQuery("select * from reg");
 %>
 <table border="1" align="center" cellpadding="10dx" summary="all posts" >

 <tr>
     <td><b>Id</b></td>
     <td><b>First Name</b></td>
     <td><b>Last Name</b></td>
     <td><b>Action</b></td>
 </tr>

 <% 
     while(rs.next()) {          
         int a = rs.getInt("id");
         String b =rs.getString("fname");
         String c=rs.getString("lname");
 %>
 <tr>
     <td><%out.println(a);%></td>
     <td><%out.println(b);%></td>
     <td><%out.println(c);%></td>
     <td><a href="jdbcDelete.jsp?id1=<%=a%>">Delete</a></td>
 </tr>
 <%
     }
 %>
 </table>
 <%
     st.close();
     con.close();
 %>

jdbcDelete.jsp

<% Integer dlt =(Integer)request.getAttribute("id1");
    ResultSet rs=null;
    Statement st=null;

    rs = st.executeQuery("delete from reg where id='"+dlt+"'");
    response.sendRedirect("jdbcData.jsp");
%>

Upvotes: 0

Views: 5352

Answers (2)

Naman
Naman

Reputation: 2203

There are number of reason why your code is not working.

  1. In jdbcDelete.jsp there is no connection object present so you need to establish connection first

    <% 
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/innoventa","root","root");
        Statement st = con.createStatement();
    %>
    
  2. You are using request.getAttribute("id1"); to get ID and you have not set any attribute in page jdbcData.jsp so you will get null. You need to use request.getParameter("id1"); instead, request.getParameter("id1"); always returns String so you need to parse String to int. To do that

    int dlt = Integer.parseInt(request.getParameter("id1"));
    
  3. You are executing executeQuery which returns ResultSet.

Executes the given SQL statement, which returns a single ResultSet object.

Here you need to use executeUpdate which is used to execute DML commands like INSERT, UPDATE AND DELETE and it returns int.

int i = st.executeUpdate("delete from student where studentid="+dlt);

So finally you jdbcDelete.jsp looks like:

<%@ page import="java.sql.*" %>
<% 
    int dlt = Integer.parseInt(request.getParameter("id1"));
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/innoventa","root","root");

    Statement st = con.createStatement();;  

    int i = st.executeUpdate("delete from student where studentid="+dlt);
    if (i != 0) {
        response.sendRedirect("jdbcData.jsp");
    } else {
        out.print("Data not deleted successfully");
    }
%>

Suggestion: It is bad practice to use java code in JSP instead use servlets, also use preparedstatement but as you are learning by time you will learn.

Hope this will help.

Upvotes: 2

Anptk
Anptk

Reputation: 1123

You can re-write your code like this,

<% Integer dlt =(Integer)request.getAttribute("id1");
ResultSet rs=null;
Statement st=null;

PreparedStatement ps=con.prepareStatement("delete from reg where id=?")
ps.setInt(1,dlt);
ps.executeUpdate();

response.sendRedirect("jdbcData.jsp");
%>

NB:-For database operation you can write services or DAO And please go through MVC architecture

Upvotes: 0

Related Questions