Member Avatar for mrabrar09

Hello friends,

I am new to Jsp

Here am facing a problem while trying to retrieve the data from ms access db using student no(number)

form.jps
=========

<html><head>
<BODY style="background-color:SteelBlue">
<center>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<form method="post" action="search.jsp"><h3>
Enter Ur Number: </h3><input type="text" size="3" name="no"><br />
<p><input type="submit" value="Submit">
</form>
</center>
</body>
</html>

============

search.jsp
================================

<%@ page import="java.sql.*" %>
    <html>
    <BODY style="background-color:SteelBlue">
	<form>

<table border="1">
<tr><th>No</th><th>Name</th><th>qual</th></tr>
<%
int num=Integer.parseInt(request.getParameter("no")); // no is student id taken from 

form.jsp eg no=1

//String num=request.getParameter("no");  
  
//int x= Integer.parseInt(num);
	
%>
<%
    Connection con=null;
    Statement stmt=null;
    ResultSet rs=null;
    try
	{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    con=DriverManager.getConnection("Jdbc:Odbc:student");
    //out.println("connection ok");
   String query= "select  *from student where no=' " +num+" ' "; 
	stmt=con.createStatement( );
	rs= stmt.executeQuery(query);
    }
    catch(Exception e)
    {
    out.println("error" +e);
    }%>

 <!-- 	<table  cellpadding="15" border="1" style="background-color: #ffffcc;">
<tr><td>No:</td><td>Name:</td><td>Qualification:</td></tr> 
 -->
<% while(rs.next())
	 { 

    %>
	    <tr><td><input type="text" name="no" value="<%=rs.getInt("no")%>" ></td>
        <td><input type="text" name="no" value="<%=rs.getString("nam")%>" ></td>
        <td><input type="text" name="no" value="<%=rs.getString("qual")%>"></td></tr> 
<%
}%> 
<br>
<%	rs.close();
    stmt.close();
    con.close();                              
%>
    </table>
	</form>
<form action="view.html" method="get" ><input type="submit" value="Go Home"/></form>
</body>
</html>

===============
I create Student db in ms access and in that i created three fields that are no,nam and qual. when am trying to search the student detail by no(number) the output on web page is not displaying.

But when am search by nam or qual it displaying the output on to the web page.

thanks in advance,
Abrar

  • 2 Contributors
  • 4 Replies
  • 327 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by javaAddict
Member Avatar for javaAddict

First of all check your query. Always print it before you run it and see what is going on. In this case you have this: "select *from student where no=' " +num+" ' "; Can you see the blank spaces where you have the quotes. Also you need a space at the '*'. This is what you execute:
select *from student where no=' 1 '

You need this:
select * from student where no='1' "select * from student where no='" +num+"' "; Or since 'no' is a number you can leave it like this:
select * from student where no=1 : "select * from student where no="+num; And before you continue, delete all that code from your jsp. Put all tha code in a method in a seperate class and call that method:

<%@ page import="java.sql.*" %>
    <html>
    <BODY style="background-color:SteelBlue">
	<form>

<table border="1">
<tr><th>No</th><th>Name</th><th>qual</th></tr>
<%
int num=Integer.parseInt(request.getParameter("no")); // no is student id taken from 
	
/*
Call a method with argument the num that returns the student. Crerate a Student class with attributes the columns of the table with get/set methods
*/
Student st = yourClassInstance.getStudentByNum(num);
    

<table  cellpadding="15" border="1" style="background-color: #ffffcc;">
<tr><td>No:</td><td>Name:</td><td>Qualification:</td></tr> 

<% 
// if no student found (rs.next==false then return null) an example at the end
if (st!=null)
	 { 

    %>
	    <tr><td><input type="text" name="no" value="<%=st.getNo()%>" ></td>
        <td><input type="text" name="no" value="<%=st.getName()%>" ></td>
        <td><input type="text" name="no" value="<%=st.getQual%>"></td></tr> 
<%
}%> 
<br>

    </table>
	</form>
<form action="view.html" method="get" ><input type="submit" value="Go Home"/></form>
</body>
</html>

Also at the search.jsp you have the input texts to have the same name:
<input type="text" name="no" value="<%=st.getNo()%>" >
<input type="text" name="no" value="<%=st.getName()%>" >
<input type="text" name="no" value="<%=st.getQual%>">

Use a different name or since you don't a form to submit them don't user input text at all:

<tr>
  <td><%=st.getNo()%></td>
  <td><%=st.getName()%></td>
  <td><%=st.getQual%></td>
</tr>

// Example of the new method you will create. Fill in the blanks

Student st = null;

// call the query
if (rs.next()) {
  st = new Stusent();
  st.set....
  st.set
  ...
}

return st; // if no student found the st will be null
Member Avatar for mrabrar09

@ JavaAddict Thanks a Lot I'll try and let you know

Member Avatar for mrabrar09

I

String query = "select *from student where nam=' "+nam+" ' ";

-here i this query have not face any problem getting result on webpage.

II.

String query = "select *from student where no=' " +no+ " ' ";

- but here am simple replacing the column name and trying to search by student id, getting error and simply showing empty table.

III.

String query = "SELECT student.[no], student.nam, student.qual FROM student WHERE (((student.[no])='"+num+"'))";

- working perfectly.

I solved the problem, the actual problem in query .. the sql and MS-Access query is different that why my search code is not working because of am using sql in my code but i need ms access query so i found it. Above query is ms access query now i am able to retrieve student details through their id's ....

And thanks @Javaddict who shows interest in my post his explanation is good but he suggesting me to use class/get/set methods... but i don't want use that one because am a beginner so i want to learn step by step. so, I really appreciate him thanks a lot

Thanks,
Abrar

Member Avatar for javaAddict

but he suggesting me to use class/get/set methods... but i don't want use that one because am a beginner so i want to learn step by step. so, I really appreciate him thanks a lot

Thanks,
Abrar

Actually those are the first things you learn. If you don't know how to create your own classes with attributes and get/set methods, stop immediately trying to write sql and jsp.
You cannot proceed with anything if you don't know that because it is basic O.O. programming.

Later on you will not be able to write anything that concerns connecting to a database and displaying the results if you can't put the results in objects that you created.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.