Forum:

JDBC and Relational Databases

javax. servlet. Servlet Exception: java. sql. SQLException

Ranch Hand

Posts: 151

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi,
I am stuck here about 2-hours. Kindly help me.
In this my updateuser.jsp page I am retrieving the data from drop down list which is in my search user.jsp . I am taking form name of searchuser.jsp page for reference here which is form1. in my user table there is column userid, emailed, password:
Now here I am trying to display the data about the user selected from drop down list in searchuser.jsp
Here is an my updateuser.jsp:
<body>
<%
String form=request.getParameter("form1");

InitialContext context = new InitialContext();
DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/mynewdatabase");
Connection conn = ds.getConnection();
context.close();

Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery("select emailid,password from user where emailid='"+form+"'");
rs.next();
//// String EMAIL = rs.getString("emailid ");////
String PASSWORD = rs.getString("password");
%>
<table border="1">
<thead>
<tr>
<th><b>MODIFY USER</b></th>
</tr>
</thead>
<tbody>
<tr>
<td>E-MAIL</td>
<td><input type="text" name="email" value="<%=rs.getString("EMAIL")%>" size="30" /> </td>
</tr>
<tr>
<td>PASSWORD</td>
<td><input type="text" name="password" value="<%=rs.getString("PASSWORD")%>" /></td>
</tr>
<tr>
<td><input type="submit" value="SUBMIT" name="submit" /></td>
<td><input type="reset" value="RESET" name="res" /></td>
</tr>
</tbody>
</table>

</body>

But the error shows at marked line as:
javax.servlet.ServletException: java.sql.SQLException: Illegal operation on empty result set.
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:850)

Whether I am trying in the right way?
Any suggestion is highly appreciated
Thanks and Regards
Harshal
[ July 10, 2008: Message edited by: Bear Bibeault ]

Raghavan Muthu

Ranch Hand

Posts: 3389

I like...

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Instead of blindly using the

rs.next();

you check whether the resultset is empty or not before you could retrieve values from it. It should better be

while(rs.next())
{
//do whatever you want with the resultset here!
}

Also try to use a meaningful subject line so as to avoid confusions and reduce the # of characters on the title of the thread!

Again, whenever you post the Java code, try to use the code tags so that the code will look neatly!

Everything has got its own deadline including one's EGO!
[CodeBarn] [Java Concepts-easily] [Corey's articles] [SCJP-SUN] [Servlet Examples] [Java Beginners FAQ] [Sun-Java Tutorials] [Java Coding Guidelines]

Rajkumar balakrishnan

Ranch Hand

Posts: 445

I like...

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Try Raghavan's suggestion and what is "form1". Here you must put the name of the combo box from where you fetch the data.So instead of

String xx=request.getParameter("form1");

try using

String xx=request.getParameter("combo box name");

Hope this helps and use a meaningful heading for your post..

Never try to be a hard-worker. Be a smart-worker.
My Blog

Harshal Gurav

Ranch Hand

Posts: 151

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi,
Thanks a lot,
I updated it with rs.close();
But now showing the Error like:
java.lang.NullPointerException
com.mysql.jdbc.ResultSetImpl.buildIndexMapping(ResultSetImpl.java:709)
at the same line.
Need Your Help.
Regards
Haresh

Rajkumar balakrishnan

Ranch Hand

Posts: 445

I like...

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I updated it with rs.close();
But now showing the Error like:
java.lang.NullPointerException
com.mysql.jdbc.ResultSetImpl.buildIndexMapping(ResultSetImpl.java:709)
at the same line.

It's hard to say without relevant code. Do post your ResultSetImpl.java here or just tell us what is there?

Never try to be a hard-worker. Be a smart-worker.
My Blog

Harshal Gurav

Ranch Hand

Posts: 151

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi,
Here ia an searchuser.jsp page:

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
    <form name="searchuser" action="updateuser.jsp" method="POST">
        
        <table border="1" width="100">
<thead>
<tr>
<th>Seach criteria</th>
</tr>
</thead>
<tbody>
<tr>
<td><b>Username:</b> </td>
<td><select name="form1">
    <% Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mynewdatabase","root", "root123");
       st=con.createStatement();
           try {
        rs = st.executeQuery("Select * from user");
                           while (rs.next())
                                 {
                      %>
                               <option value = "<%=rs.getString("userid")%>" ><%=rs.getString(2)%></option>
 
                      <%
                           }
 
                       }
                       finally
                       {
                           if (rs != null)
                           {
                               rs.close();
                               rs = null;
                           }
                           if (st != null)
                           {
                               st.close();
                               st = null;
                           }
 
                       }
                   %>
                   </select>
      </td>
</tr>
<tr align="center">
<td><input type="submit" value="Submit" name="submit" /></td>
</tr>
 
</tbody>
</table>
 
        
</form>
    </body>
</html>

And here is an updateuser.jsp

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
         <%
         String form=request.getParameter("form1");
         
   InitialContext context = new InitialContext();
DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/mynewdatabase");
Connection conn = ds.getConnection();
context.close();
       
   Statement stmt=conn.createStatement();
   ResultSet rs=stmt.executeQuery("select emailid,password from user where emailid='"+form+"'");
   rs.close();
   String EMAIL = rs.getString("emailid ");
   String PASSWORD = rs.getString("password");
   %>
   <table border="1">
<thead>
<tr>
    <th><b>MODIFY USER</b></th>
</tr>
</thead>
<tbody>
<tr>
<td>E-MAIL</td>
<td><input type="text" name="email" value="<%=rs.getString("EMAIL")%>" size="30" /> </td>
</tr>
<tr>
<td>PASSWORD</td>
<td><input type="text" name="password" value="<%=rs.getString("PASSWORD")%>" /></td>
</tr>
<tr>
<td><input type="submit" value="SUBMIT" name="submit" /></td>
<td><input type="reset" value="RESET" name="res" /></td>
</tr>
</tbody>
</table>
 
             
    </body>
</html>

Any more clarification
Thanks
harshal

Raghavan Muthu

Ranch Hand

Posts: 3389

I like...

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Just check your resultset and try to print the results!

Harshal Gurav

Ranch Hand

Posts: 151

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi;
Thanks for your patience
can you more elaborate it?
waiting your responce.
Regards
harshal

Rajkumar balakrishnan

Ranch Hand

Posts: 445

I like...

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Just change the

<input type="text" name="email" value="<%=rs.getString("EMAIL")%>" size="30" />

<input type="text" name="email" value="<%=EMAIL%>" size="30" />

and for password too..

Then to retrieve the data from DB you must have a code like this

while(rs.next())
{
//retrieve data
}

First took your own time to learn JDBC concepts.This link may helpful to you..Please do read that before you post your next query. This will makes you more clear with JDBC.
[ July 10, 2008: Message edited by: Rajkumar balakrishnan ]

Never try to be a hard-worker. Be a smart-worker.
My Blog

Raghavan Muthu

Ranch Hand

Posts: 3389

I like...

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Harshal,

Looking at the error message you have pasted at first

javax.servlet.ServletException: java.sql.SQLException: Illegal operation on empty result set.

Can you please try executing the same SQL Query in the SQL Editor or with the MySQL Client and see are there any matching rows for the username you specify?

And did you change the code to first check the resultset being non-null before attempting to fetch any rows from it? You have seemed to use in your searchuser.jsp but not in updateuser.jsp.

I suggested to use

Try doing this way so that it will not throw any exceptions for the resultset being empty but attempted!

Important! Moreover, you are closing the resultset before you try to fetch the values from it. Uncomment that statement. See your code below

[ July 10, 2008: Message edited by: Raghavan Muthu ]

Rajkumar balakrishnan

Ranch Hand

Posts: 445

I like...

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

First do some research on the topic before you post queries here. This will make ranchers to avoid answering for some basic questions like closing resultset before you retrieve data from it. Please do read some nice tutorials available online.This is what we expect from the person who post their queries here... And try Raghavan's suggestion....It works like a charm...

Try to use MVC in future.. Using JDBC in JSP is not a good practice...

Never try to be a hard-worker. Be a smart-worker.
My Blog

Consider Paul's rocket mass heater.