Forum:

JDBC and Relational Databases

error showing while retrieving the data from drop down list

Ranch Hand

Posts: 151

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi,
In the below jsp page, i am retrieving the data from drop down list of adduser.jsp page .but showing the error like:
javax.servlet.ServletException: java.sql.SQLException
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:850)
The error showing is at marked line.
Here is an code:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page language ="java" %>
<%@ page import="java.sql.*, javax.sql.*, javax.naming.*,java.io.*,java.util.*" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
 
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>      
              <table border="1">
          <% 
          String form=request.getParameter("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 emailid,password from user where emailid='"+form+"'");
             [B]String EMAIL = rs.getString("emailid");[/B]
                String PASSWORD = rs.getString("password");
                           while (rs.next())
                                 {
                      %>                          
                   
                 <tbody>
                  <tr><thead>
                  <tr>
                   <th><b>MODIFY USER</b></th>
                 </tr>
                   </thead>
                       <td>E-MAIL</td>
                     <td><input type="text" name="email" value="<%=rs.getString("userid")%>" ><%=rs.getString("EMAIL")%> size="30" /> </td>
                    </tr>
                    <tr>
                       <td>PASSWORD</td>
                       <td><input type="text" name="password" value="<%=rs.getString("userid")%>" ><%=rs.getString("PASSWORD")%> size="10" /></td>
                           </tr>
                         <tr>
                        <td><input type="submit" value="SUBMIT" name="submit" /></td>
                      <td><input type="reset" value="RESET" name="res" /></td>
                        </tr>
                        </tbody>            
                <%
                      }
                    }                       
                       finally
                       {
                           if (rs != null)
                           {
                               rs.close();
                               rs = null;
                           }
                           if (st != null)
                           {
                               st.close();
                               st = null;
                           }
 
                       }
                   %>
       
  
                  %>
</table>       
    </body>
</html>

Thanks
Harshal

Rajkumar balakrishnan

Ranch Hand

Posts: 445

I like...

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Here try this code instead of yours.. The code that you highlighted seems to be not good..You must use rs.next() inorder to fetch values from DB...But where you do it?...Please do read more about JDBC and correct way to use it...

Connection con;
Statement ps;
ResultSet rs;
 
String url="jdbc:mysql://localhost:3306/raj1";
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection(url,"root","root");
ps=con.createStatement();
ResultSetMetaData rm=null;
 
int flag=0;
rs=ps.executeQuery("select * from tbl_user");
flag=1;
if(flag!=0)
{
%>
<table width="438" border="1">
                      <tr>
                        <td width="89" class="style13">User ID </td>
                        <td width="121" class="style13">User Name </td>
                        <td width="116" class="style13">Pass word </td>
                        <td width="94" class="style13">Role</td>
                      </tr>
 <%
while(rs.next())
{
  %>
 <tr>
                                  <td><%=rs.getString(1)%></td>
                                  <td><%=rs.getString(2)%></td>
                                  <td><%=rs.getString(3)%></td>
                                  <td><%=rs.getString(4)%></td>
                               </tr>

<%
}
}
else
{
out.println("Database not found!!!!");
}
}
catch(Exception e)
{
System.out.println("Error");
}
%>

Also read the article in this -LINK- . It may be very helpful to you Harshal...
[ July 11, 2008: Message edited by: Rajkumar balakrishnan ]

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

Sravan Kumar

Ranch Hand

Posts: 121

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

This marked line reads from the ResultSet before you call rs.next() and hence results in an Exception. Even to read the first fetched record, you have to call rs.next() to move the cursor to the first record.

By the way, I see that you call rs.getString("userid") and rs.getString("EMAIL") later in the code, but where are you fetching them?

keep smilin :: sravan<br /><a href="http://sravanpens.blogspot.com" target="_blank" rel="nofollow">I scribble here</a>

Harshal Gurav

Ranch Hand

Posts: 151

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi
but i am using drop down list name(form1) as reference from the previous jsp page(searchuser.jsp) to retrive data from table i.e suppose i selected abc user as option in drop down list in searchuser.jsp page, i am expecting to get data related to that abc user in table in updateuser.jsp page .And accordingly i marked quary as:
now my page displaying the result as jsp
tag %>
Any suggestion is highly appreciated.
Thanks and Regards
Harshal

Rajkumar balakrishnan

Ranch Hand

Posts: 445

I like...

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Will you put your modified code here.So we can answer effectively..If you still not modify the code it's hard to give you the answer..

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

Harshal Gurav

Ranch Hand

Posts: 151

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi Rajkumar,
Thanks very much for your helping attitude.I want hertly appreciate your suggestion.
Now I am enclosing both jsp pages i.e 1. searchuser.jsp where moderator can select name of particular employee from drop down list and after submitting this page, in the next page i.e updateuser.jsp i will get all the details of employee like here email and password. My next aim is updating all detail of employee I can feed the updated date into the table:
Here is searchuser.jsp:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page language ="java" %>
<%@ page import="java.sql.*, javax.sql.*, javax.naming.*,java.io.*,java.util.*" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
 
<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:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page language ="java" %>
<%@ page import="java.sql.*, javax.sql.*, javax.naming.*,java.io.*,java.util.*" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
 
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>      
              <table border="1">
          <% 
          String form=request.getParameter("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 emailid as mail,password as pass from user where emailid='"+form+"'");
                
                           while (rs.next())
                                 {
                               String EMAIL = rs.getString("mail");
                                String PASSWORD = rs.getString("pass");
                                              
                   %>
                 <tbody>
                  <tr><thead>
                  <tr>
                   <th><b>MODIFY USER</b></th>
                 </tr>
                   </thead>
                       <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")%>" size="10" /></td>
                           </tr>
                         <tr>
                        <td><input type="submit" value="SUBMIT" name="submit" /></td>
                      <td><input type="reset" value="RESET" name="res" /></td>
                        </tr>
                        </tbody>
                        
                         
                <%
                      }
                    }                       
                       finally
                       {
                           if (rs != null)
                           {
                               rs.close();
                               rs = null;
                           }
                           if (st != null)
                           {
                               st.close();
                               st = null;
                           }
 
                       }
                   %>
       
  
                  %>
</table>       
    </body>
</html>

Kindly Suggest me Required modification.
Thanks and Regards
Harshal

Rajkumar balakrishnan

Ranch Hand

Posts: 445

I like...

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

The way you print is wrong..You already fetch the data from DB and store it in variable name EMAIL and PASSWORD..so you must print them in the Table or whatever else.
So change your code like this..

<input type=text value="<%=EMAIL%>">

and for password just replace EMAIL with password in the above code... Hope this might help you.. You need to learn more about JDBC that's what i suggest you from your earlier post itself..

[ July 11, 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 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Harsha,

By looking at your recent posts (which are almost similar in the forums), you can better take some time off to study the JDBC concepts and tutorial. It will really help you and facilitate you to proceed further without any hassles.

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]

Harshal Gurav

Ranch Hand

Posts: 151

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi Raghavan,
As You suggested code cause my jsp page is running dut showing output as
jsp syntax as %>
How can display this data in table form. I think my code inside the table is not working.
Kindly update me.
Thanks
Regards
Harshal
[ July 11, 2008: Message edited by: Harshal Gurav ]

Rajkumar balakrishnan

Ranch Hand

Posts: 445

I like...

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Wouldn't you try the code that i post here...? Please do read some tutorials and please check your JSP file line by line which may helpful to you in time..

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

Harshal Gurav

Ranch Hand

Posts: 151

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi Rajkumar,Raghavan
Thanks for your Suggestion.
I am Definately follow The Instruction as you suggest me.
I want to just know whether it is possible to display the data in updateuser.jsp page I tabular format which is related to the option selected in searchuser.jsp page.
Regards
Harshal

Rajkumar balakrishnan

Ranch Hand

Posts: 445

I like...

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Yes it's possible..You can display it in whatever format you want.. Your code seems to be ok and with the code and instructions that we gave itself will solve your problem. You still experience any problem with your code...huh...Then let us know about it clearly..

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

Raghavan Muthu

Ranch Hand

Posts: 3389

I like...

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Originally posted by Harshal Gurav:
showing output as
jsp syntax as %>

Just check the JSP syntax for proper matching/nesting of directives (<%.. %>) in your .jsp file!

How can display this data in table form. I think my code inside the table is not working.

You can display them using <table> tag and you have to iterate the data by using <tr><td> repeatedly as follows.

<blockquote>code:

<pre name="code" class="core">
//Say for example, you have a java.util.List which has the data to be displayed and you get it from session

<%
List myList = (List) session.getAttribute("EmployeeList");

//now you need to iterate the list..

Iterator iterator = myList.iterator();

%>


<table>



<th>Name</th>
<th>Age</th>
<th>Department</th>



<%
while(iterator.hasNext())
{
Employee emp = (Employee) iterator.next();
%>
<tr>
<td><%= emp.getName()%></td>
<td><%= emp.getAge()%></td>
<td><%= emp.getDepartment()%></td>
</tr>
<%
}
%>
</table>
</pre>

</blockquote>

Hope this sample code would help! If you don't understand what exactly this means, kindly take some time to read about JSP basics and especially scriptlets. It would help you to proceed further!

Consider Paul's rocket mass heater.