Solved

Search by keyword + skill +display in a table.

Posted on 2003-11-05
15
364 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
java cyclic graphs 15 194
java.util.concurrent.locks in java 3 178
servlet concurrency 13 84
Can't access image with tomcat/html 6 85
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this article, I show you step by step with screenshots to assist you - HOW TO: Deploy and Install the VMware vCenter Server Appliance 6.5 (VCSA 6.5), with some helpful tips along the way.
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now