• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

Resultset problems

String strquery1 = "SELECT * FROM SGT_WH_LOOKUP";
String strquery2 = "SELECT * FROM SGT_INV_TMP";

// *******WHY IF I PUT ResultSet rst2 = stmt.executeQuery(strquery2) ON TOP OF ResultSet rst1 = stmt.executeQuery(strquery1) WILL HAVE ERROR?????
ResultSet rst1 = stmt.executeQuery(strquery1);
ResultSet rst2 = stmt.executeQuery(strquery2);

while ( rst2.next() ) {
%>
<tr>
      // *******HOW TO I CHECK IF rst2.getString("Inv_DistWHCode") IS IN rst1 THEN DISPLAY BELOW RECORD????
      <td><%=rst2.getString("Inv_DistWHCode")%>&nbsp;</td>
      <%}%>
</tr>
<%
}
%>
0
mayachoy
Asked:
mayachoy
2 Solutions
 
fargoCommented:
because you are using same statement for executing the queries. Use two seperate statements. Moreover, always remember to close rs, stmt and conn in finally clause.

I don't understand your requirement much, but u can also make it in single query by using sql joins etc.
0
 
JoeCommented:
You should try doing something like this instead of looping back through the result set for every record...

 String strquery1 = null;
String strquery2 = "SELECT * FROM SGT_INV_TMP";

// *******WHY IF I PUT ResultSet rst2 = stmt.executeQuery(strquery2) ON TOP OF ResultSet rst1 = stmt.executeQuery(strquery1) WILL HAVE ERROR?????
ResultSet rst1 = null;
ResultSet rst2 = stmt.executeQuery(strquery2);
Statement stmt2 = con.createStatement();

while ( rst2.next() ) {
%>
<tr>
     <%
            //not sure if this is the right column name in your where, may have to change
             strquery1 = "SELECT * FROM SGT_WH_LOOKUP WHERE Inv_DistWHCode = '" + rst2.getString("Inv_DistWHCode")  + "'";
             rs1 = stmt2.executeQuery(strquery1); //dep

            if (rs1.next()){
           
      %>
     <td><%=rst2.getString("Inv_DistWHCode")%>&nbsp;</td>
     <%}%>
</tr>
<%
}
%>









If your DB supports it, here is an even better way to get it all in one select statement:


sql = "SELECT * FROM SGT_INV_TMP WHERE Inv_DistWHCode IN (SELECT Inv_DistWHCode FROM SGT_WH_LOOKUP)"


Joe

0
 
mayachoyAuthor Commented:
hi all,

thks for the help. i will try the sql later.

for fargo,
do you mean i have to use stm1 or stmt2?

ResultSet rst1 = stmt1.executeQuery(strquery1);
ResultSet rst2 = stmt2.executeQuery(strquery2);
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now