How to Extract row from table view using JSP Code

Hi Friends,

This is vinay here. Hope this mail finds u all in condition.
I am totally new to java world and trying to design a web page using NetBeans IDE 6.8.

My problem exist with retrival of row from a table on click of link.
Table Structure is given bellow:

User_ID First_Name Middle_Name Last_name Role Region Status Email
1 A B C D E F G Update Delete

On click of Update, entire row should get placed into the corresponding textboxes & dropdown.

I am unable to achieve the functionality. So please send me the code if anyone of you have. This is really urgent for me.

Thank in advance....

Regards
Vinay Kumar Rai

View Answers

May 22, 2010 at 12:03 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:#49743D;font-weight:bold;color:#ffffff;" onclick="editRecord(<%=rs.getString(1)%>);" ></td>
<td><input type="button" name="delete" value="Delete" style="background-color:#ff0000;font-weight:bold;color:#ffffff;" onclick="deleteRecord(<%=rs.getString(1)%>);" ></td>
</tr>
<%
}
%>
<%
}
catch (Exception e) {
e.printStackTrace();
}
%>
</table>
</form>
</body>
</html>

2)edituser.jsp:

<%@page language="java"%>
<%@page import="java.sql.*"%>
<form method="post" action="update.jsp">
<table border="1">
<tr><th>FirstName</th><th>LastName</th><th>Address</th><th>Email</th></tr>
<%
String id=request.getParameter("id");
int no=Integer.parseInt(id);
int sumcount=0;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test";, "root", "root");
String query = "select * from register where id='"+no+"'";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(query);
while(rs.next()){
%>
<tr>
<td><input type="text" name="firstname" value="<%=rs.getString(2)%>"></td>
<td><input type="text" name="lastname" value="<%=rs.getString(3)%>"></td>
<td><input type="text" name="address" value="<%=rs.getString(4)%>"></td>
<td><input type="text" name="email" value="<%=rs.getString(5)%>"></td>
<td><input type="hidden" name="id" value="<%=rs.getString(1)%>"></td>
</tr>
<tr>
<td><input type="submit" name="Submit" value="Update" style="background-color:#49743D;font-weight:bold;color:#ffffff;"></td>
</tr>
<%
}
}
catch(Exception e){}
%>
</table>
</form>
</form>

May 22, 2010 at 12:05 PM

continue..

3)update.jsp:

<%@page import="java.sql.*"%>
<%
String id=request.getParameter("id");
int no=Integer.parseInt(id);
String firstname=request.getParameter("firstname");
String lastname=request.getParameter("lastname");
String address=request.getParameter("address");
String email=request.getParameter("email");
try{
Connection conn = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root";, "root");

Statement st=null;
st=conn.createStatement();
st.executeUpdate("update register set firstname='"+firstname+"',lastname='"+lastname+"',address='"+address+"',email='"+email+"' where id='"+no+"'");
out.println("Data is updated successfully");
}
catch(Exception e){
out.println(e);
}
%>

4)deleteuser.jsp:

<%@page language="java"%>
<%@page import="java.sql.*"%>
<%
String id=request.getParameter("id");
int no=Integer.parseInt(id);
int sumcount=0;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test";, "root", "root");
String query = "select * from register where id='"+no+"'";
Statement st = conn.createStatement();
st.executeUpdate("DELETE FROM register WHERE id = '"+no+"'");
out.println("Record is deleted successfully");
}
catch(Exception e){}
%>

For the above code, we have used following database table:

CREATE TABLE `register` (
`id` bigint(20) NOT NULL auto_increment,
`firstname` varchar(40) default NULL,
`lastname` varchar(40) default NULL,
`address` varchar(100) default NULL,
`email` varchar(100) default NULL,
PRIMARY KEY (`id`)
)

Thanks