Forum:

How to store option format value in database in jsp

Ranch Hand

Posts: 151

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi,
I have one jsp page containing 2o questions retrieved from table along with its possible four-option answer in this manner:
Question1:
Option-a
Option-b
Option-c
Option-d
All the options are in radio button form. I am trying to store the result of these question test in my final output table:
Structure of this table is:

Eventide -> int(2)->data from another event table->getting value in update table
Question id ->int(2) ->data from questionbank table-> getting value in update table

Serialno ->int(3) ->no. from questionbank table
Answer -> varchar(2) -> value of the option selected from below question page>
So structure of final table will be:
Event-id question-id serial-no answer
Same Same 1 a/b/c/d
For all For all 2 a/b/c/d
Question question 3 a/b/c/d

So, How it is possible to store the result of all question with answer equals to one of the four options in the same row of questioned.
Is there required in looping in my insertdata.jsp page.
Here questioned is same for all questions:
Here is fillquestion.jsp page:

<%-- 
    Document   : fillquestion
    Created on : Jul 18, 2008, 10:07:43 AM
    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>Fill-up-Question</title>
    </head>
    <body>
    <form name="fill-ok" action="fill-question-ok.jsp" method="POST">
        
        <table width="50%" border="1" borderColor="#000066" cellspacing="0" cellpadding="5" align="center">
              <TBODY>
<tr>
<td colspan="8" align="middle" class="StripColor">
<font class="FontGeneralBoldWhite">Fill Up the Question</font>
                        
</td>
</tr> 
        
        <%
        String ID=request.getParameter("id"); 
        String EVENTID=request.getParameter("even");
        Connection connection = null;
        Statement st = null;
        Statement st1 = null;
        ResultSet rs = null;
        ResultSet rs1 = 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 questionbank where questionid='"+ID+"'");
        
         while ( rs.next() )
   {
            %>
            
                      
 
    <tr>
             <input type="hidden" name="Event" value="<%=request.getParameter("event")%>"/>
             <input type="hidden" name="ID" value="<%=request.getParameter("id")%>"/>
             <input type="hidden" name="QseriNO" value="<%=rs.getString("qserialno")%>"/>

<td><%=rs.getString("qserialno")%>:  <b>Question:</b>
               <%=rs.getString("questionname")%>
</td>
                </tr>
                <tr>
                 //<td><input type="hidden" name="QserialNO" value="<%=request.getParameter("Qserialno")%>"></td>
                <td><b>A:</b><input type="radio" name="answer<%=rs.getString("qserialno")%>" value="A" /><%=rs.getString("OptionA")%></td>           
                <td><B>B:</B><input type="radio" name="answer<%=rs.getString("qserialno")%>" value="B" /><%=rs.getString("OptionB")%></td>
                <td><B>C:</B><input type="radio" name="answer<%=rs.getString("qserialno")%>" value="C" /><%=rs.getString("OptionC")%></td> 
                <td><B>D:</B><input type="radio" name="answer<%=rs.getString("qserialno")%>" value="D" /><%=rs.getString("OptionD")%></td>                 
                </tr>             
        <%
                           }
 
                       }   finally
                       {
                           if (rs != null)
                           {
                               rs.close();
                               rs = null;
                           }
                           if (st != null)
                           {
                               st.close();
                               st = null;
                           }
 
                       }
                       %>
                       <%
                       st1=con.createStatement();
                     try {  
                         rs1=st1.executeQuery("select count(*) from questionbank where questionid='"+ID+"'");
                        int count=0;
                        while(rs1.next())
                            
                       {
                         count=rs1.getInt(1);
                         String myString = String.valueOf(count); %>                         
                         <input type="hidden" name="countvalue" value="<%=myString %>">
                         <%                                              
                         }
                         out.println(count);
                         
                        } finally
                       {
                           if (rs1 != null)
                           {
                               rs1.close();
                               rs1 = null;
                           }
                           if (st1 != null)
                           {
                               st1.close();
                               st1 = null;
                           }
 
                       }
                      
                     %>
                   
 
 
 
    </table>
    
    <tr>
                <TD>                                                                <input type="submit" value="SUBMIT" name="submit" /></TD>
                <td><input type="reset" value="RESET" name="reset" /></td>
                    
       </tr>
               
   </form>
   </body>
   </html>

And here is an my insertdata.jsp page
I am not sure about the code written in this page, kindly provide update

<%-- 
    Document   : fill-question-ok
    Created on : Jul 21, 2008, 3:41:58 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>Fill-question-ok</title>
    </head>
    <body>
    <form name="fill-question-ok1" action="outputtable.jsp" method="POST">
    
     <%   
      String EVENTID=request.getParameter("Event");
      out.println("eventid="+EVENTID);
      String QID=request.getParameter("ID")  ;
      out.println("Qid="+QID);
      String QSERIALNO=request.getParameter("QseriNO")  ;
      out.println("Qno="+QSERIALNO);
      String ANSWER=request.getParameter("answer");
      out.println("answer="+ANSWER);
      int COUNT= Integer.parseInt(request.getParameter("countvalue"));
      out.println("Count="+COUNT);
      //String OPC=request.getParameter("opc");
      //String OPD=request.getParameter("opd");
      String connectionURL = "jdbc:mysql://localhost:3306/mynewdatabase";
   
        Connection connection = null;   
        PreparedStatement pstatement = null;
   
         Class.forName("com.mysql.jdbc.Driver").newInstance();
           int updateQuery = 0;
     
     
            try {            
              
              connection = DriverManager.getConnection
              (connectionURL, "root", "root123");
              
             
              for(int Qno=1;Qno<=COUNT;Qno++)
             {                  
              String queryString = "INSERT INTO final(Userid,Eventid,Questionid,Qserialno,Answer) VALUES (?,?,?,?,?)";
      pstatement = connection.prepareStatement(queryString);
              pstatement.setString(1,"1");
              pstatement.setString(2,EVENTID);
              pstatement.setString(3,QID);
              pstatement.setString(4,QSERIALNO);
              pstatement.setString(5,ANSWER);

updateQuery = pstatement.executeUpdate();
              out.println("Q.="+updateQuery);
              }
              if (updateQuery != 0) { %>
           <br>
           
      <tr><th>Data is inserted successfully 
                    in database.</th></tr>
   
              <%
             
            }
              }
            
            catch (Exception ex) {
            out.println("Exception ERROR:"+ex.getMessage());    
            out.println("Unable to connect to batabase.");
   
            }
            finally {
                pstatement.close();
                connection.close();
           }

%>
 </form>
 </body> 
</html>

Thanks and Regards
Harshal

Thanks in advance

Amit Ghorpade

Bartender

Posts: 2856

I like...

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Please do not post the same question multiple times.

SCJP, SCWCD.
|Asking Good Questions|

Slideshow boring ... losing consciousness ... just gonna take a quick nap on this tiny ad ...

Gift giving made easy with the permaculture playing cards

https://coderanch.com/t/777758/Gift-giving-easy-permaculture-playing