Only 49 hours left in the trailboss' kickstarter!

New rewards and stretch goals. CLICK HERE!



  • Post Reply Bookmark Topic Watch Topic
  • New Topic

how can we delete data from database corresponding check box using html table on JSP Page.  RSS feed

 
Dileep Yadav
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Problem code:

JSP 1:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@page import="java.sql.*;" %>
<%!Connection con;%>
<%!Statement stmt = null;%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Search Data</title>
<script>
function myFunction()
{
var x = document.getElementById("ch").name;
document.getElementById("checks").innerHTML=x;
}
</script>

</head>
<body>
<form name="form1" method=post action="dele.jsp" >
<center>
<span style="color: #00c7be;font-weight:bold;"><h1>Patient Details</h1></span>
<br/>
<td> <label> <span style="color:darkolivegreen;font-weight:bold">Search By ID:</span></label> <input type="text" value=""/><input type="submit" value="Search"/>       <label> <span style="color:darkolivegreen;font-weight:bold">Search By Name:</span></label> <input type="text" value=""/> <input type="submit" value="Search"/>       <label> <span style="color:darkolivegreen;font-weight:bold">Search By Date:</span></label> <input type="text" value=""/><input type="submit" value="Search"/></td>
<hr width="2058px">
<div id="header" style="background-color:#FFA500;border:2px solid #a1a1a1;padding:5px 20px; background:#dddddd;
width:2030px;
border-radius:25px;">
<h1 style="margin-bottom:0;" align="left">
<input type="submit" value="Delete"/>



   
</h1>
</div>
<hr width="2058px">

<%
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// Connection con = DriverManager.getConnection("jdbc:odbc:PatientMaintenanceDetails");
String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=PatientMaintenanceDetails;user=sa;password=123;";
con = DriverManager.getConnection(connectionUrl);

ResultSet result;
System.out.println("Data ");
try
{

stmt = con.createStatement();
result=stmt.executeQuery("select *from PatientDetailsdata");
%>
<table border="1" >
<tr bgcolor="#C6C4C7" >
<td width="40px" >Check To Action</td>
<td width="40px">Patient ID</td>
<td width="40px">Date/Time</td>
<td width="40px">Name</td>
<td width="40px">Age</td>
<td width="40px">Gender</td>
<td width="40px">Blood Group</td>
<td width="40px">Guardian</td>
<td width="40px" >Date Of Daignostic </td>
<td width="40px">Cast</td>
<td width="40px">Address1</td>
<td width="40px">Address2</td>
<td width="40px">Area</td>
<td width="40px">District</td>
<td width="40px">Police Station</td>
<td width="40px">Pin</td>
<td width="40px">Phone</td>
<td width="40px">Mobile</td>
<td width="40px">Email ID</td>
<td width="40px">Address 1</td>
<td width="40px">Address 2</td>
<td width="40px">Area </td>
<td width="40px">District </td>
<td width="40px">Police Station</td>
<td width="40px">PIN</td>
<td width="40px">Phone</td>
<td width="40px">Mobile</td>
<td width="40px">Email ID</td>


</tr>

<%

System.out.println("hhhhhhhhhhhhhhhhhhhhhhh");
int i=0; while(result.next())
{
System.out.println("in while loop");
%>
<tr bgcolor="white">
<td><input type="checkbox" id="ch" name="check<%=i%>" value="<%= result.getString("PatientID") %>">
<td ><%=result.getString(1) %>
<td ><%=result.getString(2) %>
<td ><%=result.getString(3) %>
<td ><%=result.getString(4) %>
<td ><%=result.getString(5)%>
<td ><%=result.getString(6) %>
<td ><%=result.getString(7) %>
<td ><%=result.getString(8) %>
<td ><%=result.getString(9) %>
<td ><%=result.getString(10) %>
<td ><%=result.getString(11) %>
<td ><%=result.getString(12) %>
<td ><%=result.getString(13) %>
<td ><%=result.getString(14) %>
<td ><%=result.getString(15) %>
<td ><%=result.getString(16) %>
<td ><%=result.getString(17) %>
<td ><%=result.getString(18) %>
<td ><%=result.getString(19) %>
<td ><%=result.getString(20) %>
<td ><%=result.getString(21) %>
<td ><%=result.getString(22) %>
<td ><%=result.getString(23) %>
<td ><%=result.getString(24) %>
<td ><%=result.getString(25) %>
<td ><%=result.getString(26) %>
<td ><%=result.getString(27) %>


<% i++; %>

</tr>
<%

}
%>
</table>

<% }catch(Exception e)
{
e.printStackTrace();
}

%>
</center>
<input type="hidden" name="c" value="checks">
</form>
</body>
</html>

JSP 2:

<%--
Document : dele
Created on : Jan 22, 2014, 12:41:44 PM
Author : GMT10
--%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<%@ page import="java.sql.*"%>
<%! Connection con; %>
<%! Statement stmt;%>

<%! String deleteArray[]; %>

<%

deleteArray = request.getParameterValues("c");

String selected="";

if (deleteArray != null) {

if(deleteArray.length == 1) {

// Only one item is selected
selected = deleteArray[0];

} else {

// multiple items selected
for (int i = 0; i < deleteArray.length; i++) {

selected = selected+deleteArray[i];

if( i < deleteArray.length -1) {
selected += ",";
}
}
}
}





%>
<%
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// Connection con = DriverManager.getConnection("jdbc:odbc:PatientMaintenanceDetails");
String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=PatientMaintenanceDetails;user=sa;password=123;";
con = DriverManager.getConnection(connectionUrl);
stmt=con.createStatement();
stmt.executeUpdate("DELETE from PatientDetailsdata WHERE PatientID='"+selected+"';");
RequestDispatcher rd=request.getRequestDispatcher("deletesuccess.jsp");
rd.forward(request, response);
%>
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1.) Don't write Java code in JSPs. Put the code in a normal Java class and call that code from a servlet. Then when you get a problem post explaining what the error is.
2.) When you put the database code in a normal class, close all resources you used in a finally block or use the try with resources if on Java 7.
3.) Use a PreparedStatement when passing parameters to queries to prevent sql injection and make your code a bit faster.
 
Stefan Evans
Bartender
Posts: 1834
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Couple of additional comments.

The correct sql syntax for passing a comma separated set of values to a sql statement is with the 'in' keyword.
i.e. delete from patientDetailsData where PatientId in (42,69,7)
However that does not lend itself to a prepared statement.

The preferred method as per a prepared statement would preferably be executed within a loop.


 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!