Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

UPDATING A TABLE USING AN SQL UPDATE STATEMENT

Posted on 2005-04-01
59
Medium Priority
?
252 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
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The deadly train derailment that occurred recently in DuPont, Washington, raises a lot of questions. It was a new route, the first trip tested with passengers, and the train was travelling at 50 mph over the zone’s speed limit. Could IoT play a role…
Are you a startup company? Being a startup, you may be using shared hosting, or maybe even dedicated hosting. But have you ever given a thought to using cloud computing now? Yes, don’t be surprised, it is possible for startups to opt for cloud compu…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

577 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