asked on

JSP while loop exits even though data exists

Hey guys,
I had to pick up a project someone who does not work here anymore was working on, I added the things I needed, but it is not working right!

Query 1 runs and selects the data the other queries need to run (and some that just needs displayed)

query 3,4,5 are supposed to loop trough a result set and print out the results based on the information in query 1 but there is not always info coming back, so I added an if statement before the query runs. When I do this, query 3 will run once and then exit the loop even if there is another record.

here is the code:

<%@ page import="java.sql.*,oracle.jdbc.driver.*, oracle.sql.*,java.io.*,javax.sql.*,java.awt.*,java.util.*,javax.servlet.*" %>
       <% String castnum = request.getParameter("castnumholder");
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

            Connection connection = DriverManager.getConnection ("jdbc:oracle:thin:@100.100.100.1:1521:test","test","test");
      connection.setAutoCommit(false);
      Statement statement = connection.createStatement ();
      String query1;
      String query2;
      String query3;
      String query4;
      String query5;
%>
<HTML>
<HEAD>
<TITLE>CERTIFICATE OF COMPLIANCE (COC)</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<%!
       //if the result is null, replace null with a blank
String blanknull(String s) {
return (s == null) ? "" : s;
}
%>

<img src="astrologococ.jpg" width=800 heigth=75><HR>

<% query2 = "select to_char(sysdate,'DD-MON-YYYY') as dt from dual";

ResultSet resultSet2 = statement.executeQuery ( query2 );

while (resultSet2.next ())
{
String vdt = resultSet2.getString("dt");
String dt = vdt;
String vsonum = request.getParameter("hidsonum");
String vitem = request.getParameter("hiditem");

%>
<FORM name="cocprint" method="get" target="_blank" action="wo_cert_qc_coc_mech2.jsp">
<input type="hidden" name="hidsonum" value= "<%= vsonum %>">
<input type="hidden" name="hiditem" value= "<%= vitem %>">
<INPUT type="submit" value="Print">
</FORM>

       <% query1 = "select a.dienum,a.alloy,a.temper,a.ordlen,a.custpo,a.part,a.descrip,b.custname,b.address1,b.address2,b.city,b.state,b.zip,LTRIM(RTRIM(c.castnum1)) as castnum1,LTRIM(RTRIM(c.castnum2)) as castnum2,LTRIM(RTRIM(c.castnum3)) as castnum3,d.color from cust b, soitem a,pressprod c,finish d where a.sonum = '" + vsonum + "' and a.soitemnum = '"+ vitem +"' AND a.sonum = c.sonum AND a.soitemnum = c.soitemnum AND a.custship = b.custnum AND a.fincode = d.fincode";

ResultSet resultSet1 = statement.executeQuery ( query1 );

while (resultSet1.next ())

{
String vcustname = resultSet1.getString("custname");
String vaddress1 = resultSet1.getString("address1");
String vaddress2 = resultSet1.getString("address2");
String vcity = resultSet1.getString("city");
String vstate = resultSet1.getString("state");
String vzip = resultSet1.getString("zip");
String vdienum = resultSet1.getString("dienum");
String valloy = resultSet1.getString("alloy");
String vtemper = resultSet1.getString("temper");
String vordlen = resultSet1.getString("ordlen");
String vcustpo = resultSet1.getString("custpo");
String vdescrip = resultSet1.getString("descrip");
String vcast1 = resultSet1.getString("castnum1");
String vcast2 = resultSet1.getString("castnum2");
String vcast3 = resultSet1.getString("castnum3");
String vcolor = resultSet1.getString("color");
String vpart = resultSet1.getString("part");
//this runs fine
%>
<table width="100%">
<tr><TD><B><FONT size=4>Customer : </TD><TD><%= blanknull(vcustname) %></TD><TD><B><FONT size=4>Date : </TD><TD><%= blanknull(dt) %></FONT></TD></TR>
<TR><TD></TD><TD><%= blanknull(vaddress1) %><%= blanknull(vaddress2) %></TD><TD></TD></TR>
<TR><TD></TD><TD><%= blanknull(vcity) %>,<%= blanknull(vstate) %>  <%= blanknull(vzip) %></TD><TD></TD></TR></TABLE><HR>
<CENTER>

<table width="80%">
<TR align="RIGHT"><TD width="10%"><B><FONT size=4>ASTM(B221) Standard Data for:</TD><TD align="LEFT" width="5%"><%= blanknull(valloy) %> <%= blanknull(vtemper) %></TD><TD width="10%"><B><FONT size=4>Customer Order # :</TD><TD align="LEFT" width="5%"><%= blanknull(vcustpo) %></FONT></TD></TR><TR><TD><B><FONT size=4>Order/Item # :</TD><TD align="LEFT" width="5%"><%= blanknull(vsonum) %> / <%= blanknull(vitem) %></FONT></TD></TR></TABLE><BR>
<CENTER><TABLE><TR><TD>Part:</TD><TD><%= vpart %></TD></TR></TABLE>
<CENTER><table width="50%">
<TR><TD>Die Number : </TD><TD><%= blanknull(vdienum) %></TD><TD>Order Length : </TD><TD><%= blanknull(vordlen) %></TD></TR>
<TR><TD>Alloy-Temper : </TD><TD><%= blanknull(valloy) %> <%= blanknull(vtemper) %></TD><TD>Order Weight : </TD><TD></TD></TR>
<TR><TD>Description : </TD> <TD><%= blanknull(vdescrip) %></TD><TD>UC# : </TD><TD><%= blanknull(vcolor) %></TD></TR><CENTER>
<table width="100%"><CENTER>
<TR><TH width="60%"><B><FONT size=5>Chemical Analysis:</TH><TH><B><FONT size=5>Mechanical Analysis</TH></TR>
<TR width="70%"><TD><table border=1>
<TR bgcolor="#cococo"><TD></TD><TD></TD><TD>SI</TD><TD>ZN</TD><TD>CU</TD><TD>MG</TD><TD>CR</TD><TD>FE</TD><TD>MN</TD><TD>TI</TD><TD size="5%">Others (Each)</TD><TD size="5%">Others (Total)</TD></TR>
<TR><TD>Table 1</TD><TD>Castnum</TD><TD>.20-.60</TD><TD><.10</TD><TD><.10</TD><TD>.45-.90</TD><TD><.10</TD><TD><.35</TD><TD><.10</TD><TD><.10</TD><TD><.05</TD><TD><.05</TD></TR>

<% //make sure there info exists
if (vcast1.length() >= 1 ) {
query3 = "select si,zn,cu,mg,cr,fe,mn,ti from wo_qc_chemical where castnum = '" + vcast1 + "'";
ResultSet resultSet3 = statement.executeQuery ( query3 );

while (resultSet3.next ())
{
String vsi1 = resultSet3.getString("si");
String vzn1 = resultSet3.getString("zn");
String vcu1 = resultSet3.getString("cu");
String vmg1 = resultSet3.getString("mg");
String vcr1 = resultSet3.getString("cr");
String vfe1 = resultSet3.getString("fe");
String vmn1 = resultSet3.getString("mn");
String vti1 = resultSet3.getString("ti");
//this query runs, but only once even if there are multiple rows of data! WHY?
       %>

       <TR><TD>Analysis</TD><TD><%= blanknull(vcast1) %></TD><TD><%= blanknull(vsi1) %> </TD><TD><%= blanknull(vzn1) %> </TD><TD><%= blanknull(vcu1) %> </TD><TD><%= blanknull(vmg1) %> </TD><TD><%= blanknull(vcr1) %> </TD><TD><%= blanknull(vfe1) %> </TD><TD><%= blanknull(vmn1) %> </TD><TD><%= blanknull(vti1) %> </TD><TD></TD><TD></TD></TR>
<% } } else { %>
       <TR><TD>Analysis</TD><TD><%= blanknull(vcast1) %></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD></TD><TD></TD></TR>
<% } %>
       <% //make sure there info exists
if (blanknull(vcast2).length() >= 1 ) {
query4 = "select si,zn,cu,mg,cr,fe,mn,ti from wo_qc_chemical where castnum = '" + vcast2 + "'";
ResultSet resultSet4 = statement.executeQuery ( query4 );

while (resultSet4.next ())
{
String vsi2 = resultSet4.getString("si");
String vzn2 = resultSet4.getString("zn");
String vcu2 = resultSet4.getString("cu");
String vmg2 = resultSet4.getString("mg");
String vcr2 = resultSet4.getString("cr");
String vfe2 = resultSet4.getString("fe");
String vmn2 = resultSet4.getString("mn");
String vti2 = resultSet4.getString("ti");
//this query runs, but only once even if there are multiple rows of data! WHY?
%>

       <TR><TD>Analysis</TD><TD><%= blanknull(vcast2) %></TD><TD><%= blanknull(vsi2) %> </TD><TD><%= blanknull(vzn2) %> </TD><TD><%= blanknull(vcu2) %> </TD><TD><%= blanknull(vmg2) %> </TD><TD><%= blanknull(vcr2) %> </TD><TD><%= blanknull(vfe2) %> </TD><TD><%= blanknull(vmn2) %> </TD><TD><%= blanknull(vti2) %> </TD><TD></TD><TD></TD></TR>
<% } } else { %>
       <TR><TD>Analysis</TD><TD><%= blanknull(vcast2) %></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD></TD><TD></TD></TR>
<% } %>

       <% //make sure there info exists
if (blanknull(vcast3).length() >= 1 ) {
query5 = "select si,zn,cu,mg,cr,fe,mn,ti from wo_qc_chemical where castnum = '" + vcast3 + "'";
ResultSet resultSet5 = statement.executeQuery ( query5 );

while (resultSet5.next ())
{
String vsi3 = resultSet5.getString("si");
String vzn3 = resultSet5.getString("zn");
String vcu3 = resultSet5.getString("cu");
String vmg3 = resultSet5.getString("mg");
String vcr3 = resultSet5.getString("cr");
String vfe3 = resultSet5.getString("fe");
String vmn3 = resultSet5.getString("mn");
String vti3 = resultSet5.getString("ti");
//this query runs, but only once even if there are multiple rows of data! WHY?
       %>

       <TR><TD>Analysis</TD><TD><%= blanknull(vcast3) %></TD><TD><%= blanknull(vsi3) %> </TD><TD><%= blanknull(vzn3) %> </TD><TD><%= blanknull(vcu3) %> </TD><TD><%= blanknull(vmg3) %> </TD><TD><%= blanknull(vcr3) %> </TD><TD><%= blanknull(vfe3) %> </TD><TD><%= blanknull(vmn3) %> </TD><TD><%= blanknull(vti3) %> </TD><TD></TD><TD></TD></TR></TABLE>
<% } } else { %>
<TR><TD>Analysis</TD><TD><%= blanknull(vcast3) %></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD></TD><TD></TD></TR></TABLE>
<% } %>
<TD><table border=1>
<TR bgcolor="#cococo"><TD></TD><TD>Wall Thickness</TD><TD>Ultimate (kpsi)</TD><TD>Yield (kpsi)</TD><TD>Elongation (%) -2 in</TD></TR>
       <TR><TD>Table 2</TD></TD><TD><.125</TD><TD>>21.0</TD><TD>>15.0</TD><TD>>8</TD></TR>
       <TR><TD></TD><TD>>.124</TD><TD>>21.0</TD><TD>>15.0</TD><TD>>10</TD></TR></TABLE>
</TD></TR></TABLE>

<table width="100%" style="position:absolute; left:0px; top:720px; z-index:1">
       <th colspan="2"><center><br><br><font size=2>This certificate certifies that the customer specifications mentioned above were followed and comply to the agreement between the customer and supplier.</center></th>
<tr></tr>
<tr><td></td><td align=RIGHT><p><align="LEFT"><b>Authorized By :__________________________</TD></tr>
<tr><td></td><td align=RIGHT>Astro Companies, Inc.</TD></tr>
</table>
<img src="sig.gif" style="position:absolute; left:525px; top:785; width:150px; height:53px; z-index:1">

       <% } } %><HR>
<%
connection.commit();
      statement.close();
connection.close();
      %>
</BODY>
</HTML>

thanks!

ASKER CERTIFIED SOLUTION

haobaba1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

astrohelp

ASKER

no I do things like this all the time, I have never had more than one statement. I will go ahead and give it a shot for the heck of it though

haobaba1

"By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists. "

http://java.sun.com/j2se/1.4.2/docs/api/index.html

It sure looks to me like you have multiple queries interleaved using the same statement object.

astrohelp

ASKER

ahhhh I get it now, previously I had ended the current connection, but since I am running this nested inside the other query, it requires a new statement! Let me give it a go!

sbockelman

anybody feel it worthwhile to comment on other aspects of the code presented?

I don't want to offer unsolicited advice, but surely, this page provides some great material for refactoring / optimization examples/conversation.

Care to comment on this? Or should it be left alone...since the question has been answered?

haobaba1

I would agree that it needs lots of work, I hate to open a can of worms for this fellow if he doesn't ask for it though, but how about a good discussion. Myself I would first start with removing all the queries from the jsp by putting them into custom iteration tags that can generate the table this would allow us to see the actual page formatting and picture what this thing really looks like. On top of parameterizing the queries it is obvious that he is running them multiple times so that would them a perfect candidate for PreparedStatements. He has a bomb shell waiting for him because he isn't using parameterized queries and I wouldn't be surprised if funky data with ' , " chars etc isn't causing the page to crash, but we haven't gotten that far into the diagnosis yet:) What would you suggest, I myself have only been using java for web development heavily for about 6 months total. I have similar pages in a site I am working on except I have 508 compliance requirements so I couldn't even get away without adding id, scope, and header attributres to every th and td this made using custom tags a gem for my situation.

haobaba1

being bored as I am on sat night I spent some time indenting your code to better get a sense of how nested these queries are and I came up with this, the markup errors were too numerous to list but I did fix most of them there is still a table nested that isn't inside <td></td> or a row of another table:

<%@ page import="java.sql.*,oracle.jdbc.driver.*, oracle.sql.*,java.io.*,javax.sql.*,java.awt.*,java.util.*,javax.servlet.*" %>
<% String castnum = request.getParameter("castnumholder");
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Connection connection = DriverManager.getConnection ("jdbc:oracle:thin:@100.100.100.1:1521:test","test","test");
connection.setAutoCommit(false);
Statement statement = connection.createStatement ();
String query1;
String query2;
String query3;
String query4;
String query5;
%>
<HTML>
<HEAD>
<TITLE>CERTIFICATE OF COMPLIANCE (COC)</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<%!
//if the result is null, replace null with a blank
String blanknull(String s) {
return (s == null) ? "" : s;
}
%>

<img src="astrologococ.jpg" width=800 heigth=75><HR>

<% query2 = "select to_char(sysdate,'DD-MON-YYYY') as dt from dual";

ResultSet resultSet2 = statement.executeQuery ( query2 );

while (resultSet2.next ()){
String vdt = resultSet2.getString("dt");
String dt = vdt;
String vsonum = request.getParameter("hidsonum");
String vitem = request.getParameter("hiditem");

%>
                    <FORM name="cocprint" method="get" target="_blank" action="wo_cert_qc_coc_mech2.jsp">
            <input type="hidden" name="hidsonum" value= "<%= vsonum %>">
              <input type="hidden" name="hiditem" value= "<%= vitem %>">
           <INPUT type="submit" value="Print">
           </FORM>

              <% query1 = "select a.dienum,a.alloy,a.temper,a.ordlen,a.custpo,a.part,a.descrip,b.custname,b.address1,b.address2,b.city,b.state,b.zip,LTRIM(RTRIM(c.castnum1)) as castnum1,LTRIM(RTRIM(c.castnum2)) as castnum2,LTRIM(RTRIM(c.castnum3)) as castnum3,d.color from cust b, soitem a,pressprod c,finish d where a.sonum = '" + vsonum + "' and a.soitemnum = '"+ vitem +"' AND a.sonum = c.sonum AND a.soitemnum = c.soitemnum AND a.custship = b.custnum AND a.fincode = d.fincode";

              ResultSet resultSet1 = statement.executeQuery ( query1 );

              while (resultSet1.next ()){
              String vcustname = resultSet1.getString("custname");
              String vaddress1 = resultSet1.getString("address1");
              String vaddress2 = resultSet1.getString("address2");
              String vcity = resultSet1.getString("city");
              String vstate = resultSet1.getString("state");
              String vzip = resultSet1.getString("zip");
              String vdienum = resultSet1.getString("dienum");
              String valloy = resultSet1.getString("alloy");
              String vtemper = resultSet1.getString("temper");
              String vordlen = resultSet1.getString("ordlen");
              String vcustpo = resultSet1.getString("custpo");
              String vdescrip = resultSet1.getString("descrip");
              String vcast1 = resultSet1.getString("castnum1");
              String vcast2 = resultSet1.getString("castnum2");
              String vcast3 = resultSet1.getString("castnum3");
              String vcolor = resultSet1.getString("color");
              String vpart = resultSet1.getString("part");
              //this runs fine
              %>
                   <table width="100%">
                         <tr>
                                    <TD><B><FONT size=4>Customer : </TD>
                                    <TD><%= blanknull(vcustname) %></TD>
                                    <TD><B><FONT size=4>Date : </TD>
                                    <TD><%= blanknull(dt) %></FONT></TD>
                              </TR>
                          <TR>
                                    <TD></TD>
                                    <TD><%= blanknull(vaddress1) %><%= blanknull(vaddress2) %></TD>
                                    <TD></TD>
                              </TR>
                       <TR>
                                    <TD></TD>
                                    <TD><%= blanknull(vcity) %>,<%= blanknull(vstate) %>  <%= blanknull(vzip) %></TD>
                                    <TD></TD>
                              </TR>
                        </TABLE>
                        <HR>
                   <CENTER>
                   <table width="80%">
                          <TR align="RIGHT">
                                    <TD width="10%"><B><FONT size=4>ASTM(B221) Standard Data for:</TD>
                                    <TD align="LEFT" width="5%"><%= blanknull(valloy) %> <%= blanknull(vtemper) %></TD>
                                    <TD width="10%"><B><FONT size=4>Customer Order # :</FONT></TD>
                                    <TD align="LEFT" width="5%"><FONT size=4><%= blanknull(vcustpo) %></FONT></TD>
                              </TR>
                              <TR>
                                    <TD><B><FONT size=4>Order/Item # :</FONT></TD>
                                    <TD align="LEFT" width="5%"><FONT size=4><%= blanknull(vsonum) %> / <%= blanknull(vitem) %></FONT></TD>
                              </TR>
                        </TABLE><BR>
                        </center>
                 <CENTER>
                        <TABLE>
                              <TR>
                                    <TD>Part:</TD>
                                    <TD><%= vpart %></TD>
                              </TR>
                        </TABLE>
                        </center>
                   <CENTER>
                        <table width="50%">
                       <TR>
                                    <TD>Die Number : </TD>
                                    <TD><%= blanknull(vdienum) %></TD>
                                    <TD>Order Length : </TD>
                                    <TD><%= blanknull(vordlen) %></TD>
                              </TR>
                       <TR>
                                    <TD>Alloy-Temper : </TD>
                                    <TD><%= blanknull(valloy) %> <%= blanknull(vtemper) %></TD>
                                    <TD>Order Weight : </TD>
                                    <TD></TD>
                              </TR>
                          <TR>
                                    <TD>Description : </TD>
                                    <TD><%= blanknull(vdescrip) %></TD>
                                    <TD>UC# : </TD>
                                    <TD><%= blanknull(vcolor) %></TD>
                              </TR>
                              <CENTER>
                              <%-- invalid markup was here --%>
                              <tr>
                                    <td>
                                          <CENTER>
                               <table width="100%"><CENTER>
                                           <TR>
                                                      <TH width="60%"><B><FONT size=5>Chemical Analysis:</TH>
                                                      <TH><B><FONT size=5>Mechanical Analysis</TH>
                                                </TR>
                                           <TR width="70%">
                                                      <TD>
                                                      <table border=1>
                                                       <TR bgcolor="#cococo">
                                                                  <TD></TD>
                                                                  <TD></TD>
                                                                  <TD>SI</TD>
                                                                  <TD>ZN</TD>
                                                                  <TD>CU</TD>
                                                                  <TD>MG</TD>
                                                                  <TD>CR</TD>
                                                                  <TD>FE</TD>
                                                                  <TD>MN</TD>
                                                                  <TD>TI</TD>
                                                                  <TD size="5%">Others (Each)</TD>
                                                                  <TD size="5%">Others (Total)</TD>
                                                            </TR>
                                                     <TR>
                                                                  <TD>Table 1</TD>
                                                                  <TD>Castnum</TD>
                                                                  <TD>.20-.60</TD>
                                                                  <TD><.10</TD>
                                                                  <TD><.10</TD>
                                                                  <TD>.45-.90</TD>
                                                                  <TD><.10</TD>
                                                                  <TD><.35</TD>
                                                                  <TD><.10</TD>
                                                                  <TD><.10</TD>
                                                                  <TD><.05</TD>
                                                                  <TD><.05</TD>
                                                            </TR>
                                                       <% //make sure there info exists
                                                       if (vcast1.length() >= 1 ) {
                                                              query3 = "select si,zn,cu,mg,cr,fe,mn,ti from wo_qc_chemical where castnum = '" + vcast1 + "'";
                                                              ResultSet resultSet3 = statement.executeQuery ( query3 );
                                                              while (resultSet3.next ()){
                                                              String vsi1 = resultSet3.getString("si");
                                                              String vzn1 = resultSet3.getString("zn");
                                                              String vcu1 = resultSet3.getString("cu");
                                                              String vmg1 = resultSet3.getString("mg");
                                                              String vcr1 = resultSet3.getString("cr");
                                                              String vfe1 = resultSet3.getString("fe");
                                                                   String vmn1 = resultSet3.getString("mn");
                                                              String vti1 = resultSet3.getString("ti"); //this query runs, but only once even if there are multiple rows of data! WHY?
                                                       %>

                                                              <TR>
                                                                        <TD>Analysis</TD>
                                                                        <TD><%= blanknull(vcast1) %></TD>
                                                                        <TD><%= blanknull(vsi1) %> </TD>
                                                                        <TD><%= blanknull(vzn1) %> </TD>
                                                                        <TD><%= blanknull(vcu1) %> </TD>
                                                                        <TD><%= blanknull(vmg1) %> </TD>
                                                                        <TD><%= blanknull(vcr1) %> </TD>
                                                                        <TD><%= blanknull(vfe1) %> </TD>
                                                                        <TD><%= blanknull(vmn1) %> </TD>
                                                                        <TD><%= blanknull(vti1) %> </TD>
                                                                        <TD></TD>
                                                                        <TD></TD>
                                                                  </TR>
                                                       <% }
                                                              } else { %>
                                                              <TR>
                                                                        <TD>Analysis</TD>
                                                                        <TD><%= blanknull(vcast1) %></TD>
                                                                        <TD> </TD><TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD></TD>
                                                                        <TD></TD>
                                                                  </TR>
                                                       <% } %>
                                                            <% //make sure there info exists
                                                       if (blanknull(vcast2).length() >= 1 ) {
                                                              query4 = "select si,zn,cu,mg,cr,fe,mn,ti from wo_qc_chemical where castnum = '" + vcast2 + "'";
                                                         ResultSet resultSet4 = statement.executeQuery ( query4 );
                                                              while (resultSet4.next ()){
                                                              String vsi2 = resultSet4.getString("si");
                                                              String vzn2 = resultSet4.getString("zn");
                                                                   String vcu2 = resultSet4.getString("cu");
                                                              String vmg2 = resultSet4.getString("mg");
                                                              String vcr2 = resultSet4.getString("cr");
                                                              String vfe2 = resultSet4.getString("fe");
                                                              String vmn2 = resultSet4.getString("mn");
                                                                   String vti2 = resultSet4.getString("ti");
                                                              //this query runs, but only once even if there are multiple rows of data! WHY?
                                                       %>

                                                              <TR>
                                                                        <TD>Analysis</TD>
                                                                        <TD><%= blanknull(vcast2) %></TD>
                                                                        <TD><%= blanknull(vsi2) %> </TD>
                                                                        <TD><%= blanknull(vzn2) %> </TD>
                                                                        <TD><%= blanknull(vcu2) %> </TD>
                                                                        <TD><%= blanknull(vmg2) %> </TD>
                                                                        <TD><%= blanknull(vcr2) %> </TD>
                                                                        <TD><%= blanknull(vfe2) %> </TD>
                                                                        <TD><%= blanknull(vmn2) %> </TD>
                                                                        <TD><%= blanknull(vti2) %> </TD>
                                                                        <TD></TD>
                                                                        <TD></TD>
                                                                  </TR>
                                                                  <% }
                                                            } else { %>
                                                              <TR>
                                                                        <TD>Analysis</TD>
                                                                        <TD><%= blanknull(vcast2) %></TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD></TD>
                                                                        <TD></TD>
                                                                  </TR>
                                                       <% } %>
                                                       <% //make sure there info exists
                                                       if (blanknull(vcast3).length() >= 1 ) {
                                                              query5 = "select si,zn,cu,mg,cr,fe,mn,ti from wo_qc_chemical where castnum = '" + vcast3 + "'";
                                                              ResultSet resultSet5 = statement.executeQuery ( query5 );
                                                              while (resultSet5.next ()){
                                                              String vsi3 = resultSet5.getString("si");
                                                              String vzn3 = resultSet5.getString("zn");
                                                                   String vcu3 = resultSet5.getString("cu");
                                                              String vmg3 = resultSet5.getString("mg");
                                                                   String vcr3 = resultSet5.getString("cr");
                                                                          String vfe3 = resultSet5.getString("fe");
                                                              String vmn3 = resultSet5.getString("mn");
                                                              String vti3 = resultSet5.getString("ti");
                                                              //this query runs, but only once even if there are multiple rows of data! WHY?
                                                       %>
                                                                   <TR>
                                                                              <TD>Analysis</TD>
                                                                                    <TD><%= blanknull(vcast3) %></TD>
                                                                                    <TD><%= blanknull(vsi3) %> </TD>
                                                                                    <TD><%= blanknull(vzn3) %> </TD>
                                                                                    <TD><%= blanknull(vcu3) %> </TD>
                                                                                    <TD><%= blanknull(vmg3) %> </TD>
                                                                                    <TD><%= blanknull(vcr3) %> </TD>
                                                                                    <TD><%= blanknull(vfe3) %> </TD>
                                                                                    <TD><%= blanknull(vmn3) %> </TD>
                                                                                    <TD><%= blanknull(vti3) %> </TD>
                                                                                    <TD></TD>
                                                                                    <TD></TD>
                                                                        </TR>
                                                                  </TABLE>//this should definitly not be inside the while loop you always need to close your tables
                                                              <% }
                                                              } else { %>
                                                              <TR>
                                                                        <TD>Analysis</TD>
                                                                        <TD><%= blanknull(vcast3) %></TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD> </TD>
                                                                        <TD></TD>
                                                                        <TD></TD>
                                                                  </TR>
                                                                  </TABLE>//dito
                                                       <% }
                                                            //should move closing table tag to after this scriptlet
                                                             %>

                                                       <TD>
                                                                  <table border=1>
                                                                        <TR bgcolor="#cococo">
                                                                              <TD></TD>
                                                                              <TD>Wall Thickness</TD>
                                                                              <TD>Ultimate (kpsi)</TD>
                                                                              <TD>Yield (kpsi)</TD>
                                                                              <TD>Elongation (%) -2 in</TD>
                                                                        </TR>
                                                                   <TR>
                                                                              <TD>Table 2</TD>
                                                                              <TD><.125</TD>
                                                                              <TD>>21.0</TD>
                                                                              <TD>>15.0</TD>
                                                                              <TD>>8</TD>
                                                                        </TR>
                                                                   <TR>
                                                                              <TD></TD>
                                                                              <TD>>.124</TD>
                                                                              <TD>>21.0</TD>
                                                                              <TD>>15.0</TD>
                                                                              <TD>>10</TD>
                                                                        </TR>
                                                      </TABLE>
                                               </TD>
                                                </TR>
                                          </TABLE>
                                      <table width="100%" style="position:absolute; left:0px; top:720px; z-index:1">
                                                <tr>
                                                  <th colspan="2"><center><br><br><font size=2>This certificate certifies that the customer specifications mentioned above were followed and comply to the agreement between the customer and supplier.</center></font></th>
                                                </tr>
                                      <tr>
                                                </tr>
                                           <tr>
                                                      <td>
                                                      </td>
                                                      <td align=RIGHT><p><align="LEFT"><b>Authorized By :__________________________</b></p></TD>
                                                </tr>
                                           <tr>
                                                      <td></td>
                                                      <td align=RIGHT>Astro Companies, Inc.</TD>
                                                </tr>
                                      </table>
                                      <img src="sig.gif" style="position:absolute; left:525px; top:785; width:150px; height:53px; z-index:1">
                                    </td>
                              </tr>
                        </table>
                        </center>
              <% }
       } %><HR>
<%
connection.commit();
statement.close();
connection.close();
%>
</BODY>
</HTML>

sbockelman

haobaba1 ,

I've been doing web development since 1997, the last 4 years, I have been doing mostly Java/J2EE.

The most glaring issue with the page design submitted by our friend here is that almost NONE of the work the page is doing should be done in a JSP.

The general architecture of this is astonishly bad. JSP is a "presentation" technology and should be used for rendering a view of information.

I couldn't even begin to elucidate this idea in a post here, but...I'd recommend studying the following sources (and their links):

http://struts.apache.org/

http://java.sun.com/blueprints/enterprise/

http://java.sun.com/blueprints/corej2eepatterns/

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

Secondly, row-based logic (as opposed to set-based logic) of the kind exhibited here
is usually not necessary and can be replaced by using SQL correctly to join on the related tables of data
and then produce the display using an algorithm that expects several rows per "thing" in the resultset.

Making fewer trips to the database is almost always the best way to improve response-time.

In some part of the distributed logic (usually in a factory class),
there can be code similar to this: (psuedo-code)

tempId = rs.getInt("category_id");
if( lastId == tempId ) {
// process 'detail' row
} else {
// process new top-level row
lastId = tempId;
}

Variations on this theme abound and can be extended to arbitrary levels of depth.
------------------------------

Finally, adopting XHTML (or at least HTML 4.x) standard markup would be recommended. Even the fixed markup is sub-optimal.

Use a consistent case for all tags (I recommend lowercase).
Use CSS for styling; avoid deprecated tags, such as <font>, <center>, <align> (?), etc.
Prefer external stylehseets to inline style attributes.
Consider using a page layout framework, such as Tiles.
Consider using tag libraries to generate HTML (watch out for performance issues, especially with tags that are just scriptlets in disguise).

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

Beyond these...

Use a DataSource and a ConnectionFactory to encapsulate database connection management, use connection pooling, etc.

Use an IDE (such as Eclipse) that enhances productivity and encourages refactoring.

Read and keep up with sites, magazines, and books by ( or that feature articles and interviews with ) the more respected members of the J2EE community.

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

Of course, this is just my 3 cents. Feel free to do whatever gets you home at 5:00 PM every day. These principles work for me.

astrohelp

ASKER

I appreciate all of your tips, but I would like to remind everyone that this is not my code and I do not feel like rewriting it at this point, I have far to many important things to do and this is just a report for one of the departments. In the previous coder's defense, he was rather new and the formatting needed to be very exact on this project and he did it the way he knew how, I would pass your info along to him, but he no longer works with us. I appreciate everyone's input though!