Find a Record in JSP

Hello;

I have created a simple JSP that connects to MS Access and should retrieve a record. However I cant make it to work.

I have this to get the value of fld1 :
id = request.getParameter("fld1");

I have this to compare the value :
query = "SELECT * FROM Employees WHERE IdNumber = id";  

Its not working. However if I replace id w/ 001 it works. All I want is to compare id w/ IdNumber for my jsp
to search the records in my database then display it.

Here's my complete code :

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@ page import ="java.sql.*" %>
   
    <html>
    <head><title>Database Connection</title></head><form name="frmx"><body>
    <br>Id Number : <br><input type=field name="fld1" cols="30">
    <input type="submit" name="cmdbtn1" value=" Find "/><br><br>
   
    <%! Connection con; %>
    <%! Statement stmt; %>
    <%! ResultSet rs; %>
    <%! String query; %>
    <%! String url; %>
    <%! String id; %>

    <%
        id = request.getParameter("fld1");
       
            try
            {              
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                url = "jdbc:odbc:Records";
                con = DriverManager.getConnection(url);
                stmt = con.createStatement();  

                query = "SELECT * FROM Employees WHERE IdNumber = id";      
                rs = stmt.executeQuery(query);
           
                while (rs.next()) {  
                    out.println("   Record(s) Found : <br><br>");
                    out.println("     <b>Id Number :</b> " + rs.getString(1) + "<br>");
                    out.println("     <b>Lastname  :</b> " + rs.getString(2) + "<br>");
                    out.println("     <b>Firstname :</b> " + rs.getString(3) + "<br>");
                }
            }
            catch (ClassNotFoundException x) {
                x.printStackTrace();
            }
            catch(SQLException y){
                y.printStackTrace();
            }
    %>
   
    <br><br></body></form>
</html>

Thanks : Rajah
rajah_mohammedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ldbkuttyCommented:
change your JSP part to this:

 <%
         String id = request.getParameter("fld1");  // declare the "id" field...
       
            try
            {              
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                url = "jdbc:odbc:Records";
                con = DriverManager.getConnection(url);
                stmt = con.createStatement();  

                query = "SELECT * FROM Employees WHERE IdNumber = " + id; // + is the concatenation operator in Java.
                rs = stmt.executeQuery(query);
           
                while (rs.next()) {  
                    out.println("   Record(s) Found : <br><br>");
                    out.println("     <b>Id Number :</b> " + rs.getString(1) + "<br>");
                    out.println("     <b>Lastname  :</b> " + rs.getString(2) + "<br>");
                    out.println("     <b>Firstname :</b> " + rs.getString(3) + "<br>");
                }
            }
            catch (ClassNotFoundException x) {
                x.printStackTrace();
            }
            catch(SQLException y){
                y.printStackTrace();
            }
    %>
0
rajah_mohammedAuthor Commented:
I tried what you said but its still a blank results ? what do I do ?
0
ldbkuttyCommented:
just noticing the fact that you already defined "id", but why a weird syntax <%!  ...  %>

change your full code like this:

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@page import ="java.sql.*" %>
   
<html>
<head><title>Database Connection</title></head>
<body>
<form name="frmx" method="post" action="this_page_name.jsp">
<br>Id Number : <br><input type=field name="fld1" size="30">
<input type="submit" name="cmdbtn1" value=" Find "/><br><br>
</form>    
<br><br>    

  <%
      Connection con;
      Statement stmt;
      ResultSet rs;
      String query;
      String url;
     
      String id = (request.getParameter("fld1") != null) ? request.getParameter("fld1") : null;

                  if(id != null && !"".equals(id)) {
          try {              
              Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
              url = "jdbc:odbc:Records";
              con = DriverManager.getConnection(url);
              stmt = con.createStatement();  

              query = "SELECT * FROM Employees WHERE IdNumber = " + id;
              rs = stmt.executeQuery(query);
         
              while (rs.next()) {  
                  out.println("   Record(s) Found : <br><br>");
                  out.println("     <b>Id Number :</b> " + rs.getString(1) + "<br>");
                  out.println("     <b>Lastname  :</b> " + rs.getString(2) + "<br>");
                  out.println("     <b>Firstname :</b> " + rs.getString(3) + "<br>");
              }
          }
          catch (ClassNotFoundException x) {
              x.printStackTrace();
          }
          catch(SQLException y){
              y.printStackTrace();
          }
      }
  %>
   
</body>
</html>
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

ldbkuttyCommented:
and ofcourse,

action="this_page_name.jsp"  in the <form...> tag means the JSP page name.
0
rajah_mohammedAuthor Commented:
This is what I did according to you, where records1.jsp is the name of this JSP  :

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@page import ="java.sql.*" %>
   
<html>
<head><title>Database Connection</title></head>
<body><form name="frmx" method="post" action="records1.jsp">
<br>Id Number : <br><input type=field name="fld1" size="25">
<input type="submit" name="cmdbtn1" value=" Find "/><br><br>
</form><br><br>    

  <%
      Connection con;
      Statement stmt;
      ResultSet rs;
      String query;
      String url;
     
      String id = (request.getParameter("fld1") != null) ? request.getParameter("fld1") : null;

        if(id != null && !"".equals(id)) {
           
            try {      
       
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                url = "jdbc:odbc:Records";
                con = DriverManager.getConnection(url);
                stmt = con.createStatement();  

                query = "SELECT * FROM Employees WHERE IdNumber = " + id;
                rs = stmt.executeQuery(query);
         
                while (rs.next()) {  
                    out.println(" Record(s) Found : <br><br>");
                    out.println("     <b>Id Number :</b> " + rs.getString(1) + "<br>");
                    out.println("     <b>Lastname  :</b> " + rs.getString(2) + "<br>");
                    out.println("     <b>Firstname :</b> " + rs.getString(3) + "<br>");
              }
          }
          catch (ClassNotFoundException x) {
              x.printStackTrace();
          }
          catch(SQLException y){
              y.printStackTrace();
          }
      }
  %>
 
</body>
</html>

Its displaying a blank page ? what do I do thanks ?
0
ldbkuttyCommented:
change
<input type=field name="fld1" size="25">

to:
<input type="text" name="fld1" size="25" />

1. do you get any errors/exceptions in the console ?
2. << However if I replace id w/ 001 it works. >>
    do you mean the page displays records if you have 001 instead of "id" ?
3. What do you get if you print "id" ?
    i.e. : out.println("Entered value : " + id); right after the
            String id = ..... line.
0
rajah_mohammedAuthor Commented:
This can be helpfull :

1.) I'm using netbeans 4.0 beta 2
2.) I'm using Tomcat 5.0 bundled w/ netbeans.

Answer to your questions :

1.) No Errors it complies successfully.
2.) Yes from my old codes above if I use 001 it displays the records.
3.) It displays = 001.

Here 's the codes from your suggestions :

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@page import ="java.sql.*" %>
   
<html>
<head><title>Database Connection</title></head>
<body><form name="frmx" method="post" action="records1.jsp">
<br>Id Number : <br><input type="text" name="fld1" size="25"/>
<input type="submit" name="cmdbtn1" value=" Find "/><br><br>
</form><br><br>    

  <%
      Connection con;
      Statement stmt;
      ResultSet rs;
      String query;
      String url;
     
      String id = (request.getParameter("fld1") != null) ? request.getParameter("fld1") : null;
      out.println("" + id);

        if(id != null && !"".equals(id)) {
           
            try {      
       
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                url = "jdbc:odbc:Records";
                con = DriverManager.getConnection(url);
                stmt = con.createStatement();  

                query = "SELECT * FROM Employees WHERE IdNumber = " + id;
                rs = stmt.executeQuery(query);
         
                while (rs.next()) {  
                    out.println(" Record(s) Found : <br><br>");
                    out.println("     <b>Id Number :</b> " + rs.getString(1) + "<br>");
                    out.println("     <b>Lastname  :</b> " + rs.getString(2) + "<br>");
                    out.println("     <b>Firstname :</b> " + rs.getString(3) + "<br>");
              }
          }
          catch (ClassNotFoundException x) {
              x.printStackTrace();
          }
          catch(SQLException y){
              y.printStackTrace();
          }
      }
  %>
 
</body>
</html>

Its still displaying a blank page ? what do I do ?
0
rajah_mohammedAuthor Commented:
I'm suspecting that there is a problem on comparing :
query = "SELECT * FROM Employees WHERE IdNumber = " + id;

If we can fix this the issue will be resolved. So do you have any
other way arround ?
0
ldbkuttyCommented:
< Yes from my old codes above if I use 001 it displays the records. >
Does it displays the records even now.. (with the above latest script also!) ?

I dont see any errors if it displays !!!

However, if you have declared the "IdNumber" table field other than integer, use:

query = "SELECT * FROM Employees WHERE IdNumber = '" + id + "'";

instead of

query = "SELECT * FROM Employees WHERE IdNumber = " + id;

Just for trial and error, print something in the try block lines and see till which line of the script, outut is getting displayed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rajah_mohammedAuthor Commented:
Haha I told you its just the comparing part. Your the man you got it, I just customized it from how I want it :

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@ page import ="java.sql.*" %>
   
    <html>
    <head><title>Database Connection</title></head>
    <body><form name="frmx" action="records1.jsp">
    <br>Id Number : <br><input type=field name="fld1" cols="30">
    <input type="submit" name="cmdbtn1" value=" Find "/><br><br></form>
   
    <% Connection con; %>
    <% Statement stmt; %>
    <% ResultSet rs; %>
    <% String query; %>
    <% String url; %>
    <% String id; %>

    <%
        id = request.getParameter("fld1");
       
            if(id != null && !"".equals(id)) {
               
                try
                {              
                    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                    url = "jdbc:odbc:Records";
                    con = DriverManager.getConnection(url);
                    stmt = con.createStatement();  

                    query = "SELECT * FROM Employees WHERE IdNumber = '" + id + "'";
                   
                    rs = stmt.executeQuery(query);
           
                    while (rs.next()) {  
                        out.println("   Record(s) Found : <br><br>");
                        out.println("     <b>Id Number :</b> " + rs.getString(1) + "<br>");
                        out.println("     <b>Lastname  :</b> " + rs.getString(2) + "<br>");
                        out.println("     <b>Firstname :</b> " + rs.getString(3) + "<br>");
                    }
                }
                catch (ClassNotFoundException x) {
                    x.printStackTrace();
                }
                catch(SQLException y){
                    y.printStackTrace();
                }
           }
    %>
   
    <br><br></body>
</html>

Thanks again man. I just started as a programmer in jsp and you did help me
a lot, hope w/ could do more of this in the future.
0
ldbkuttyCommented:
glad i was of help :)

All the best.....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.