UPDATING A TABLE USING AN SQL UPDATE STATEMENT

Hi there i have two pages a changq.jsp page where the Adminisrator can change questions and a changedq.jsp page where the sql statement is that updates the changes made in the database.
The database table is called questions and contains two fields Q_No and Q_Test.
Here is my code for the changeq.jsp page THIS CODE WORKS:

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


-------------connection stuff-------------------------

Statement  = connection.createStatement();
ResultSet rs22 = Statement.executeQuery("SELECT * FROM Questions   ");

%>



<table width="63%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td valign=bottom class="subtitle">&nbsp;</td>
    <td valign=bottom class="subtitle"><h2><strong>Change Question Bank</strong></h2>
   </td>
   
    </td>
  </tr>
  <form name="frm1" id="frm1" method="post" action="changedq.jsp" onSubmit="return validateForm(frm1)">

    <tr align="left" valign="top">
      <td valign="center" height="35" colspan="4">
        <p>
        <table width="100%" border="0" cellspacing="0" cellpadding="3>


          <tr>
           
          <td align=right bgcolor="#ffffff" valign="bottom" width="31%" class="subtitle">
                                  <div align="left"><b> Logged in as:</b></div></td>
                                <td align=left valign=bottom width="45%" >
<b>
<% out.print(  session.getValue("UserName")+ "<br>");%>
</b>
                                </td>
          </tr>

<%
    int i = 1 ;
    while (rs22.next ())
    {
        String name = "QuestionNo" + i ;
          i++;
     
       
      %>

          <tr valign="bottom">
            <td> <div align="left"><b><%= name %> </b></div></td>
            <td align="left" valign="bottom" colspan=2>
              <input name = "<%= name %>"size="40" maxlength="100" tabindex="1" value="<%=rs22.getString ("Q_Test")%>">
            </td>


         
<%}%>

                        </table>
          <tr>
      <td colspan="2" align="right" valign="center"></td>
      <td width="21%" align="right" valign="center"></td>
      <td width="8%" align="left" valign="center"> </td>
    </tr>
    <tr>
      <td width="24%"></td>
      <td width="47%">
<tr><td></td>
      <td> <input type="submit" value="Change Questions"> </td>
      </tr>  
        </table>
</form>
        </table>


<%
            
{
connection.close();
}

%>

----------------------------------------------------------
Here is the code for the changedq.jsp this paqge isnt working and its to do with the SQL statement i think:

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

statement = connection.createStatement();


 String  Q1 = "'" + request.getParameter("QuestionNo1") + "'";
 String Q2  = "'" + request.getParameter("QuestionNo2") + "'" ;
 String Q3 = "'" + request.getParameter("QuestionNo3") + "'";
 String Q4 = "'" + request.getParameter("QuestionNo4") + "'";
 String Q5 = "'" + request.getParameter("QuestionNo5") + "'";
 String Q6 = "'" + request.getParameter("QuestionNo6") + "'";
 String Q7 = "'" + request.getParameter("QuestionNo7") + "'";
 String Q8 = "'" + request.getParameter("QuestionNo8") + "'";
 String Q9  = "'" + request.getParameter("QuestionNo9") + "'";
 String Q10 = "'" + request.getParameter("QuestionNo10") + "'";
 String Q11 = "'" + request.getParameter("QuestionNo11") + "'";
 String Q12  = "'" + request.getParameter("QuestionNo12") + "'";
 String Q13  = "'" + request.getParameter("QuestionNo13") + "'";
 String Q14 = "'" + request.getParameter("QuestionNo14") + "'";
 String Q15 = "'" + request.getParameter("QuestionNo15") + "'";
 String Q16 = "'" + request.getParameter("QuestionNo16") + "'";
 String Q17  = "'" + request.getParameter("QuestionNo17") + "'";
 String Q18  = "'" + request.getParameter("QuestionNo18") + "'";
 String Q19 = "'" + request.getParameter("QuestionNo19") + "'";
 String Q20  = "'" + request.getParameter("QuestionNo20") + "'";

statement = connection.createStatement();

String query = ("Update Questions set Q_Test = " + Q1 + ", Q_Test = " + Q2 + ",Q_Test = " + Q13 + ", Q_Test = " + Q4 + ", Q_Test = " + Q5 + ", Q_Test = " + Q6+ ", Q_Test = " + Q7+ ",  Q_Test =  " + Q8 +", Q_Test = " + Q9 +", Q_Test  = " + Q10+", Q_Test  = " + Q11+",  Q_Test  = " + Q12 +", Q_Test  = " + Q13+", Q_Test  = " + Q14 +", Q_Test  = " + Q15 +", Q_Test  =  " + Q16 +", Q_Test = " +Q17+", Q_Test  = " + Q18 + ", Q_Test =" + Q19+", Q_Test  = " + Q20 +"  ");

statement.execute( query);




%>


Thank you  <a href="adminoptions.jsp">Return</a>

<%
{
connection.close();
}
%>
-----------------------------------------------

I think there should be a WHERE statement at the end of the code but i dont no what it should contain?


acslaterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimYatesCommented:
Ok...try this:

<%
    String sql = "UPDATE questions SET Q_Test = ? WHERE Q_No = ?" ;
    PreparedStatement stmt = conn.prepareStatement( sql ) ;
    int i = 1 ;
    String param = request.getParameter( "QuestionNo" + i ) ;
    while( param != null )
    {
        stmt.setString( 1, param ) ;
        stmt.setString( 2, "QuestionNo" + i ) ;
        stmt.executeUpdate() ;
        i++ ;
    }
    stmt.close() ;
%>

That should loop through all the poarameters, and set them one by one...
0
TimYatesCommented:
Eeek...  almost forgot:

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

<%
    String sql = "UPDATE questions SET Q_Test = ? WHERE Q_No = ?" ;
    PreparedStatement stmt = connection.prepareStatement( sql ) ;
    int i = 1 ;
    String param = request.getParameter( "QuestionNo" + i ) ;
    while( param != null )
    {
        stmt.setString( 1, param ) ;
        stmt.setString( 2, "QuestionNo" + i ) ;
        stmt.executeUpdate() ;
        i++ ;
        param = request.getParameter( "QuestionNo" + i ) ;
    }
    stmt.close() ;
%>
0
nagkiCommented:
Hi acslater,

If that QuestionNo's are of integer type and their datatype in database is of type integer...u should do like this
int Q1=Integer.parseInt(request.getParameter("Question1"));

Cheers!
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

bloodredsunCommented:
You dataabse design needs to change. You should have a unique identifier or a foreign key for each question. At the moment you are updating the same variable many times. You also have no '  ' sinlge quote marks around your question Strings

Here's a possible example

>>Update Questions set Q_Test ='bert', Q_Test = 'ernie'

You have just set the column Q_Test to one value and then over written it to another. If you had a unique identifier for each one you could do the following

Update Questions set Q_Test ='bert' where Q_Number = '1'
Update Questions set Q_Test ='ernie' where Q_Number = '2'
etc

This will sequencially update your questions.

This method would also be improved by using a Prepared Statement and batches of parameters.
0
TimYatesCommented:
>> You also have no '  ' sinlge quote marks around your question Strings

He put those in at the request.getParameter() stage :-)
0
acslaterAuthor Commented:
Q_No is the unique identifier so if i had

Update Questions set Q_Test =  " + Q1 + " where Q_Number = 1;

Would that work?
0
TimYatesCommented:
did you try my code?
0
TimYatesCommented:
As your "editing" page is now dynamic, it would be best if your "update" page were dynamic too...
0
bloodredsunCommented:
ha, misread the question and missed the fact that he has a Q_No value.

What a wally...
0
acslaterAuthor Commented:
Yea but should it be exactly like this line what are the ??

 String sql = "UPDATE questions SET Q_Test = ? WHERE Q_No = ?" ;
0
TimYatesCommented:
>>  Yea but should it be exactly like this line what are the ??

Yes.  Exactly like that...

I use a "PreparedStatement", the "?" chars get set by the two calls:

        stmt.setString( 1, param ) ;
        stmt.setString( 2, "QuestionNo" + i ) ;
 
The first call sets parameter 1 (the first ?) and the second sets the 2nd one :-)

Then you call executeUpdate, and the update is fired to the database...
0
bloodredsunCommented:
That's using a PreparedStatment. They are placeholders.

Here's a version using batching that would be even more efficent.

PreparedStatement stmt = conn.prepareStatement("Update Questions set Q_Test = ? where Q-No = ? ")
while( param != null )
      stmt.setString( 1, "");//assuming both are Strings
      stmt.setString( 2, "");//assuming both are Strings
      stmt.addBatch();
}
stmt.executeUpdate();//this is done only once so very fast
0
TimYatesCommented:
>>  Here's a version using batching that would be even more efficent.

Hehehe...true...  But with only 20 updates, I doubt you're gonna notice the speedup ;-)

But that is a good point...
0
TimYatesCommented:
>> stmt.executeUpdate();//this is done only once so very fast


Shouldn't that be:

    stmt.executeBatch();//this is done only once so very fast

?
0
acslaterAuthor Commented:
Ok something happened to the questions in my database so im going to retype them then try your code il get back to ye and let ye no
0
TimYatesCommented:
*fingers crossed*
0
bloodredsunCommented:
>>stmt.executeBatch();//this is done only once so very fast
Yes...curse these fat fingers and their typing

Two errors in one thread...I feel myself slinking off into the corner to wear my dunce hat :-)
0
TimYatesCommented:
Hehehe it's Friday, and you were probably up all night playing on your new PSP!

;-D
0
acslaterAuthor Commented:
hello this is my code and im getting a blank page no errors just a blank page and its not updating the database.

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

-----------------connection stuff--------------------------
      
statement = connection.createStatement();


 String  Q1 = "'" + request.getParameter("QuestionNo1") + "'";
 String Q2  = "'" + request.getParameter("QuestionNo2") + "'" ;
 String Q3 = "'" + request.getParameter("QuestionNo3") + "'";
 String Q4 = "'" + request.getParameter("QuestionNo4") + "'";
 String Q5 = "'" + request.getParameter("QuestionNo5") + "'";
 String Q6 = "'" + request.getParameter("QuestionNo6") + "'";
 String Q7 = "'" + request.getParameter("QuestionNo7") + "'";
 String Q8 = "'" + request.getParameter("QuestionNo8") + "'";
 String Q9  = "'" + request.getParameter("QuestionNo9") + "'";
 String Q10 = "'" + request.getParameter("QuestionNo10") + "'";
 String Q11 = "'" + request.getParameter("QuestionNo11") + "'";
 String Q12  = "'" + request.getParameter("QuestionNo12") + "'";
 String Q13  = "'" + request.getParameter("QuestionNo13") + "'";
 String Q14 = "'" + request.getParameter("QuestionNo14") + "'";
 String Q15 = "'" + request.getParameter("QuestionNo15") + "'";
 String Q16 = "'" + request.getParameter("QuestionNo16") + "'";
 String Q17  = "'" + request.getParameter("QuestionNo17") + "'";
 String Q18  = "'" + request.getParameter("QuestionNo18") + "'";
 String Q19 = "'" + request.getParameter("QuestionNo19") + "'";
 String Q20  = "'" + request.getParameter("QuestionNo20") + "'";



    String sql = "UPDATE questions SET Q_Test = ? WHERE Q_No = ?" ;
    PreparedStatement stmt = connection.prepareStatement( sql ) ;
    int i = 1 ;
    String param = request.getParameter( "QuestionNo" + i ) ;
    while( param != null )
    {
        stmt.setString( 1, param ) ;
        stmt.setString( 2, "QuestionNo" + i ) ;
        stmt.executeUpdate() ;
        i++ ;
        param = request.getParameter( "QuestionNo" + i ) ;
    }
    stmt.close() ;




%>
Thank you<a href ="adminoptions.jsp">Return</a>
<%
{
connection.close();
}
%>


0
TimYatesCommented:
What is "Q_No"...  should it be an int?

You also don't need the

    statement = connection.createStatement();

or any of the

    String  Q1 = "'" + request.getParameter("QuestionNo1") + "'";
    ...snip...
    String Q20  = "'" + request.getParameter("QuestionNo20") + "'";

lines any more...
0
bloodredsunCommented:
All I can say is "Get one"...

I've just about got used to envious stares on the Tube when I commmute but the best bit is when someone says "I hear the screen is rubbish..blah blah" and you show them that it is in fact, the sexiest bit of kit this side of the Ann Summer's catalogue.

Downside is, I have blisters on my playing thumbs ;-)
0
acslaterAuthor Commented:
yea question number is an int ok so i dont need any of that stuff

String  Q1 = "'" + request.getParameter("QuestionNo1") + "'";
    ...snip...
    String Q20  = "'" + request.getParameter("QuestionNo20") + "'";
0
TimYatesCommented:
Nope...  try:

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

-----------------connection stuff--------------------------
     
    String sql = "UPDATE questions SET Q_Test = ? WHERE Q_No = ?" ;
    PreparedStatement stmt = connection.prepareStatement( sql ) ;
    int i = 1 ;
    String param = request.getParameter( "QuestionNo" + i ) ;
    while( param != null )
    {
        stmt.setString( 1, param ) ;
        stmt.setInt( 2, i ) ;
        stmt.executeUpdate() ;
        i++ ;
        param = request.getParameter( "QuestionNo" + i ) ;
    }
    stmt.close() ;
%>
Thank you<a href ="adminoptions.jsp">Return</a>
<%
{
connection.close();
}
%>
0
acslaterAuthor Commented:
i took out the other stuff and now just have:  

String sql = "UPDATE questions SET Q_Test = ? WHERE Q_No = ?" ;
    PreparedStatement stmt = connection.prepareStatement( sql ) ;
    int i = 1 ;
    String param = request.getParameter( "QuestionNo" + i ) ;
    while( param != null )
    {
        stmt.setString( 1, param ) ;
        stmt.setString( 2, "QuestionNo" + i ) ;
        stmt.executeUpdate() ;
        i++ ;
        param = request.getParameter( "QuestionNo" + i ) ;
    }
    stmt.close() ;



%>
Thank you<a href ="adminoptions.jsp">Return</a>
<%
{
connection.close();
}
%>
0
bloodredsunCommented:
then it should be

stmt.setInt( 2,  Integer.parseInt("QuestionNo" + i) ) ;
0
TimYatesCommented:
>> I've just about got used to envious stares on the Tube when I commmute but
>> the best bit is when someone says "I hear the screen is rubbish..blah blah"
>> and you show them that it is in fact, the sexiest bit of kit this side of the Ann
>> Summer's catalogue.

Hehe, between this and the DS, I'm going to have a poor summer ;-)

>> Downside is, I have blisters on my playing thumbs ;-)

hehehe!
0
bloodredsunCommented:
actually

stmt.setInt( 2,  i) ) ;
0
acslaterAuthor Commented:
just tried that and its still given a blank page any ideas??
0
TimYatesCommented:
       stmt.setString( 2, "QuestionNo" + i ) ;

should be:

        stmt.setInt( 2, i ) ;

assuming each question has a number starting at 1 :-)
0
TimYatesCommented:
IS there an exception in tomcat/logs/catalina.out
0
acslaterAuthor Commented:
the questions go from one to 20.

Yea i changed it to  stmt.setInt( 2, i ) ;

>>IS there an exception in tomcat/logs/catalina.out

what do you mean?
0
TimYatesCommented:
open catalina.out in a text editor, and scroll to the end...

It may have printed an exception out there...
0
acslaterAuthor Commented:
Not sure if i have control of tomcat im using a hosting site its called javaserver hosting.com
0
TimYatesCommented:
Ahhh...ok...

hmmmm...

can you post the entire jsp?
0
acslaterAuthor Commented:
<%


Statement      statement = null;
ResultSet      resultset = null;
Connection connection = null;

---------------------connection stuff-------------------------------

                 String sql = ("UPDATE questions SET Q_Test = ? WHERE Q_No = ?") ;
    PreparedStatement stmt = connection.prepareStatement( sql ) ;
    int i = 1 ;
    String param = request.getParameter( "QuestionNo" + i ) ;
    while( param != null )
    {
        stmt.setString( 1, param ) ;
        stmt.setInt( 2, i ) ;
        stmt.executeUpdate() ;
        i++ ;
        param = request.getParameter( "QuestionNo" + i ) ;
    }
    stmt.close() ;
%>
Thank you<a href ="adminoptions.jsp">Return</a>
<%

{
connection.close();
}

%>

this is all the code dont no why im getting a blank page and its not updating the database either is there another way of doing it?
0
TimYatesCommented:
Try this...  we will try to catch any SQL exceptions, and ENSURE your statmenets and connections are closed

What version of java are you running?  We can make the exception output more informative if you are on 1.4+

<%
    PreparedStatement stmt = null ;
    Connection connection = null;

    try
    {
        ---------------------connection stuff-------------------------------

        String sql = ("UPDATE questions SET Q_Test = ? WHERE Q_No = ?") ;
        stmt = connection.prepareStatement( sql ) ;
        int i = 1 ;
        String param = request.getParameter( "QuestionNo" + i ) ;
        while( param != null )
        {
            stmt.setString( 1, param ) ;
            stmt.setInt( 2, i ) ;
            stmt.executeUpdate() ;
            i++ ;
            param = request.getParameter( "QuestionNo" + i ) ;
        }
    }
    catch( SQLException ex )
    {
        out.println( "ERROR! :: " + ex.toString() ) ;
    }
    finally
    {
        try { if( stmt != null ) stmt.close() ; } catch( SQLException ex ) {}
        try { if( connection != null ) connection.close() ; } catch( SQLException ex ) {}
    }
%>
Thank you<a href ="adminoptions.jsp">Return</a>
0
acslaterAuthor Commented:
Dont no what version we are using mysql 4.0.22 standard if that helps

with the code you just gave me im now getting  a bad gateway error
0
acslaterAuthor Commented:
just found it were using jvm version 1.4.2_07-b05
0
TimYatesCommented:
>> with the code you just gave me im now getting  a bad gateway error

So it's printing

  ERROR! :: Bad Gateway

?
0
acslaterAuthor Commented:
its saying:

Information Alert
 
 

Status : 502 Bad Gateway

Description : The origin web server encountered an unexpected condition which prevented it from fulfilling the request. Please try your request again.


 
 
0
TimYatesCommented:
Ok, try this:

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

<%@ page import="java.sql.*" %>
<%
    PreparedStatement stmt = null ;
    Connection connection = null;

    try
    {
        ---------------------connection stuff-------------------------------

        String sql = ("UPDATE questions SET Q_Test = ? WHERE Q_No = ?") ;
        stmt = connection.prepareStatement( sql ) ;
        int i = 1 ;
        String param = request.getParameter( "QuestionNo" + i ) ;
        while( param != null )
        {
            stmt.setString( 1, param ) ;
            stmt.setInt( 2, i ) ;
            stmt.executeUpdate() ;
            i++ ;
            param = request.getParameter( "QuestionNo" + i ) ;
        }
    }
    catch( SQLException ex )
    {
        out.println( "ERROR! :: " + ex.toString() ) ;
        out.println( "<BR>" ) ;
        out.println( "<PRE>" ) ;
        StackTraceElement[] stack = ex.getStackTrace() ;
        for( int stc = 0 ; stc < stack.length ; stc++ )
            out.println( "   " + stack[ stc ].toString() ) ;
        out.println( "</PRE>" ) ;
    }
    finally
    {
        try { if( stmt != null ) stmt.close() ; } catch( SQLException ex ) {}
        try { if( connection != null ) connection.close() ; } catch( SQLException ex ) {}
    }
%>
Thank you<a href ="adminoptions.jsp">Return</a>
0
acslaterAuthor Commented:
any other ideas tim?
0
TimYatesCommented:
>> Status : 502 Bad Gateway

Eeek...that sounds like the website has gone down :-/
0
acslaterAuthor Commented:
no it hasnt it gives that error sometimes and it sounds serious and theres something stupid wrong.

Its still giving bad gateway error
0
acslaterAuthor Commented:
is there another way i can try because i cant get this working
0
TimYatesCommented:
Ok, lets try and catch ALL possible exceptions try:

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

<%@ page import="java.sql.*" %>
<%
    PreparedStatement stmt = null ;
    Connection connection = null;

    try
    {
        ---------------------connection stuff-------------------------------

        String sql = ("UPDATE questions SET Q_Test = ? WHERE Q_No = ?") ;
        stmt = connection.prepareStatement( sql ) ;
        int i = 1 ;
        String param = request.getParameter( "QuestionNo" + i ) ;
        while( param != null )
        {
            stmt.setString( 1, param ) ;
            stmt.setInt( 2, i ) ;
            stmt.executeUpdate() ;
            i++ ;
            param = request.getParameter( "QuestionNo" + i ) ;
        }
    }
    catch( Exception ex )   // catch EVERYTHING!!
    {
        out.println( "ERROR! :: " + ex.toString() ) ;
        out.println( "<BR>" ) ;
        out.println( "<PRE>" ) ;
        StackTraceElement[] stack = ex.getStackTrace() ;
        for( int stc = 0 ; stc < stack.length ; stc++ )
            out.println( "   " + stack[ stc ].toString() ) ;
        out.println( "</PRE>" ) ;
    }
    finally
    {
        try { if( stmt != null ) stmt.close() ; } catch( SQLException ex ) {}
        try { if( connection != null ) connection.close() ; } catch( SQLException ex ) {}
    }
%>
0
TimYatesCommented:
And what goes inside

        ---------------------connection stuff-------------------------------

?

obviously, change IP addresses, usernames and passwords ;-)
0
acslaterAuthor Commented:
still getting bad gateway error dont no whats wrong. but the site hasnt gone down!
0
TimYatesCommented:
can you post EXACTLY the jsp you have?
0
acslaterAuthor Commented:
OK I  have no errors now what was wrong was something stupid the table name was spelt wrong in the update. The problem is its not working its not updating the database with what i change the questions
0
acslaterAuthor Commented:
THIS IS EXACTLY THE CODE IM USING ANY IDEAS WHY ITS NOT UPDATING ITS OBVIOUSLY NOT EXECUTING THE UPDATE:


<%@ page language="java" contentType="text/html" import="java.sql.*, java.util.*, java.lang.*, java.io.* " errorPage="errorpage.jsp" %>


<%
    PreparedStatement stmt = null ;
    Connection connection = null;

    try
    {
       
                Class.forName("org.gjt.mm.mysql.Driver").newInstance();
               connection = DriverManager.getConnection("jdbc:mysql://localhost/combcdte_project?user=*********_admin&password=********");


        String sql = ("UPDATE Questions SET Q_Test = ? WHERE Q_No = ?") ;
        stmt = connection.prepareStatement( sql ) ;
        int i = 1 ;
        String param = request.getParameter( "QuestionNo" + i ) ;
        while( param != null )
        {
            stmt.setString( 1, param ) ;
            stmt.setInt( 2, i ) ;
            stmt.executeUpdate() ;
            i++ ;
            param = request.getParameter( "QuestionNo" + i ) ;
        }
    }
    catch( Exception ex )   // catch EVERYTHING!!
    {
        out.println( "ERROR! :: " + ex.toString() ) ;
        out.println( "<BR>" ) ;
        out.println( "<PRE>" ) ;
        StackTraceElement[] stack = ex.getStackTrace() ;
        for( int stc = 0 ; stc < stack.length ; stc++ )
            out.println( "   " + stack[ stc ].toString() ) ;
        out.println( "</PRE>" ) ;
    }
    finally
    {
        try { if( stmt != null ) stmt.close() ; } catch( SQLException ex ) {}
        try { if( connection != null ) connection.close() ; } catch( SQLException ex ) {}
    }

{
connection.close();
}

%>

0
TimYatesCommented:
Ok, let's try some fancy debug :-)


----------------------------
<%@ page import="java.sql.*" %>
<%
    PreparedStatement stmt = null ;
    Connection connection = null;

    try
    {
        ---------------------connection stuff-------------------------------

        String sql = ("UPDATE questions SET Q_Test = ? WHERE Q_No = ?") ;
        stmt = connection.prepareStatement( sql ) ;
        int i = 1 ;
        String param = request.getParameter( "QuestionNo" + i ) ;
        out.println( "- Got paramter QuestionNo" + i + " it was equal to " + param + "<br>" ) ;
        while( param != null )
        {
            out.println( "- Setting question " + i + " to " + param + "<br>" ) ;
            stmt.setString( 1, param ) ;
            stmt.setInt( 2, i ) ;
            int woo = stmt.executeUpdate() ;
            out.println( woo + " rows updated sucessfully!" + "<br>" ) ;
            i++ ;
            param = request.getParameter( "QuestionNo" + i ) ;
            out.println( "- Got paramter QuestionNo" + i + " it was equal to " + param + "<br>" ) ;
        }
        out.println( "Everything done (apparently)<br>" ) ;
    }
    catch( Exception ex )   // catch EVERYTHING!!
    {
        out.println( "ERROR! :: " + ex.toString() ) ;
        out.println( "<BR>" ) ;
        out.println( "<PRE>" ) ;
        StackTraceElement[] stack = ex.getStackTrace() ;
        for( int stc = 0 ; stc < stack.length ; stc++ )
            out.println( "   " + stack[ stc ].toString() ) ;
        out.println( "</PRE>" ) ;
    }
    finally
    {
        out.println( "Closing all our connections and stuff<br>" ) ;
        try { if( stmt != null ) stmt.close() ; } catch( SQLException ex ) {}
        try { if( connection != null ) connection.close() ; } catch( SQLException ex ) {}
    }
%>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TimYatesCommented:
Get rid of the

{
connection.close();
}

bit in your jsp ...  we close the connection in the finally block :-)
0
acslaterAuthor Commented:
Got paramter QuestionNo1 it was equal to Do you like programming computers?
- Setting Question 1 to Do you like programming computers?
1 rows updated sucessfully!
- Got paramter QuestionNo2 it was equal to null
Everything done (apparently)
Closing all our connections and stuff

this is what its saying now
 
0
acslaterAuthor Commented:
its working for the first question but not the rest!
0
acslaterAuthor Commented:
whats happening so why is it only doing the first question?
0
acslaterAuthor Commented:
thanks tim i got it working thanks for your help
0
TimYatesCommented:
Apparently, your form isn't posting a parameter called "QuestionNo2" :-/

Hmmm...

View the HTML source of your form...  does it have inputs named "QuestionNo1" to "QuestionNo20"?
0
TimYatesCommented:
>>  thanks tim i got it working thanks for your help

Yay!  Cool :-)

Glad it's all working now :-)

Debug can really help sometimes ;-)

Good luck with it :-)

Tim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.