Link to home
Start Free TrialLog in
Avatar of acslater
acslater

asked on

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?


Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
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...
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
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() ;
%>
Avatar of nagki
nagki
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!
Avatar of bloodredsun
bloodredsun
Flag of Australia image
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.
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
>> You also have no '  ' sinlge quote marks around your question Strings

He put those in at the request.getParameter() stage :-)
Avatar of acslater
acslater

ASKER

Q_No is the unique identifier so if i had

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

Would that work?
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
did you try my code?
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
As your "editing" page is now dynamic, it would be best if your "update" page were dynamic too...
Avatar of bloodredsun
bloodredsun
Flag of Australia image
ha, misread the question and missed the fact that he has a Q_No value.

What a wally...
Avatar of acslater
acslater

ASKER

Yea but should it be exactly like this line what are the ??

 String sql = "UPDATE questions SET Q_Test = ? WHERE Q_No = ?" ;
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
>>  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...
Avatar of bloodredsun
bloodredsun
Flag of Australia image
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
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
>>  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...
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
>> stmt.executeUpdate();//this is done only once so very fast


Shouldn't that be:

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

?
Avatar of acslater
acslater

ASKER

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
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
*fingers crossed*
Avatar of bloodredsun
bloodredsun
Flag of Australia image
>>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 :-)
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
Hehehe it's Friday, and you were probably up all night playing on your new PSP!

;-D
Avatar of acslater
acslater

ASKER

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();
}
%>


Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
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...
Avatar of bloodredsun
bloodredsun
Flag of Australia image
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 ;-)
Avatar of acslater
acslater

ASKER

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") + "'";
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
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();
}
%>
Avatar of acslater
acslater

ASKER

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();
}
%>
Avatar of bloodredsun
bloodredsun
Flag of Australia image
then it should be

stmt.setInt( 2,  Integer.parseInt("QuestionNo" + i) ) ;
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
>> 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!
Avatar of bloodredsun
bloodredsun
Flag of Australia image
actually

stmt.setInt( 2,  i) ) ;
Avatar of acslater
acslater

ASKER

just tried that and its still given a blank page any ideas??
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
       stmt.setString( 2, "QuestionNo" + i ) ;

should be:

        stmt.setInt( 2, i ) ;

assuming each question has a number starting at 1 :-)
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
IS there an exception in tomcat/logs/catalina.out
Avatar of acslater
acslater

ASKER

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?
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
open catalina.out in a text editor, and scroll to the end...

It may have printed an exception out there...
Avatar of acslater
acslater

ASKER

Not sure if i have control of tomcat im using a hosting site its called javaserver hosting.com
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
Ahhh...ok...

hmmmm...

can you post the entire jsp?
Avatar of acslater
acslater

ASKER

<%


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?
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
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>
Avatar of acslater
acslater

ASKER

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
Avatar of acslater
acslater

ASKER

just found it were using jvm version 1.4.2_07-b05
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
>> with the code you just gave me im now getting  a bad gateway error

So it's printing

  ERROR! :: Bad Gateway

?
Avatar of acslater
acslater

ASKER

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.


 
 
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
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>
Avatar of acslater
acslater

ASKER

any other ideas tim?
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
>> Status : 502 Bad Gateway

Eeek...that sounds like the website has gone down :-/
Avatar of acslater
acslater

ASKER

no it hasnt it gives that error sometimes and it sounds serious and theres something stupid wrong.

Its still giving bad gateway error
Avatar of acslater
acslater

ASKER

is there another way i can try because i cant get this working
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
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 ) {}
    }
%>
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
And what goes inside

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

?

obviously, change IP addresses, usernames and passwords ;-)
Avatar of acslater
acslater

ASKER

still getting bad gateway error dont no whats wrong. but the site hasnt gone down!
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
can you post EXACTLY the jsp you have?
Avatar of acslater
acslater

ASKER

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
Avatar of acslater
acslater

ASKER

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();
}

%>

ASKER CERTIFIED SOLUTION
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
Get rid of the

{
connection.close();
}

bit in your jsp ...  we close the connection in the finally block :-)
Avatar of acslater
acslater

ASKER

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
 
Avatar of acslater
acslater

ASKER

its working for the first question but not the rest!
Avatar of acslater
acslater

ASKER

whats happening so why is it only doing the first question?
Avatar of acslater
acslater

ASKER

thanks tim i got it working thanks for your help
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
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"?
Avatar of TimYates
TimYates
Flag of United Kingdom of Great Britain and Northern Ireland image
>>  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