?
Solved

UPDATING A TABLE USING AN SQL UPDATE STATEMENT

Posted on 2005-04-01
59
Medium Priority
?
249 Views
Last Modified: 2010-04-01
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?


0
Comment
Question by:acslater
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 28
  • 23
  • 7
  • +1
59 Comments
 
LVL 35

Expert Comment

by:TimYates
ID: 13679224
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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679228
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
 
LVL 8

Expert Comment

by:nagki
ID: 13679235
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 29

Expert Comment

by:bloodredsun
ID: 13679237
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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679247
>> You also have no '  ' sinlge quote marks around your question Strings

He put those in at the request.getParameter() stage :-)
0
 

Author Comment

by:acslater
ID: 13679253
Q_No is the unique identifier so if i had

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

Would that work?
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13679255
did you try my code?
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13679259
As your "editing" page is now dynamic, it would be best if your "update" page were dynamic too...
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13679264
ha, misread the question and missed the fact that he has a Q_No value.

What a wally...
0
 

Author Comment

by:acslater
ID: 13679288
Yea but should it be exactly like this line what are the ??

 String sql = "UPDATE questions SET Q_Test = ? WHERE Q_No = ?" ;
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13679306
>>  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
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13679308
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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679316
>>  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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679318
>> 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
 

Author Comment

by:acslater
ID: 13679323
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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679331
*fingers crossed*
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13679345
>>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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679374
Hehehe it's Friday, and you were probably up all night playing on your new PSP!

;-D
0
 

Author Comment

by:acslater
ID: 13679496
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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679510
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
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13679517
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
 

Author Comment

by:acslater
ID: 13679522
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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679537
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
 

Author Comment

by:acslater
ID: 13679540
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
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13679543
then it should be

stmt.setInt( 2,  Integer.parseInt("QuestionNo" + i) ) ;
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13679544
>> 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
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13679555
actually

stmt.setInt( 2,  i) ) ;
0
 

Author Comment

by:acslater
ID: 13679559
just tried that and its still given a blank page any ideas??
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13679561
       stmt.setString( 2, "QuestionNo" + i ) ;

should be:

        stmt.setInt( 2, i ) ;

assuming each question has a number starting at 1 :-)
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13679562
IS there an exception in tomcat/logs/catalina.out
0
 

Author Comment

by:acslater
ID: 13679570
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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679584
open catalina.out in a text editor, and scroll to the end...

It may have printed an exception out there...
0
 

Author Comment

by:acslater
ID: 13679590
Not sure if i have control of tomcat im using a hosting site its called javaserver hosting.com
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13679612
Ahhh...ok...

hmmmm...

can you post the entire jsp?
0
 

Author Comment

by:acslater
ID: 13679630
<%


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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679664
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
 

Author Comment

by:acslater
ID: 13679690
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
 

Author Comment

by:acslater
ID: 13679694
just found it were using jvm version 1.4.2_07-b05
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13679716
>> with the code you just gave me im now getting  a bad gateway error

So it's printing

  ERROR! :: Bad Gateway

?
0
 

Author Comment

by:acslater
ID: 13679720
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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679732
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
 

Author Comment

by:acslater
ID: 13679734
any other ideas tim?
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13679736
>> Status : 502 Bad Gateway

Eeek...that sounds like the website has gone down :-/
0
 

Author Comment

by:acslater
ID: 13679747
no it hasnt it gives that error sometimes and it sounds serious and theres something stupid wrong.

Its still giving bad gateway error
0
 

Author Comment

by:acslater
ID: 13679786
is there another way i can try because i cant get this working
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13679794
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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679797
And what goes inside

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

?

obviously, change IP addresses, usernames and passwords ;-)
0
 

Author Comment

by:acslater
ID: 13679814
still getting bad gateway error dont no whats wrong. but the site hasnt gone down!
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13679823
can you post EXACTLY the jsp you have?
0
 

Author Comment

by:acslater
ID: 13679842
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
 

Author Comment

by:acslater
ID: 13679863
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
 
LVL 35

Accepted Solution

by:
TimYates earned 1400 total points
ID: 13679872
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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679875
Get rid of the

{
connection.close();
}

bit in your jsp ...  we close the connection in the finally block :-)
0
 

Author Comment

by:acslater
ID: 13679897
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
 

Author Comment

by:acslater
ID: 13679911
its working for the first question but not the rest!
0
 

Author Comment

by:acslater
ID: 13679924
whats happening so why is it only doing the first question?
0
 

Author Comment

by:acslater
ID: 13679944
thanks tim i got it working thanks for your help
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13679950
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
 
LVL 35

Expert Comment

by:TimYates
ID: 13679954
>>  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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
Check out the latest tech news, community articles, and expert highlights in August's newsletter.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question