Update page not showing any record info

[oaklandar]

I want to make an update page that lists records where I can click on the record link and edit the record. I have the Record viewing page set up and working where it shows the links but now when I click on the record link to get an update page it shows the correct argument ID in URL but the update page doesnt show the record. Here is what I have:
Record viewing page:

<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>

<% Connection connection = DriverManager.getConnection("jdbc:odbc:theDSN", "", "");
Statement statement = connection.createStatement();
ResultSet resultset = null;

resultset = statement.executeQuery("select * from tableOne"); 
%>

<%
while(resultset.next())
{ 
	%>
	<a href="updater.jsp?NameID=<%= resultset.getString("NameID") %>">
edit</A><br>

<%
}
%>

updater.jsp

<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>

<% Connection connection = DriverManager.getConnection("jdbc:odbc:theDSN", "", "");
Statement statement = connection.createStatement();
String myID = request.getParameter("NameID");
ResultSet resultset = statement.executeQuery("select * from tableOne where NameID like '" + myID + "'"); 
%>

<% while(resultset.next())
{ 
%>
<%= resultset.getString("NameID") %><br>

<% } %>
<%
resultset.close();
connection.close();
%>

The NameID value does not show up on the action page.

Please advise.

 

[vulcand4]

A couple of questions for clarification:
Does the String myID have anything in it after the following line of code is executed?

String myID = request.getParameter("NameID");

If so, are you sure your ResultSet comes back with one or more records in it?

 

[oaklandar]

Thanks, It now works.

 

[pgaec]

Why dont u put the resultset in the session - So that you dont have to make a database call again and again?

<%@ page import="java.sql.*" %>
<% ResultSet resultset = session.getAttribute("resultset",resultset);
if (resultset == null) {
 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection connection = DriverManager.getConnection("jdbc:odbc:theDSN", "", "");
Statement statement = connection.createStatement();
resultset = statement.executeQuery("select * from tableOne");
session.putAttribute("resultset",resultset);
}
%>

<%
while(resultset.next())
{
    %>
    <a href="updater.jsp?NameID=<%= resultset.getString("NameID") %>">
edit</A><br>

<%
}
%>

 

[sedj]

mexicomeat : That is one of the most dangerous and ill advised pieces of advise I've heard for a while ...

An open ResultSet object generally maintains an open link/connection to the database, and also maintains an open cursor object on the database. This could lead to anything from just wasteful use of resources, to an actual db crash due to too many open cursors.
What happens if the user closes the web browser between pages ? Your ResultSet leaves an open connection and open cursor on the database. It would not take many of these to bring down the db.

If you have code that is passed around in the session object, I suggest you remove it immediately. Going to a db twice is better than your suggestion.

If you go to a db *often*, then you should at least use a connection pool to limit the impact.

If you must carry around data in the session, then loop the result set, and add your data to an ArrayList, or ArrayList of beans encapsulating your set data.

Java/JSP may be fluffy and abstract - but please try to think about the consequences of your programming technique !



--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk