Hi;
The below jsp page is compiling fine but executing it shows error asjavax.servlet.ServletException: java.sql.SQLException: Column 'Total' not found.

<%-- 
    Document   : evaluationeventtable
    Created on : Jul 24, 2008, 6:52:37 PM
    Author     : user1
--%>

<%@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>Table-Data</title>
    </head>
    <body>
    <%  int QNO;  
        String ID=request.getParameter("id"); 
        out.println(ID);
        String EVENTID=request.getParameter("event");
        out.println(EVENTID);
        Connection connection = null;
        Statement st = null;
        Statement st1 = null;
        Statement st2 = null;
        Statement st3 = null;
        Statement st4 = null;
        Statement st5 = null;
        ResultSet rs= null;
        ResultSet rs1= null;
        ResultSet rs2= null;
        ResultSet rs3= null;
        ResultSet rs4= null;
        ResultSet rs5= null;
        
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mynewdatabase","root", "root123");
          
                       st5=con.createStatement();
                       int count=0;
                     try {  
                         rs5=st5.executeQuery("select count(*) from Final where Eventid='"+EVENTID+"'");
                        
                        while(rs5.next())                            
                       {count=rs5.getInt(1);                                         
                        out.println(count);                                                                       
                         }                         
                         
                        } finally
                       {
                           if (rs5 != null)
                           {
                               rs5.close();
                               rs5 = null;
                           }
                           if (st5 != null)
                           {
                               st5.close();
                               st5 = null;
                           }

                       }                
                       st=con.createStatement();
                       
                try      {
               for( QNO=1;QNO<=count;QNO++);
                  
            rs = st.executeQuery("SELECT * FROM Questionbank where Questionid='"+ID+"'");
                           
              while ( rs.next() )
                     {                 
                  
                   %>
                         	<tr>
                      <input type="hidden" name="Qserino" value="<%=rs.getString("qserialno")%>"/>              
                     <td colspan="5"><b>Question:</b>
                      <%=rs.getString("questionname")%>
		    </td>
                     </tr>
                     
                     <%  
                      st1=con.createStatement();
                    try {  
                      rs1=st1.executeQuery("select count(*) as Total from final where questionid='"+ID+"'and Eventid='"+EVENTID+"'and Qserialno='"+QNO+"'and Answer='A'");
                          
                        while(rs1.next())                            
                      {
                            %>
                        	<tr>
                    <td colspan="5"><b>No. of A:</b>
                     [B]<%=rs.getString("Total")%>[/B]
		   </td>
                    </tr>
                    <%   
                       
                          }                    

                      }   finally
                      {
                          if (rs1!= null)
                          {
                             rs1.close();
                              rs1= null;
                          }    
                          if (st1 != null)
                          {
                              st1.close();
                              st1 = null;
                          }

                      }
                      %>
                       <%    
                      st2=con.createStatement();
                    try {  
                      rs2=st2.executeQuery("select count(*) as Total1 from final where questionid='"+ID+"' and Eventid='"+EVENTID+"'and Qserialno='"+QNO+"'and Answer='B'");
                          
                        while(rs2.next())                            
                      {
                            %>
                        	<tr>
                    <td colspan="5"><b>No. of B:</b>
                     <%=rs.getString("Total1")%>
		    </td>
                     </tr>
                    <%                         
                          }                    

                      }   finally
                      {
                          if (rs2!= null)
                          {
                              rs2.close();
                              rs2= null;
                          }    
                          if (st1 != null)
                          {
                              st1.close();
                              st1 = null;
                          }
                      }
                      %>
                      <%           
                      st3=con.createStatement();
                    try {  
                      rs3=st3.executeQuery("select count(*) as Total2 from final where questionid='"+ID+"' and Eventid='"+EVENTID+"'and Qserialno='"+QNO+"'and Answer='C'");
                          
                        while(rs3.next())                            
                      {
                             %>
                        	<tr>
                    <td colspan="5"><b>No. of C:</b>
                     <%=rs.getString("Total2")%>
		    </td>
                     </tr>
                    <%                         
                          }                     

                      }   finally
                      {
                          if (rs3!= null)
                          {
                              rs3.close();
                              rs3= null;
                          }    
                          if (st3 != null)
                          {
                              st3.close();
                              st3 = null;
                          }

                      }
                      %>
                      <%               
                      st4=con.createStatement();
                    try {  
                      rs4=st4.executeQuery("select count(*) as Total3 from final where questionid='"+ID+"' and Eventid='"+EVENTID+"'and Qserialno='"+QNO+"'and Answer='D'");
                         
                        while(rs4.next())                            
                      {
                         %>
                        	<tr>
                    <td colspan="5"><b>No. of D:</b>
                     <%=rs.getString("Total3")%>
		    </td>
                    </tr>
                    <%                       
                          }                    

                      }   finally
                      {
                          if (rs4!= null)
                          {
                              rs4.close();
                              rs4= null;
                          }    
                          if (st4 != null)
                          {
                              st4.close();
                              st4 = null;
                          }
                      }
                      %> 
                  <%                       
                           }                    
                       
                       }   finally
                       {
                           if (rs != null)
                           {
                               rs.close();
                               rs = null;
                           }    
                           if (st != null)
                           {
                               st.close();
                               st = null;
                           }

                       }
                      %>                 
                     
                     </body></html>

Thansk in advance
Regards
Haresh

Did you mean maybe rs1.getString("Total"), seeing as how that statement seems to be in the rs1.next() while loop.

Also, you definately should not be doing this stuff as scriptlets in a JSP. This is a maintenance nightmare.

Hi;
with updated code, I am getting the value of four count query as zero.

<%-- 
    Document   : evaluationeventtable
    Created on : Jul 24, 2008, 6:52:37 PM
    Author     : user1
--%>

<%@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>Table-Data</title>
    </head>
    <body>
    <%  int QNO;  
        String ID=request.getParameter("id"); 
        out.println(ID);
        String EVENTID=request.getParameter("event");
        out.println(EVENTID);
        Connection connection = null;
        Statement st = null;
        Statement st1 = null;
        Statement st2 = null;
        Statement st3 = null;
        Statement st4 = null;
        Statement st5 = null;
        ResultSet rs= null;
        ResultSet rs1= null;
        ResultSet rs2= null;
        ResultSet rs3= null;
        ResultSet rs4= null;
        ResultSet rs5= null;
        
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mynewdatabase","root", "root123");
          
                       st5=con.createStatement();
                       int count=0;
                     try {  
                         rs5=st5.executeQuery("select count(*) from Final where Eventid='"+EVENTID+"'");
                        
                        while(rs5.next())                            
                       {count=rs5.getInt(1);                                         
                        out.println(count);                                                                       
                         }                         
                         
                        } finally
                       {
                           if (rs5 != null)
                           {
                               rs5.close();
                               rs5 = null;
                           }
                           if (st5 != null)
                           {
                               st5.close();
                               st5 = null;
                           }

                       }                
                       st=con.createStatement();
                       
                try      {
               
                  
            rs = st.executeQuery("SELECT * FROM Questionbank where Questionid='"+ID+"'");
                           
              while ( rs.next() )
                     {                 
                  
                   %>     
                     <table border="0">    	
                      <tr>             
                          <th><b>Question:</b></th><th><b>OPTION-A</b></th><th><b>OPTION-B</b></th><th><b>OPTION-C</b></th><th><b>OPTION-D</b></th></tr>
                          <tr> <td>  <%=rs.getString("questionname")%></td>
		    
                     </tr>
                     
                     <% 
                      for( QNO=1;QNO<=count;QNO++);                     
                      st1=con.createStatement();
                    try {  
                      rs1=st1.executeQuery("select count(*) as Total from final where questionid='"+ID+"'and Eventid='"+EVENTID+"'and Qserialno='"+QNO+"'and Answer='A'");
                          
                        while(rs1.next())                            
                      {
                            %>
                        	
                    <td><b>
                    <%=rs1.getString("Total")%></b> 
		   </td>
                    
                    <%   
                       
                          }                    

                      }   finally
                      {
                          if (rs1!= null)
                          {
                             rs1.close();
                              rs1= null;
                          }    
                          if (st1 != null)
                          {
                              st1.close();
                              st1 = null;
                          }

                      }
                      %>
                       <%    
                      st2=con.createStatement();
                    try {  
                      rs2=st2.executeQuery("select count(*) as Total1 from final where questionid='"+ID+"' and Eventid='"+EVENTID+"'and Qserialno='"+QNO+"'and Answer='B'");
                          
                        while(rs2.next())                            
                      {
                            %>
                        	
                    <td><b>
                     <%=rs2.getString("Total1")%></b>
		    </td>
                     
                    <%                         
                          }                    

                      }   finally
                      {
                          if (rs2!= null)
                          {
                              rs2.close();
                              rs2= null;
                          }    
                          if (st1 != null)
                          {
                              st1.close();
                              st1 = null;
                          }
                      }
                      %>
                      <%           
                      st3=con.createStatement();
                    try {  
                      rs3=st3.executeQuery("select count(*) as Total2 from final where questionid='"+ID+"' and Eventid='"+EVENTID+"'and Qserialno='"+QNO+"'and Answer='C'");
                          
                        while(rs3.next())                            
                      {
                             %>
                        	
                    <td><b>
                     <%=rs3.getString("Total2")%></b>
		    </td>
                     
                    <%                         
                          }                     

                      }   finally
                      {
                          if (rs3!= null)
                          {
                              rs3.close();
                              rs3= null;
                          }    
                          if (st3 != null)
                          {
                              st3.close();
                              st3 = null;
                          }

                      }
                      %>
                      <%               
                      st4=con.createStatement();
                    try {  
                      rs4=st4.executeQuery("select count(*) as Total3 from final where questionid='"+ID+"' and Eventid='"+EVENTID+"'and Qserialno='"+QNO+"'and Answer='D'");
                         
                        while(rs4.next())                            
                      {
                         %>
                        	
                    <td><b>
                     <%=rs4.getString("Total3")%></b></td> </tr></table>	    
                   
                    <%                       
                          }                    

                      }   finally
                      {
                          if (rs4!= null)
                          {
                              rs4.close();
                              rs4= null;
                          }    
                          if (st4 != null)
                          {
                              st4.close();
                              st4 = null;
                          }
                      }
                      %> 
                  <%                       
                           }                    
                       
                       }   finally
                       {
                           if (rs != null)
                           {
                               rs.close();
                               rs = null;
                           }    
                           if (st != null)
                           {
                               st.close();
                               st = null;
                           }

                       }
                      %> 
                                     
                     
                     </body></html>

and here is an output:

6 1 22 Question: OPTION-A OPTION-B OPTION-C OPTION-D 
How difficult did you find the course? 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
How much outside work per week (excluding time spent in attending lectures) did the course entail? 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
How easily were the books and articles recommended in the class available in the library? 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
How useful did you find the suggested readings? 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
Do you think a sufficient number of quizzes/exams were given? 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
How helpful were the quizzes/exams? 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
How fair were the exams (that counted towards your grade) testing your knowledge of the course material? 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
Was classroom participation encouraged? 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
Contents of the course 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
How far was your understanding of the subject increased after taking this course? 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
Impacts of the course 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
Does it prepare you for research in that field?  
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
Should the same course be given to others (Assume you are again in the same batch). 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
Knowledge of the instructor related to the course 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
Preparation of the instructor 
0  0  0  0 
Question: OPTION-A OPTION-B OPTION-C OPTION-D 
Preparation of the instructor 
0  0  0  0

Any suggestion is highly appreciated.
Thanks and Regards
Haresh

Also, you definately should not be doing this stuff as scriptlets in a JSP. This is a maintenance nightmare.

No point to repeat that, this guy completely ignore that for last two weeks from me. Another hopeless case to bang on...

No point to repeat that, this guy completely ignore that for last two weeks from me. Another hopeless case to bang on...

Yeah, I've pretty much scaled back on saying this (nobody listens, they all want the "easy way out", then cry when they need to fix or modify it), but, sometimes, I'll climb back onto that horse for a round or two. ;-)

That code just look awful ! ! !
So much nicer if data would be retrieved from DB in servlet, stored in the bean and with session passed to JSP where everything would be displayed with few lines of necessary scriplet

> So much nicer if data would be retrieved from DB in servlet,

Not to mention that actually *nothing* should go in servlet except the processing of request parameters / attributes and invocation of the actual business logic components. This helps in ensuring that you don't repeat yourself when the view changes. E.g. from a web based application to a swing based application.

No point to repeat that, this guy completely ignore that for last two weeks from me. Another hopeless case to bang on...

yep ..... you have one witness here, who saw it first hand !!! ;)

Solved.
Thanks and Regards
Haresh

Thanks and Regards
Haresh

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.