error while retroeving data from mqsql database

Thread Solved

•
•
Join Date: Jun 2008
Posts: 38
Reputation: guravharsha is an unknown quantity at this point 
Solved Threads: 0
guravharsha guravharsha is offline Offline
Light Poster

error while retroeving data from mqsql database

 
0
  #1
Jul 25th, 2008
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>
                     <%=rs.getString("Total")%>
		   </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
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2006
Posts: 2,467
Reputation: masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of 
Solved Threads: 267
Moderator
masijade's Avatar
masijade masijade is offline Offline
Nearly a Posting Maven

Re: error while retroeving data from mqsql database

 
0
  #2
Jul 25th, 2008
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.
Last edited by masijade; Jul 25th, 2008 at 6:58 am.
Java Programmer and Sun Systems Administrator

----------------------------------------------

Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.
--Brian Kernighan
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 38
Reputation: guravharsha is an unknown quantity at this point 
Solved Threads: 0
guravharsha guravharsha is offline Offline
Light Poster

Re: error while retroeving data from mqsql database

 
0
  #3
Jul 25th, 2008
Hi;
with updated code, I am getting the value of four count query as zero.
JSP Syntax (Toggle Plain Text) 
  1.  <%-- 
  2.     Document   : evaluationeventtable
  3.     Created on : Jul 24, 2008, 6:52:37 PM
  4.     Author     : user1
  5. --%>
  6.  
  7. <%@page contentType="text/html" pageEncoding="UTF-8"%>
  8. <%@ page language ="java" %>
  9. <%@ page import="java.sql.*, javax.sql.*, javax.naming.*,java.io.*,java.util.*" %>
  10. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
  11.    "http://www.w3.org/TR/html4/loose.dtd">
  12.  
  13. <html>
  14.     <head>
  15.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  16.         <title>Table-Data</title>
  17.     </head>
  18.     <body>
  19.     <%  int QNO;  
  20.         String ID=request.getParameter("id"); 
  21.         out.println(ID);
  22.         String EVENTID=request.getParameter("event");
  23.         out.println(EVENTID);
  24.         Connection connection = null;
  25.         Statement st = null;
  26.         Statement st1 = null;
  27.         Statement st2 = null;
  28.         Statement st3 = null;
  29.         Statement st4 = null;
  30.         Statement st5 = null;
  31.         ResultSet rs= null;
  32.         ResultSet rs1= null;
  33.         ResultSet rs2= null;
  34.         ResultSet rs3= null;
  35.         ResultSet rs4= null;
  36.         ResultSet rs5= null;
  37.  
  38.         Class.forName("com.mysql.jdbc.Driver");
  39.         Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mynewdatabase","root", "root123");
  40.  
  41.                        st5=con.createStatement();
  42.                        int count=0;
  43.                      try {  
  44.                          rs5=st5.executeQuery("select count(*) from Final where Eventid='"+EVENTID+"'");
  45.  
  46.                         while(rs5.next())                            
  47.                        {count=rs5.getInt(1);                                         
  48.                         out.println(count);                                                                       
  49.                          }                         
  50.  
  51.                         } finally
  52.                        {
  53.                            if (rs5 != null)
  54.                            {
  55.                                rs5.close();
  56.                                rs5 = null;
  57.                            }
  58.                            if (st5 != null)
  59.                            {
  60.                                st5.close();
  61.                                st5 = null;
  62.                            }
  63.  
  64.                        }                
  65.                        st=con.createStatement();
  66.  
  67.                 try      {
  68.  
  69.  
  70.             rs = st.executeQuery("SELECT * FROM Questionbank where Questionid='"+ID+"'");
  71.  
  72.               while ( rs.next() )
  73.                      {                 
  74.  
  75.                    %>     
  76.                      <table border="0">    	
  77.                       <tr>             
  78.                           <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>
  79.                           <tr> <td>  <%=rs.getString("questionname")%></td>
  80.  
  81.                      </tr>
  82.  
  83.                      <% 
  84.                       for( QNO=1;QNO<=count;QNO++);                     
  85.                       st1=con.createStatement();
  86.                     try {  
  87.                       rs1=st1.executeQuery("select count(*) as Total from final where questionid='"+ID+"'and Eventid='"+EVENTID+"'and Qserialno='"+QNO+"'and Answer='A'");
  88.  
  89.                         while(rs1.next())                            
  90.                       {
  91.                             %>
  92.  
  93.                     <td><b>
  94.                     <%=rs1.getString("Total")%></b> 
  95. 		   </td>
  96.  
  97.                     <%   
  98.  
  99.                           }                    
  100.  
  101.                       }   finally
  102.                       {
  103.                           if (rs1!= null)
  104.                           {
  105.                              rs1.close();
  106.                               rs1= null;
  107.                           }    
  108.                           if (st1 != null)
  109.                           {
  110.                               st1.close();
  111.                               st1 = null;
  112.                           }
  113.  
  114.                       }
  115.                       %>
  116.                        <%    
  117.                       st2=con.createStatement();
  118.                     try {  
  119.                       rs2=st2.executeQuery("select count(*) as Total1 from final where questionid='"+ID+"' and Eventid='"+EVENTID+"'and Qserialno='"+QNO+"'and Answer='B'");
  120.  
  121.                         while(rs2.next())                            
  122.                       {
  123.                             %>
  124.  
  125.                     <td><b>
  126.                      <%=rs2.getString("Total1")%></b>
  127. 		    </td>
  128.  
  129.                     <%                         
  130.                           }                    
  131.  
  132.                       }   finally
  133.                       {
  134.                           if (rs2!= null)
  135.                           {
  136.                               rs2.close();
  137.                               rs2= null;
  138.                           }    
  139.                           if (st1 != null)
  140.                           {
  141.                               st1.close();
  142.                               st1 = null;
  143.                           }
  144.                       }
  145.                       %>
  146.                       <%           
  147.                       st3=con.createStatement();
  148.                     try {  
  149.                       rs3=st3.executeQuery("select count(*) as Total2 from final where questionid='"+ID+"' and Eventid='"+EVENTID+"'and Qserialno='"+QNO+"'and Answer='C'");
  150.  
  151.                         while(rs3.next())                            
  152.                       {
  153.                              %>
  154.  
  155.                     <td><b>
  156.                      <%=rs3.getString("Total2")%></b>
  157. 		    </td>
  158.  
  159.                     <%                         
  160.                           }                     
  161.  
  162.                       }   finally
  163.                       {
  164.                           if (rs3!= null)
  165.                           {
  166.                               rs3.close();
  167.                               rs3= null;
  168.                           }    
  169.                           if (st3 != null)
  170.                           {
  171.                               st3.close();
  172.                               st3 = null;
  173.                           }
  174.  
  175.                       }
  176.                       %>
  177.                       <%               
  178.                       st4=con.createStatement();
  179.                     try {  
  180.                       rs4=st4.executeQuery("select count(*) as Total3 from final where questionid='"+ID+"' and Eventid='"+EVENTID+"'and Qserialno='"+QNO+"'and Answer='D'");
  181.  
  182.                         while(rs4.next())                            
  183.                       {
  184.                          %>
  185.  
  186.                     <td><b>
  187.                      <%=rs4.getString("Total3")%></b></td> </tr></table>	    
  188.  
  189.                     <%                       
  190.                           }                    
  191.  
  192.                       }   finally
  193.                       {
  194.                           if (rs4!= null)
  195.                           {
  196.                               rs4.close();
  197.                               rs4= null;
  198.                           }    
  199.                           if (st4 != null)
  200.                           {
  201.                               st4.close();
  202.                               st4 = null;
  203.                           }
  204.                       }
  205.                       %> 
  206.                   <%                       
  207.                            }                    
  208.  
  209.                        }   finally
  210.                        {
  211.                            if (rs != null)
  212.                            {
  213.                                rs.close();
  214.                                rs = null;
  215.                            }    
  216.                            if (st != null)
  217.                            {
  218.                                st.close();
  219.                                st = null;
  220.                            }
  221.  
  222.                        }
  223.                       %> 
  224.  
  225.  
  226.                      </body></html>

and here is an output:
JSP Syntax (Toggle Plain Text) 
  1. 6 1 22 Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  2. How difficult did you find the course? 
  3. 0  0  0  0 
  4. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  5. How much outside work per week (excluding time spent in attending lectures) did the course entail? 
  6. 0  0  0  0 
  7. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  8. How easily were the books and articles recommended in the class available in the library? 
  9. 0  0  0  0 
  10. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  11. How useful did you find the suggested readings? 
  12. 0  0  0  0 
  13. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  14. Do you think a sufficient number of quizzes/exams were given? 
  15. 0  0  0  0 
  16. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  17. How helpful were the quizzes/exams? 
  18. 0  0  0  0 
  19. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  20. How fair were the exams (that counted towards your grade) testing your knowledge of the course material? 
  21. 0  0  0  0 
  22. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  23. Was classroom participation encouraged? 
  24. 0  0  0  0 
  25. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  26. Contents of the course 
  27. 0  0  0  0 
  28. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  29. How far was your understanding of the subject increased after taking this course? 
  30. 0  0  0  0 
  31. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  32. Impacts of the course 
  33. 0  0  0  0 
  34. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  35. Does it prepare you for research in that field?  
  36. 0  0  0  0 
  37. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  38. Should the same course be given to others (Assume you are again in the same batch). 
  39. 0  0  0  0 
  40. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  41. Knowledge of the instructor related to the course 
  42. 0  0  0  0 
  43. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  44. Preparation of the instructor 
  45. 0  0  0  0 
  46. Question: OPTION-A OPTION-B OPTION-C OPTION-D 
  47. Preparation of the instructor 
  48. 0  0  0  0
Any suggestion is highly appreciated.
Thanks and Regards
Haresh
Last edited by peter_budo; Jul 25th, 2008 at 9:04 am. Reason: replacing [tex] tag with [code] for readibilty reasons
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2004
Posts: 4,275
Reputation: peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of 
Solved Threads: 494
Moderator
Featured Poster
peter_budo's Avatar
peter_budo peter_budo is offline Offline
Code tags enforcer

Re: error while retroeving data from mqsql database

 
-1
  #4
Jul 25th, 2008
•
•
•
•
Originally Posted by masijade View Post
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...
Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, Graduate & Undergraduate Software Development Community, JAVAWUG (Java Web User Group), The London Android Group
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2006
Posts: 2,467
Reputation: masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of masijade has much to be proud of 
Solved Threads: 267
Moderator
masijade's Avatar
masijade masijade is offline Offline
Nearly a Posting Maven

Re: error while retroeving data from mqsql database

 
0
  #5
Jul 25th, 2008
•
•
•
•
Originally Posted by peter_budo View Post
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. ;-)
Java Programmer and Sun Systems Administrator

----------------------------------------------

Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.
--Brian Kernighan
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2004
Posts: 4,275
Reputation: peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of 
Solved Threads: 494
Moderator
Featured Poster
peter_budo's Avatar
peter_budo peter_budo is offline Offline
Code tags enforcer

Re: error while retroeving data from mqsql database

 
-1
  #6
Jul 25th, 2008
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
Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, Graduate & Undergraduate Software Development Community, JAVAWUG (Java Web User Group), The London Android Group
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2006
Posts: 7,652
Reputation: ~s.o.s~ has much to be proud of ~s.o.s~ has much to be proud of ~s.o.s~ has much to be proud of ~s.o.s~ has much to be proud of ~s.o.s~ has much to be proud of ~s.o.s~ has much to be proud of ~s.o.s~ has much to be proud of ~s.o.s~ has much to be proud of ~s.o.s~ has much to be proud of 
Solved Threads: 474
Super Moderator
Featured Poster
~s.o.s~'s Avatar
~s.o.s~ ~s.o.s~ is offline Offline
Failure as a human

Re: error while retroeving data from mqsql database

 
0
  #7
Jul 26th, 2008
> 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.
I don't accept change; I don't deserve to live.

Jo Tujhe Jagaaye, Nindein Teri Udaaye Khwaab Hai Sachcha Wahi.
Nindon Mein Jo Aaye Jise To Bhul Jaaye Khawab Woh Sachcha Nahi.
Khwaab Ko Raag De, Nind Ko Aag De
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2007
Posts: 1,175
Reputation: stephen84s is a glorious beacon of light stephen84s is a glorious beacon of light stephen84s is a glorious beacon of light stephen84s is a glorious beacon of light stephen84s is a glorious beacon of light stephen84s is a glorious beacon of light 
Solved Threads: 125
Featured Poster
stephen84s's Avatar
stephen84s stephen84s is offline Offline
Veteran Poster

Re: error while retroeving data from mqsql database

 
0
  #8
Jul 26th, 2008
•
•
•
•
Originally Posted by peter_budo View Post
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 !!!
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand."

"How to ask questions the smart way ?"
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 38
Reputation: guravharsha is an unknown quantity at this point 
Solved Threads: 0
guravharsha guravharsha is offline Offline
Light Poster

Re: error while retroeving data from mqsql database

 
0
  #9
Jul 28th, 2008
Solved.
Thanks and Regards
Haresh
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 38
Reputation: guravharsha is an unknown quantity at this point 
Solved Threads: 0
guravharsha guravharsha is offline Offline
Light Poster

solved

 
0
  #10
Jul 28th, 2008
Thanks and Regards
Haresh
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the JSP Forum


Views: 1017 | Replies: 9
Thread Tools Search this Thread



Tag cloud for JSP
apache array backbutton combobox comma connection csv database development directorystructure dropdownlist dynamicpagetitles eclipse frames glassfish ie8 imagetodatabse imageupload integer internet java javaee javascript jsf jsp jsppagetitles levels mvc2 mvcmodel2 mysql netbeans network parameters passing ping printinserverinsteadofclient project read redirect request.getparameter response seperated servlet servletdopost()readxml sessions software sql ssl state_saving_method stocks sun tomcat tutorial update values video web write
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC