Reputation: 5
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
Reputation: 2203
There are number of reason why your code is not working.
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();
%>
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"));
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
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