Displaying a ResultSet Problem

[Tom Bevan]

Hello,
Please bear with me if this is a simple problem, still getting my head round this stuff.
I have created a connection and successfully managed to query a MySQL db, using an HTML form with radio buttons.
However, the while loop gives me all of the results, (even if they are hidden - just moving down the record set), and I just want the one associated with the radio button.
On modification, I could not get the process.jsp to display anything other than the first correct answer.
So I'm stuck between all the resultsSet, or just the first part of it. Ideally, each radio button will select the correct record - but how?
Can anyone see where I am going wrong?


Here's some code - version 1 and 2.
Version 1:

<%@ page import="java.sql.*" %>
<table border="0">
<tr><th>Author_ID</th><th>First Name</th><th>Author_ID</th></tr>
<%
Connection conn=null;
Statement st = null;
ResultSet rs = null; 

  
   
   try 
   {
  Driver mySqlDriver = (Driver)(Class.forName( "com.mysql.jdbc.Driver" ).newInstance());
DriverManager.registerDriver( mySqlDriver );
       conn = DriverManager.getConnection( "jdbc:mysql://localhost/bibliography" );
       st = conn.createStatement();
   rs = st.executeQuery("SELECT FirstName, LastName, Author_ID FROM Author WHERE Author_ID <=4");
  

while(rs.next()) {


%>

<tr>
<td bgcolor="#CC0066" align="center">
<%
int authorid = Integer.parseInt(request.getParameter("author"));
if (authorid == (rs.getInt("Author_ID")))
 {out.println(rs.getInt("Author_ID")); } %></td>
 
<td bgcolor="#CC0066"  align="center">
<%
if (authorid == (rs.getInt("Author_ID")))
 { out.println(rs.getString("FirstName"));} %></td>
<td bgcolor="#CC0066" align="center">
<% 
if (authorid == (rs.getInt("Author_ID")))
{ out.println(rs.getString("LastName"));}%></td>

<br>
<% 
}
%>
</table>
 <%
 } catch (Exception ex) { 
     ex.printStackTrace();
  %>
</table>
Ooops, something really bad happened!:
<%
 } finally {
if (rs != null) rs.close();
if (st != null) st.close();
if (conn !=null) conn.close();
}

%>

... And this is Version 2:

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

<%
Connection conn=null;
Statement st = null;
ResultSet rs = null; 

  
   
   try 
   {
  Driver mySqlDriver = (Driver)(Class.forName( "com.mysql.jdbc.Driver" ).newInstance());
DriverManager.registerDriver( mySqlDriver );
       conn = DriverManager.getConnection( "jdbc:mysql://localhost/bibliography" );
       st = conn.createStatement();
   rs = st.executeQuery("SELECT FirstName, LastName, Author_ID FROM Author WHERE Author_ID <=4");
  

rs.next();
 {


%>

<%
int selection = Integer.parseInt(request.getParameter("author"));
int optionA = 1;
int optionB = 2; 
int optionC = 3;
int optionD = 4;
if (selection == optionA) {
out.println(rs.getString("FirstName"));
} else if (selection == optionB) {
out.println(rs.getString("FirstName"));
} else if (selection == optionC) {
out.println(rs.getString("FirstName"));
} else if (selection == optionD) {
out.println(rs.getString("FirstName"));
}
 %>

<br>
<% 
}
%>

 <%
 } catch (Exception ex) { 
     ex.printStackTrace();
  %>
Ooops, something really bad happened!:
<%
 } finally {
if (rs != null) rs.close();
if (st != null) st.close();
if (conn !=null) conn.close();
}

%>

 

[Manish Sharma]

Hi
i think there is some problem on this line
conn = DriverManager.getConnection( "jdbc:mysql://localhost/bibliography" );
MySql require user and password to connect with database. you can write the line like this
conn = DriverManager.getConnection( "jdbc:mysql://localhost/bibliography","username","password");
Also your DB password never be blank.
hope it will work.
with regards
Manish

Originally posted by Tom Bevan:
Hello,
Please bear with me if this is a simple problem, still getting my head round this stuff.
I have created a connection and successfully managed to query a MySQL db, using an HTML form with radio buttons.
However, the while loop gives me all of the results, (even if they are hidden - just moving down the record set), and I just want the one associated with the radio button.
On modification, I could not get the process.jsp to display anything other than the first correct answer.
So I'm stuck between all the resultsSet, or just the first part of it. Ideally, each radio button will select the correct record - but how?
Can anyone see where I am going wrong?


Here's some code - version 1 and 2.
Version 1:

<%@ page import="java.sql.*" %>
<table border="0">
<tr><th>Author_ID</th><th>First Name</th><th>Author_ID</th></tr>
<%
Connection conn=null;
Statement st = null;
ResultSet rs = null; 

  
   
   try 
   {
  Driver mySqlDriver = (Driver)(Class.forName( "com.mysql.jdbc.Driver" ).newInstance());
DriverManager.registerDriver( mySqlDriver );
       conn = DriverManager.getConnection( "jdbc:mysql://localhost/bibliography" );
       st = conn.createStatement();
   rs = st.executeQuery("SELECT FirstName, LastName, Author_ID FROM Author WHERE Author_ID <=4");
  

while(rs.next()) {


%>

<tr>
<td bgcolor="#CC0066" align="center">
<%
int authorid = Integer.parseInt(request.getParameter("author"));
if (authorid == (rs.getInt("Author_ID")))
 {out.println(rs.getInt("Author_ID")); } %></td>
 
<td bgcolor="#CC0066"  align="center">
<%
if (authorid == (rs.getInt("Author_ID")))
 { out.println(rs.getString("FirstName"));} %></td>
<td bgcolor="#CC0066" align="center">
<% 
if (authorid == (rs.getInt("Author_ID")))
{ out.println(rs.getString("LastName"));}%></td>

<br>
<% 
}
%>
</table>
 <%
 } catch (Exception ex) { 
     ex.printStackTrace();
  %>
</table>
Ooops, something really bad happened!:
<%
 } finally {
if (rs != null) rs.close();
if (st != null) st.close();
if (conn !=null) conn.close();
}

%>

... And this is Version 2:

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

<%
Connection conn=null;
Statement st = null;
ResultSet rs = null; 

  
   
   try 
   {
  Driver mySqlDriver = (Driver)(Class.forName( "com.mysql.jdbc.Driver" ).newInstance());
DriverManager.registerDriver( mySqlDriver );
       conn = DriverManager.getConnection( "jdbc:mysql://localhost/bibliography" );
       st = conn.createStatement();
   rs = st.executeQuery("SELECT FirstName, LastName, Author_ID FROM Author WHERE Author_ID <=4");
  

rs.next();
 {


%>

<%
int selection = Integer.parseInt(request.getParameter("author"));
int optionA = 1;
int optionB = 2; 
int optionC = 3;
int optionD = 4;
if (selection == optionA) {
out.println(rs.getString("FirstName"));
} else if (selection == optionB) {
out.println(rs.getString("FirstName"));
} else if (selection == optionC) {
out.println(rs.getString("FirstName"));
} else if (selection == optionD) {
out.println(rs.getString("FirstName"));
}
 %>

<br>
<% 
}
%>

 <%
 } catch (Exception ex) { 
     ex.printStackTrace();
  %>
Ooops, something really bad happened!:
<%
 } finally {
if (rs != null) rs.close();
if (st != null) st.close();
if (conn !=null) conn.close();
}

%>

 

[Tom Bevan]

Well, currently I don't need the password and name bit, it works ok on Tomcat without, it's only on my home computer at the moment.
It is the display of the result set that I'm having trouble with, but thank you anyway.

[Rohit Ahuja]

Hi Bevy,
Could u just change this particular statement,
rs = st.executeQuery("SELECT FirstName, LastName, Author_ID FROM Author WHERE Author_ID <=4");
and replace it with
rs = st.executeQuery("SELECT FirstName, LastName, Author_ID FROM Author WHERE Author_ID ="+request.getParameter("author"));
and tell whether it helps??
i hope the author parameter is not coming null from the previous page...
[ July 09, 2003: Message edited by: Deepak Acharya ]

[Tom Bevan]

Hi Deepak,
Brilliant, works fine now, thank you very much!!!
(Didn't know you vould do that.....cool!)
Humble thanks!