MS-Access and Jsp

Question

mrabrar09 0 Newbie Poster

12 Years Ago

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

jsp microsoft-access

2 Contributors
4 Replies
325 Views
1 Week Discussion Span
Latest Post 12 Years Ago Latest Post by javaAddict

All 4 Replies

javaAddict 900 Nearly a Senior Poster

12 Years Ago

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

Reply to this topic

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.

mrabrar09 0 Newbie Poster · Answer 1 · 2012-01-16T13:33:34+00:00

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

mrabrar09 0 Newbie Poster · Answer 2 · 2012-01-17T15:02:27+00:00

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

javaAddict 900 Nearly a Senior Poster Team Colleague Featured Poster · Answer 3 · 2012-01-17T17:51:23+00:00

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.

MS-Access and Jsp

Recommended Answers Collapse Answers

All 4 Replies

Recommended Answers