[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

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
0
rajah_mohammed
Asked:
rajah_mohammed
  • 6
  • 5
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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