Solved

Search by keyword + skill +display in a table.

Posted on 2003-11-05
15
366 Views
Last Modified: 2010-04-01
Hi,
  I have a code that works for either search by keyword , or search by skill and it could either be displayed in tables individually and not together:


<%@ page import="java.sql.* ,com.dhal.*"%>
<%@ page import="java.util.StringTokenizer, java.lang.*"%>
<jsp:useBean id="s" class="com.dhal.skil" scope="session"/>
<jsp:setProperty name="s" property="*"/>
<html>
<body BGColor="#FFFFF0">
<%





String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=;password=";
Connection connection = null;
ResultSet rs2=null;
ResultSet rs=null;
Statement stmt=null;
ResultSet rs3=null;

try
{
 
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   connection = DriverManager.getConnection(connectionURL, "", "");

 
   String myskill="select * from skill";
 stmt=connection.createStatement();
 rs2=stmt.executeQuery(myskill);


}


catch( SQLException ex )
{
   ex.printStackTrace() ;
}
catch( ClassNotFoundException ex )
{
   ex.printStackTrace() ;
}

%>


<table border="4" BGColor="#FFF8DC" width="100%" CELLPADDING="4">
<tr>
<td>
<Font Color="#000000" Face="Garamond" Size="4">
<b>View Skills</b>
</Font>
</td>
</tr>
</table>
<br>
<form action="viewskill.jsp" name="form1">

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Search by Skill&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:  
<select name="choose">
<option value="">Select a skill</option>

<%
while(rs2.next()){ %>

<option value=<%=rs2.getString("skillid")%>><%=rs2.getString("skillid")%></option>
<%}%>

</select>

</p>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Search by keyword&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:  
<input type="text" name="keyword" size=20/>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input type="submit" value="Go">

<br>
<br>
<hr>


<%

String keyword = request.getParameter( "keyword" ) ;

System.out.println(keyword);

if( keyword != null&&keyword.trim().length()!= 0 ){
%>

<table border="4"BGColor="#FFF8DC" width="60%" CELLPADDING="4" style="margin-left:190px;" >
<tr>
<td align=center>
Skill ID
</td>
<td align=center>
Description
</td>
</tr>
</table>
<table border="1"BGColor="#FFFFF0" width="60%" CELLPADDING="4" style="margin-left:190px;" >

<%



StringTokenizer st = new StringTokenizer( keyword ) ;
String where = "" ;

while( st.hasMoreTokens() )
{
    if( where.length() == 0 )
      where = " WHERE " ;
    String word = st.nextToken() ;
    where += "( skilldescription LIKE '%" + word + "%' )" ;
    if( st.hasMoreTokens() )
        where += " OR " ;
}

String sqlQuery = "SELECT * FROM skill " + where ;
rs3=stmt.executeQuery(sqlQuery);


String choose=request.getParameter("choose");


 String query="Select skillid,skilldescription from skill where skillid='"+choose+"'";
 rs=stmt.executeQuery(query);


   

while(rs3.next()){
%>



<tr>
<td align=center><%=rs3.getString("skillid")%></td>  
<td align=center> <%=rs3.getString("skilldescription")%></td>  
</tr>
<%}
keyword="";
}
%>

</table>


</form>

<body>
<html>

Could you pls help me modify it ?so that it could work for both or individually and be displayed in a table
0
Comment
Question by:Jasbir21
  • 11
  • 4
15 Comments
 
LVL 35

Expert Comment

by:TimYates
ID: 9686646
Try:

<%@ page import="java.sql.* ,com.dhal.*"%>
<%@ page import="java.util.StringTokenizer, java.lang.*"%>
<jsp:useBean id="s" class="com.dhal.skil" scope="session"/>
<jsp:setProperty name="s" property="*"/>
<html>
<body BGColor="#FFFFF0">

<%
  String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=;password=";
  Connection connection = null;
  ResultSet rs2=null;
  ResultSet rs=null;
  Statement stmt=null;
  ResultSet rs3=null;
 
  try
  {
     Class.forName("com.mysql.jdbc.Driver").newInstance();
     connection = DriverManager.getConnection(connectionURL, "", "");
 
   
     String myskill="select * from skill";
     stmt=connection.createStatement();
     rs2=stmt.executeQuery(myskill);
  }
  catch( SQLException ex )
  {
     ex.printStackTrace() ;
  }
  catch( ClassNotFoundException ex )
  {
     ex.printStackTrace() ;
  }

%>


<table border="4" BGColor="#FFF8DC" width="100%" CELLPADDING="4">
  <tr>
    <td>
      <Font Color="#000000" Face="Garamond" Size="4">
        <b>View Skills</b>
      </Font>
    </td>
  </tr>
</table>
<br>
<form action="viewskill.jsp" name="form1">
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  Search by Skill&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:  
  <select name="choose">
    <option value="">Select a skill</option>

<%
  while(rs2.next())
  {
%>
    <option value=<%= rs2.getString("skillid") %>><%= rs2.getString("skillid") %></option>
<%
  }
%>

  </select>
  </p>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  Search by keyword&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:  
  <input type="text" name="keyword" size=20/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="submit" value="Go">
<br>
<br>
<hr>


  <table border="4"BGColor="#FFF8DC" width="60%" CELLPADDING="4" style="margin-left:190px;" >
    <tr>
      <td align=center>
        Skill ID
      </td>
      <td align=center>
        Description
      </td>
    </tr>
  </table>

  <table border="1"BGColor="#FFFFF0" width="60%" CELLPADDING="4" style="margin-left:190px;" >
<%
  String keyword = request.getParameter( "keyword" ) ;
  System.out.println(keyword);
 
  String where = "" ;

  if( keyword != null && keyword.trim().length()!= 0 )
  {
    StringTokenizer st = new StringTokenizer( keyword ) ;
 
    while( st.hasMoreTokens() )
    {
        if( where.length() == 0 )
          where = " ( " ;
        String word = st.nextToken() ;
        where += "( skilldescription LIKE '%" + word + "%' )" ;
        if( st.hasMoreTokens() )
          where += " OR " ;
        else
          where += " ) " ;
    }
  }

  String choose = request.getParameter( "choose" );
  if( choose != null && choose.trim().length() != 0 )
  {
    if( where.length() > 0 )
      where += " AND " ;
    where += "( skillid = '" + choose + "' )" ;
  }

  if( where.length() > 0 )
    where = " WHERE " + where ;

  String query= "Select skillid,skilldescription from skill " + where ;

  // PRINT OUT THE QUERY FOR DEBUG PURPOSES
  out.println( "<b>Executing " + query + "</b>" ) ;

  rs = stmt.executeQuery( query ) ;

  while( rs.next() )
  {
%>
    <tr>
      <td align=center><%= rs.getString( "skillid" ) %></td>  
      <td align=center> <%= rs.getString( "skilldescription" ) %></td>  
    </tr>
<%
  }
%>
  </table>
</form>
</body>
</html>
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9686678
That *should* do it...

It will print out the query string, so you can see the SQL it is going to run (which should help, as there's BOUND to be a bug or two in my logic) ;-)

if you select a skill AND type a keyword, it will only show the rows that have the keyword in them AND match the skill...

Argh....I just realized I put the old bug back in...

one sec...
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9686684
This is better (doesn't show all if page loaded)

<%@ page import="java.sql.* ,com.dhal.*"%>
<%@ page import="java.util.StringTokenizer, java.lang.*"%>
<jsp:useBean id="s" class="com.dhal.skil" scope="session"/>
<jsp:setProperty name="s" property="*"/>
<html>
<body BGColor="#FFFFF0">

<%
  String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=;password=";
  Connection connection = null;
  ResultSet rs2=null;
  ResultSet rs=null;
  Statement stmt=null;
  ResultSet rs3=null;
 
  try
  {
     Class.forName("com.mysql.jdbc.Driver").newInstance();
     connection = DriverManager.getConnection(connectionURL, "", "");
 
   
     String myskill="select * from skill";
     stmt=connection.createStatement();
     rs2=stmt.executeQuery(myskill);
  }
  catch( SQLException ex )
  {
     ex.printStackTrace() ;
  }
  catch( ClassNotFoundException ex )
  {
     ex.printStackTrace() ;
  }

%>


<table border="4" BGColor="#FFF8DC" width="100%" CELLPADDING="4">
  <tr>
    <td>
      <Font Color="#000000" Face="Garamond" Size="4">
        <b>View Skills</b>
      </Font>
    </td>
  </tr>
</table>
<br>
<form action="viewskill.jsp" name="form1">
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  Search by Skill&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:  
  <select name="choose">
    <option value="">Select a skill</option>

<%
  while(rs2.next())
  {
%>
    <option value=<%= rs2.getString("skillid") %>><%= rs2.getString("skillid") %></option>
<%
  }
%>

  </select>
  </p>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  Search by keyword&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:  
  <input type="text" name="keyword" size=20/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="submit" value="Go">
<br>
<br>
<hr>


  <table border="4"BGColor="#FFF8DC" width="60%" CELLPADDING="4" style="margin-left:190px;" >
    <tr>
      <td align=center>
        Skill ID
      </td>
      <td align=center>
        Description
      </td>
    </tr>
  </table>

  <table border="1"BGColor="#FFFFF0" width="60%" CELLPADDING="4" style="margin-left:190px;" >
<%
  String keyword = request.getParameter( "keyword" ) ;
  System.out.println(keyword);
 
  String where = "" ;

  if( keyword != null && keyword.trim().length()!= 0 )
  {
    StringTokenizer st = new StringTokenizer( keyword ) ;
 
    while( st.hasMoreTokens() )
    {
        if( where.length() == 0 )
          where = " ( " ;
        String word = st.nextToken() ;
        where += "( skilldescription LIKE '%" + word + "%' )" ;
        if( st.hasMoreTokens() )
          where += " OR " ;
        else
          where += " ) " ;
    }
  }

  String choose = request.getParameter( "choose" );
  if( choose != null && choose.trim().length() != 0 )
  {
    if( where.length() > 0 )
      where += " AND " ;
    where += "( skillid = '" + choose + "' )" ;
  }

  if( where.length() > 0 )
  {
    where = " WHERE " + where ;

    String query= "Select skillid,skilldescription from skill " + where ;
 
    // PRINT OUT THE QUERY FOR DEBUG PURPOSES
    out.println( "<b>Executing " + query + "</b>" ) ;

    rs = stmt.executeQuery( query ) ;

    while( rs.next() )
    {
%>
    <tr>
      <td align=center><%= rs.getString( "skillid" ) %></td>  
      <td align=center> <%= rs.getString( "skilldescription" ) %></td>  
    </tr>
<%
    }
  }
%>
  </table>
</form>
</body>
</html>
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 35

Expert Comment

by:TimYates
ID: 9686861
And this is even better, as it closes all the connections to the database, and catches any exceptions on your search SQL

And it's less code ;-)

0
 
LVL 35

Accepted Solution

by:
TimYates earned 250 total points
ID: 9686865
Oops.,... I mean this:

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

<%@ page import="java.sql.* ,com.dhal.*"%>
<%@ page import="java.util.StringTokenizer, java.lang.*"%>
<jsp:useBean id="s" class="com.dhal.skil" scope="session"/>
<jsp:setProperty name="s" property="*"/>
<html>
<body BGColor="#FFFFF0">

<table border="4" BGColor="#FFF8DC" width="100%" CELLPADDING="4">
  <tr>
    <td>
      <Font Color="#000000" Face="Garamond" Size="4">
        <b>View Skills</b>
      </Font>
    </td>
  </tr>
</table>
<br>
<form action="viewskill.jsp" name="form1">
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  Search by Skill&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:  
  <select name="choose">
    <option value="">Select a skill</option>

<%
  String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=;password=";
  Connection connection = null;
  Statement stmt=null;
  ResultSet rs=null;
 
  try
  {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    connection = DriverManager.getConnection(connectionURL, "", "");
   
    String myskill="select * from skill";
    stmt=connection.createStatement();
    rs=stmt.executeQuery(myskill);

    while(rs.next())
    {
%>
    <option value=<%= rs.getString("skillid") %>><%= rs.getString("skillid") %></option>
<%
    }

    // close this!!
    rs.close() ;
    rs = null ;
    stmt.close() ;
    stmt = null ;
%>

  </select>
  </p>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  Search by keyword&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:  
  <input type="text" name="keyword" size=20/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="submit" value="Go">

  <br><br><hr>

  <table border="4"BGColor="#FFF8DC" width="60%" CELLPADDING="4" style="margin-left:190px;" >
    <tr>
      <td align=center>
        Skill ID
      </td>
      <td align=center>
        Description
      </td>
    </tr>
  </table>

  <table border="1"BGColor="#FFFFF0" width="60%" CELLPADDING="4" style="margin-left:190px;" >
<%
    String keyword = request.getParameter( "keyword" ) ;
    System.out.println(keyword);
 
    String where = "" ;

    if( keyword != null && keyword.trim().length()!= 0 )
    {
      StringTokenizer st = new StringTokenizer( keyword ) ;
 
      while( st.hasMoreTokens() )
      {
        if( where.length() == 0 )
          where = " ( " ;
        String word = st.nextToken() ;
        where += "( skilldescription LIKE '%" + word + "%' )" ;
        if( st.hasMoreTokens() )
          where += " OR " ;
        else
          where += " ) " ;
      }
    }

    String choose = request.getParameter( "choose" );
    if( choose != null && choose.trim().length() != 0 )
    {
      if( where.length() > 0 )
        where += " AND " ;
      where += "( skillid = '" + choose + "' )" ;
    }

    if( where.length() > 0 )
    {
      where = " WHERE " + where ;

      String query= "Select skillid,skilldescription from skill " + where ;
   
      // PRINT OUT THE QUERY FOR DEBUG PURPOSES
      out.println( "<b>Executing " + query + "</b>" ) ;
 
      stmt=connection.createStatement();
      rs = stmt.executeQuery( query ) ;
 
      while( rs.next() )
      {
%>
    <tr>
      <td align=center><%= rs.getString( "skillid" ) %></td>  
      <td align=center> <%= rs.getString( "skilldescription" ) %></td>  
    </tr>
<%
      }
      rs.close() ;
      rs = null ;
      stmt.close() ;
      stmt = null ;
      connection.close() ;
      connection = null ;
    }
  }
  catch( SQLException ex )
  {
     ex.printStackTrace() ;
  }
  catch( ClassNotFoundException ex )
  {
     ex.printStackTrace() ;
  }
  finally
  {
    // MAKE SURE ALL DB THINGS ARE CLOSED
    try { if( rs != null ) rs.close() ; } catch( Exception ex ) { }
    try { if( stmt != null ) stmt.close() ; } catch( Exception ex ) { }
    try { if( connection != null ) connection.close() ; } catch( Exception ex ) { }
  }
%>
  </table>
</form>
</body>
</html>
0
 

Author Comment

by:Jasbir21
ID: 9686869
Hi,
   when i tried to do it together this gets printed out  but the data doesn't gets displayed

Executing Select skillid,skilldescription from skill WHERE ( ( skilldescription LIKE '%do%' ) ) AND ( skillid = 'Teacher' )  

Thanks
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9686899
is there a row which has the word "do" in it, AND skillid == "Teacher" ?

I doubt there is...

What you probably want is:

change the line:

      if( where.length() > 0 )
        where += " AND " ;

to:

      if( where.length() > 0 )
        where += " OR " ;

Then that should give you what you want :-)

Tim.
0
 

Author Comment

by:Jasbir21
ID: 9686966
...Thanks a lot, it is working fine....
Just one more question pls, when i load the page , the header table:

SKILLID and Descripton gets loaded , how do i change it so that the header will not be displayed when the user loads the page, only displayed if query is entered .

Thank you.
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9687006
Change it to:

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

<%@ page import="java.sql.* ,com.dhal.*"%>
<%@ page import="java.util.StringTokenizer, java.lang.*"%>
<jsp:useBean id="s" class="com.dhal.skil" scope="session"/>
<jsp:setProperty name="s" property="*"/>
<html>
<body BGColor="#FFFFF0">

<table border="4" BGColor="#FFF8DC" width="100%" CELLPADDING="4">
  <tr>
    <td>
      <Font Color="#000000" Face="Garamond" Size="4">
        <b>View Skills</b>
      </Font>
    </td>
  </tr>
</table>
<br>
<form action="viewskill.jsp" name="form1">
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  Search by Skill&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:  
  <select name="choose">
    <option value="">Select a skill</option>

<%
  String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=;password=";
  Connection connection = null;
  Statement stmt=null;
  ResultSet rs=null;
 
  try
  {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    connection = DriverManager.getConnection(connectionURL, "", "");
   
    String myskill="select * from skill";
    stmt=connection.createStatement();
    rs=stmt.executeQuery(myskill);

    while(rs.next())
    {
%>
    <option value=<%= rs.getString("skillid") %>><%= rs.getString("skillid") %></option>
<%
    }

    // close this!!
    rs.close() ;
    rs = null ;
    stmt.close() ;
    stmt = null ;
%>

  </select>
  </p>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  Search by keyword&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:  
  <input type="text" name="keyword" size=20/>
  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type="submit" value="Go">

  <br><br><hr>

<%
    String keyword = request.getParameter( "keyword" ) ;
    System.out.println(keyword);
 
    String where = "" ;

    if( keyword != null && keyword.trim().length()!= 0 )
    {
      StringTokenizer st = new StringTokenizer( keyword ) ;
 
      while( st.hasMoreTokens() )
      {
        if( where.length() == 0 )
          where = " ( " ;
        String word = st.nextToken() ;
        where += "( skilldescription LIKE '%" + word + "%' )" ;
        if( st.hasMoreTokens() )
          where += " OR " ;
        else
          where += " ) " ;
      }
    }

    String choose = request.getParameter( "choose" );
    if( choose != null && choose.trim().length() != 0 )
    {
      if( where.length() > 0 )
        where += " AND " ;
      where += "( skillid = '" + choose + "' )" ;
    }

    if( where.length() > 0 )
    {
%>
  <table border="4"BGColor="#FFF8DC" width="60%" CELLPADDING="4" style="margin-left:190px;" >
    <tr>
      <td align=center>
        Skill ID
      </td>
      <td align=center>
        Description
      </td>
    </tr>
  </table>

  <table border="1"BGColor="#FFFFF0" width="60%" CELLPADDING="4" style="margin-left:190px;" >
<%
      where = " WHERE " + where ;

      String query= "Select skillid,skilldescription from skill " + where ;
   
      // PRINT OUT THE QUERY FOR DEBUG PURPOSES
      out.println( "<b>Executing " + query + "</b>" ) ;
 
      stmt=connection.createStatement();
      rs = stmt.executeQuery( query ) ;
 
      while( rs.next() )
      {
%>
    <tr>
      <td align=center><%= rs.getString( "skillid" ) %></td>  
      <td align=center> <%= rs.getString( "skilldescription" ) %></td>  
    </tr>
<%
      }
      rs.close() ;
      rs = null ;
      stmt.close() ;
      stmt = null ;
      connection.close() ;
      connection = null ;
%>
  </table>
<%
    }
  }
  catch( SQLException ex )
  {
     ex.printStackTrace() ;
  }
  catch( ClassNotFoundException ex )
  {
     ex.printStackTrace() ;
  }
  finally
  {
    // MAKE SURE ALL DB THINGS ARE CLOSED
    try { if( rs != null ) rs.close() ; } catch( Exception ex ) { }
    try { if( stmt != null ) stmt.close() ; } catch( Exception ex ) { }
    try { if( connection != null ) connection.close() ; } catch( Exception ex ) { }
  }
%>
</form>
</body>
</html>
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9687016
I moved the header bit inside the

    if( where.length() > 0 )
    {

block...so it will only be displayed if it does a query :-)

Good luck!!

Tim.
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9687038
and it closes the table after it closes the connection etc :-)

Tim
0
 

Author Comment

by:Jasbir21
ID: 9687077
Thank you so much, i did learn a lot from developing bean classes to database connection to input type hidden to searching by keyword to searching by all and displaying..

....One more thing,
if i would like to add more ids , all i got to do is add here right ??


if( where.length() > 0 )
        where += " AND " ;
      where += "( skillid = '" + choose + "' )" ;
     where+="OR";
 where +="(organizationid'"+organization+"')";
    }



....Thanks again and God bless
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9687104
Change:

    if( choose != null && choose.trim().length() != 0 )
    {
      if( where.length() > 0 )
        where += " OR " ;
      where += "( skillid = '" + choose + "' )" ;
    }

to:

    if( choose != null && choose.trim().length() != 0 )
    {
      if( where.length() > 0 )
        where += " OR " ;
      where += "( skillid = '" + choose + "' )" ;
    }
    if( organization != null && organization.trim().length() != 0 )
    {
      if( where.length() > 0 )
         where += " OR " ;
      where += "( organizationid = '" + organization + "' )" ;
    }
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9687112
Glad I could help :-)

Tim.
0
 

Author Comment

by:Jasbir21
ID: 9687114
hi,
  I have an old question concerning dynamic table ,it would really very very nice if you could look at it

http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20779026.html

Thanks and God bless
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

This tutorial shows how to create a greeting card by combining two image layers and a text layer on a PC using a free image editing app.
An article on effective troubleshooting
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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