lodelode
asked on
creating a drill down page to display details of a chosen user
Hi everyone,
i have a very urgent application which requires me to create a drill down logic.
To put it in a nutshell, part of my application allows administrators to create accounts for users to access the system.
In order to keep track of the number of users created, there will be a "list all users" page which allows the administrator to have a view of all the users that he/she has created, listed in a table format using the username.
how can i perform my SQL query and how do i code my jsp in such a way that for each user, the id associated with it will be included in the <a href> link so that the userid can be passed to the next page, and this id can be used as part of the sql query to retrieve the details of the selected user.
I am a fresh newbie to jsp but have prior knowledge on sql queries and how to query the database via jsp.
Assuming my database has two attributes: username and userid.
egs would be
List All Users
Martin
Kenneth
Eugene
martin kenneth and eugene will each have a unique userid, i would like to include this userid inside the a href link when the admin clicks on the name, so that the next page can display details of the chosen user...
Can anyone help me out on this issue? Thanks!
i have a very urgent application which requires me to create a drill down logic.
To put it in a nutshell, part of my application allows administrators to create accounts for users to access the system.
In order to keep track of the number of users created, there will be a "list all users" page which allows the administrator to have a view of all the users that he/she has created, listed in a table format using the username.
how can i perform my SQL query and how do i code my jsp in such a way that for each user, the id associated with it will be included in the <a href> link so that the userid can be passed to the next page, and this id can be used as part of the sql query to retrieve the details of the selected user.
I am a fresh newbie to jsp but have prior knowledge on sql queries and how to query the database via jsp.
Assuming my database has two attributes: username and userid.
egs would be
List All Users
Martin
Kenneth
Eugene
martin kenneth and eugene will each have a unique userid, i would like to include this userid inside the a href link when the admin clicks on the name, so that the next page can display details of the chosen user...
Can anyone help me out on this issue? Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In addition to jimmack here is how your code should look like in these two pages..
<%@ page import="java.sql.* "%>
<%
String dbDriver = "sun.jdbc.odbc.JdbcOdbcDri
// here dbDriver will be whatever driver you decide to use, I am assuming you are using s jdbc odbc bridge.
try{
Class.forName(dbDriver);
String dbURL = "jdbc:odbc:Test"; // Test will be replaced by your System DSN name that u will create.
Connection dbCon = DriverManager.getConnectio
Statement st = dbCon.createStatement();
ResultSet rs = st.executeQuery("SELECT username,userid FROM mytable");
%>
<TABLE>
<TR>
<TH>User name</TH>
</TR>
<%
while(rs.next())
{%>
<TR><TD><a href="second.jsp?uid=<%= rs.getString(2) %>><%= rs.getString(1) %></a></TD></TR>
<%}
st.close();
dbCon.close();
}catch(Exception e)
{}
%>
</TABLE>
now in your second.jsp
<%
String uid = request.getParameter("uid"
Now from here on you can again connect to database and then can get other data that you want to find.
Hope this helps.
<%@ page import="java.sql.* "%>
<%
String dbDriver = "sun.jdbc.odbc.JdbcOdbcDri
// here dbDriver will be whatever driver you decide to use, I am assuming you are using s jdbc odbc bridge.
try{
Class.forName(dbDriver);
String dbURL = "jdbc:odbc:Test"; // Test will be replaced by your System DSN name that u will create.
Connection dbCon = DriverManager.getConnectio
Statement st = dbCon.createStatement();
ResultSet rs = st.executeQuery("SELECT username,userid FROM mytable");
%>
<TABLE>
<TR>
<TH>User name</TH>
</TR>
<%
while(rs.next())
{%>
<TR><TD><a href="second.jsp?uid=<%= rs.getString(2) %>><%= rs.getString(1) %></a></TD></TR>
<%}
st.close();
dbCon.close();
}catch(Exception e)
{}
%>
</TABLE>
now in your second.jsp
<%
String uid = request.getParameter("uid"
Now from here on you can again connect to database and then can get other data that you want to find.
Hope this helps.
ASKER
Hi ,
thanks for the jsp codes, so far it is working but how can i check if my database has values first before i show my output..ie, if no users have been created, is there a way i can count the number of rows in the database and if its 0 then i can say something like "no users created"?
thanks for the jsp codes, so far it is working but how can i check if my database has values first before i show my output..ie, if no users have been created, is there a way i can count the number of rows in the database and if its 0 then i can say something like "no users created"?
The line
if (rs.next())
line that I included in my first post will determine if there are any records returned.
if (rs.next())
line that I included in my first post will determine if there are any records returned.
ASKER
Hi jimmack,
pardon me if i'm wrong but in your coding you use
while(rs.next()); not if(rs.next())
my methodology for creating the check is as follows, not sure if its correct
<%
boolean b = rs.first();
if (b == false){
out.println("no users created");
}
else {
rs.beforeFirst(); // points the cursor back to the first row
while(rs.next())
{
name = rs.getString("name");
username = rs.getString("username");
%></td></tr>
<TR><TD><a href="second.jsp?uid=<%= username %>"><%= name %></a></TD></TR>
<%
}}
statement.close();
connection.close();
%>
</TABLE>
i realized that if i use rs.first();
the cursor would be placed on the first set of data already, thats why i placed a beforeFirst() to make sure the cursor points to the first data in the database..
any opinions on this?
pardon me if i'm wrong but in your coding you use
while(rs.next()); not if(rs.next())
my methodology for creating the check is as follows, not sure if its correct
<%
boolean b = rs.first();
if (b == false){
out.println("no users created");
}
else {
rs.beforeFirst(); // points the cursor back to the first row
while(rs.next())
{
name = rs.getString("name");
username = rs.getString("username");
%></td></tr>
<TR><TD><a href="second.jsp?uid=<%= username %>"><%= name %></a></TD></TR>
<%
}}
statement.close();
connection.close();
%>
</TABLE>
i realized that if i use rs.first();
the cursor would be placed on the first set of data already, thats why i placed a beforeFirst() to make sure the cursor points to the first data in the database..
any opinions on this?
Hi lodelode,
You're absolutely right. My mistake. :-)
Another option (if you would like to keep track of the number of records displayed) could be:
<%
int count = 0;
while(rs.next())
{
name = rs.getString("name");
username = rs.getString("username");
count++;
%></td></tr>
<TR><TD><a href="second.jsp?uid=<%= username %>"><%= name %></a></TD></TR>
<%
}
if (count == 0)
{
out.println("no users created");
}
This also avoid moving the cursor about (not that this is particularly significant).
You're absolutely right. My mistake. :-)
Another option (if you would like to keep track of the number of records displayed) could be:
<%
int count = 0;
while(rs.next())
{
name = rs.getString("name");
username = rs.getString("username");
count++;
%></td></tr>
<TR><TD><a href="second.jsp?uid=<%= username %>"><%= name %></a></TD></TR>
<%
}
if (count == 0)
{
out.println("no users created");
}
This also avoid moving the cursor about (not that this is particularly significant).
Once you've created the table and it's appropriate headers, get your ResultSet and then iterate through it for each user.
In the example, the href element appends the "?uid=" and user id value to the end of your next URL (not known so shown as ...).
The username is encapsulated in the <A HREF> anchor.
Any good?