query

code that manage updation n deletion in one page.... that means i wants update query n delete query that work in one page at onclick update and delete button...

View Answers

August 5, 2010 at 4:32 PM

Hi Friend,

Try the following code:

1)user.jsp
<%@ page import="java.sql.*" %>
<html>
<head>
<script language="javascript">
function editRecord(id){
window.open('http://localhost:8080/examples/jsp/edituser.jsp?id='+id,'mywindow','width=500, height=350,toolbar=no,resizable=yes,menubar=yes');
}
function deleteRecord(id){
window.open('http://localhost:8080/examples/jsp/deleteuser.jsp?id='+id,'mywindow','width=500, height=350,toolbar=no,resizable=yes,menubar=yes');
}
</script>
</head>
<body>

<br><br>
<table border="1">
<tr><th>FirstName</th><th>LastName</th><th>Address</th><th>Email</th></tr>
<%
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";;;;
String db = "test";
String driver = "com.mysql.jdbc.Driver";
String userName ="root";
String password="root";

int sumcount=0;
Statement st;
try{
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url+db,userName,password);
String query = "select * from register";
st = con.createStatement();
ResultSet rs = st.executeQuery(query);
%>

<%
while(rs.next()){
%>
<tr><td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
<td><%=rs.getString(4)%></td>
<td><%=rs.getString(5)%></td>
<td><input type="button" name="edit" value="Edit" style="background-color:green;font-weight:bold;color:#ffffff;" onclick="editRecord(<%=rs.getString(1)%>);" ></td>
<td><input type="button" name="edit" value="Delete" style="background-color:red;font-weight:bold;color:#ffffff;" onclick="deleteRecord(<%=rs.getString(1)%>);" ></td>
</tr>
<%
}
%>
<%
}
catch (Exception e) {
e.printStackTrace();
}
%>
</table>
</form>
</body>
</html>

August 5, 2010 at 5:43 PM

Hi Friend,

Try the following code:

<%@ page import="java.sql.*" %>

<html>
<body>
<form method="post">
<table border="1">
<tr><th>FirstName</th><th>LastName</th><th>Address</th><th>Email</th></tr>
<%
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";;
String db = "test";
String driver = "com.mysql.jdbc.Driver";
String userName ="root";
String password="root";

int sumcount=0;
Statement st;
try{
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url+db,userName,password);
String query = "select * from register";
st = con.createStatement();
ResultSet rs = st.executeQuery(query);
%>

<%
while(rs.next()){
%>
<tr><td><input type="hidden" value="<%=rs.getString(1)%>" name="id"><input type="text" value="<%=rs.getString(2)%>" name="fname"></td>
<td><input type="text" value="<%=rs.getString(3)%>" name="lname"></td>
<td><input type="text" value="<%=rs.getString(4)%>" name="address"></td>
<td><input type="text" value="<%=rs.getString(5)%>" name="email"></td>
<td><input type="submit" name="edit" value="Edit" style="background-color:green;font-weight:bold;color:#ffffff;" ></td>
<td><input type="submit" name="edit" value="Delete" style="background-color:red;font-weight:bold;color:#ffffff;" ></td>
</tr>
<%
}
%>
<%
}
catch (Exception e) {
e.printStackTrace();
}
%>
</table>
</form>
</body>
</html>
<%
String id=request.getParameter("id");
String firstname=request.getParameter("fname");
String lastname=request.getParameter("lname");
String address=request.getParameter("address");
String email=request.getParameter("email");

String butt=request.getParameter("edit");
if(id!=null){
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root";, "root");
Statement stmt=conn.createStatement();
if(butt.equals("Edit")){
stmt.executeUpdate("update register set firstname='"+firstname+"',lastname='"+lastname+"',address='"+address+"',email='"+email+"' where id='"+id+"'");
out.println("Data is updated successfully");
}
else if(butt.equals("Delete")){
stmt.executeUpdate("DELETE FROM register WHERE id = '"+id+"'");
out.println("Record is deleted successfully");

}
}
catch(Exception ex){}
}
%>

Thanks