asked on

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

ldbkutty

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();
}
%>

rajah_mohammed

ASKER

I tried what you said but its still a blank results ? what do I do ?

ldbkutty

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>

ldbkutty

and ofcourse,

action="this_page_name.jsp" in the <form...> tag means the JSP page name.

rajah_mohammed

ASKER

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 ?

ldbkutty

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.

rajah_mohammed

ASKER

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 ?

rajah_mohammed

ASKER

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 ?

ASKER CERTIFIED SOLUTION

ldbkutty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rajah_mohammed

ASKER

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.

ldbkutty

glad i was of help :)

All the best.....